How HMG works with SQL server express ?

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

huangchenmin
Posts: 157
Joined: Mon Jun 07, 2010 2:24 am

How HMG works with SQL server express ?

Post by huangchenmin » Mon Feb 27, 2012 8:22 am

Dear all:
I need to integrate data from SQL server express and DBF.
Thanks for helping from rathinagiri, I realized how to work with SQLite.
Now I need solutions for conection to SQL server express and select data from those database.
Could anyone provide sample code about how to archive those?
Best Regards
chen min

huangchenmin
Posts: 157
Joined: Mon Jun 07, 2010 2:24 am

Post by huangchenmin » Mon Feb 27, 2012 10:53 am

huangchenmin wrote:Dear all:
I need to integrate data from SQL server express and DBF.
.................
chen min
Well, I found some sample code in C:\hmg.3.0.35\SAMPLES\RDD.SQL\mdb
Truely speaking, It is really difficult to me!
complete could not figure it out!

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 4:10 pm

I've integrated MS SQL Express with HMG via ODBC. I Put my code, if you are interested...

try with cSQLUser = sa and empty password = ""

Code: Select all


function 	SetDataBaseConnection
	
	public SQL_Connection
	
	
	do while .t.
		cConStr := "Driver={SQL Server};Server=" + cSQLSerwer + ";Database="+ cSQLDataBase + ";Trusted_Connection=yes;Uid=" + cSQLUser + ";Pwd="+ cSQLPassword +";"
			RDDSETDEFAULT( "SQLMIX" )
			SET( 4, "yyyy-mm-dd" )
			WAIT WINDOW "Wait for connection..." NOWAIT
			nConnection := RDDINFO( 1001, { "ODBC", cConStr }, 'SQLMIX' )
			WAIT CLEAR
			If nConnection == 0
				MsgStop("Error in connection with DB: " + cSQLDataBase)
				if MsgYesNo("Try to create DB: " + cSQLDataBase+ "?")
					cConStr := "Driver={SQL Server};Server=" + cSQLSerwer + ";Database="+ "master" + ";Trusted_Connection=yes;Uid=" + cSQLUser + ";Pwd="+ cSQLPassword +";"
					nConnection := RDDINFO( 1001, { "ODBC", cConStr }, 'SQLMIX' )
					if nConnection == 0
						MsgStop("Creating DB: "+ cSQLBaza + " unsuccessful! Program will be terminated.")
						Release Window ALL
						Quit
					else
						if SQL_DowolnySQL("CREATE DATABASE " + cSQLDataBase)
							loop
						else
							MsgStop("Error while creating DB: "+cSQLDataBase)
							quit
						endif
					endif
				else
					Release Window ALL
					Quit
				endif
			else
				exit
			Endif               	
		#ENDIF
	enddo
return

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 4:12 pm

Retrieving data from SQL db:

Code: Select all

function SQL_GetArray
	param cNazwaTabeli, aListaPol, aTypyPol, cWarunek, cOrder

	local cZapytanie := "Select ", i, aRet := {}, aPom, nRow
	
	WAIT WINDOW "Retrieving data..." NOWAIT
	if cWarunek = NIL
		cWarunek := ""
	endif
	if cOrder = NIL
		cOrder := ""
	endif

	if aListaPol = NIL
		cZapytanie += "*"
	else
		for i:= 1 to len(aListaPol)
			cZapytanie += aListaPol[i] + ","
		next i
		cZapytanie := left(cZapytanie, len(cZapytanie)-1)
	endif
	
	cZapytanie += " from " + cNazwaTabeli
	if !empty(cWarunek)
		cZapytanie += " where "+cWarunek
	endif
	if !empty(cOrder)
		cZapytanie += " ORDER BY " + cOrder
	endif
	
		DBUSEAREA( .T.,, cZapytanie, "tempDBF" )
		if aListaPol = NIL
			aListaPol := {}
			for i:=1 to fcount()
				AAdd(aListaPol, Fieldname(i))
			next i
		endif
		do while !eof()
			aPom := {}
			for i:=1 to len(aListaPol)
				AAdd(aPom , SQL2Value(aTypyPol[i],FieldGet(i)))
			next i
			aadd(aRet, aPom)
			DBSkip()
		enddo
		DBCloseAll()
	WAIT CLEAR
return aRet

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 4:15 pm

Code: Select all

function SQL_GetRow
	param cNazwaTabeli, aListaPol, aTypyPol, cWarunek
	local cZapytanie := "Select ", i, aRet := {}, aPom, nRow
	local xFormatDaty
   xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )

	if cWarunek = NIL
		cWarunek := ""
	endif
	if aListaPol = NIL
		cZapytanie += "*"
	else
		for i:= 1 to len(aListaPol)
			cZapytanie += aListaPol[i] + ","
		next i
		cZapytanie := left(cZapytanie, len(cZapytanie)-1)
	endif
	cZapytanie += " from " + cNazwaTabeli
	if !empty(cWarunek)
		cZapytanie += " where "+cWarunek
	endif
	

	DBUSEAREA( .T.,, cZapytanie, "tempDBF" )
	if aListaPol = NIL
		aListaPol := {}
		for i:=1 to fcount()
			AAdd(aListaPol, Fieldname(i))
			AAdd(aTypyPol, FieldType(i))
		next i
	endif
	if !eof()
		for i:=1 to len(aListaPol)
			AAdd(aRet, SQL2Value(aTypyPol[i],FieldGet(i)))
		next i
	endif
	DBCloseAll()

	SET( _SET_DATEFORMAT, xFormatDaty)
return aRet

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 4:16 pm

Code: Select all

function SQL_GetColumn
	param cNazwaTabeli, cNazwaPola, cWarunek

	local cZapytanie := "Select ", i, aRet := {}, aPom, nRow
	local xFormatDaty
	
	xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
	
	if cWarunek = NIL
		cWarunek := ""
	endif
	
	cZapytanie += cNazwaPola + " from " + cNazwaTabeli
	if !empty(cWarunek)
		cZapytanie += " where "+cWarunek
	endif
	
		DBUSEAREA( .T.,, cZapytanie, "TempDBF" )
		do while !eof()
			aadd(aRet, FieldGet(1))
			DBSkip()
		enddo
		DBCloseAll()

	SET( _SET_DATEFORMAT, xFormatDaty)

 return aRet

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 5:25 pm

Code: Select all

function SQL_GetRow
	param cNazwaTabeli, aListaPol, aTypyPol, cWarunek
	local cZapytanie := "Select ", i, aRet := {}, aPom, nRow
	local xFormatDaty
   xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )

	if cWarunek = NIL
		cWarunek := ""
	endif
	if aListaPol = NIL
		cZapytanie += "*"
	else
		for i:= 1 to len(aListaPol)
			cZapytanie += aListaPol[i] + ","
		next i
		cZapytanie := left(cZapytanie, len(cZapytanie)-1)
	endif
	cZapytanie += " from " + cNazwaTabeli
	if !empty(cWarunek)
		cZapytanie += " where "+cWarunek
	endif
	

	DBUSEAREA( .T.,, cZapytanie, "tempDBF" )
	if aListaPol = NIL
		aListaPol := {}
		for i:=1 to fcount()
			AAdd(aListaPol, Fieldname(i))
			AAdd(aTypyPol, FieldType(i))
		next i
	endif
	if !eof()
		for i:=1 to len(aListaPol)
			AAdd(aRet, SQL2Value(aTypyPol[i],FieldGet(i)))
		next i
	endif
	DBCloseAll()

	SET( _SET_DATEFORMAT, xFormatDaty)
return aRet

Code: Select all

function SQL_GetColumn
	param cNazwaTabeli, cNazwaPola, cWarunek

	local cZapytanie := "Select ", i, aRet := {}, aPom, nRow
	local xFormatDaty
	
	xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
	
	if cWarunek = NIL
		cWarunek := ""
	endif
	
	cZapytanie += cNazwaPola + " from " + cNazwaTabeli
	if !empty(cWarunek)
		cZapytanie += " where "+cWarunek
	endif
	
		DBUSEAREA( .T.,, cZapytanie, "TempDBF" )
		do while !eof()
			aadd(aRet, FieldGet(1))
			DBSkip()
		enddo
		DBCloseAll()

	SET( _SET_DATEFORMAT, xFormatDaty)

 return aRet

Code: Select all

function SQL_SaveRecord
	param cNazwaTabeli, aListaPol, aListaWartosci
	local cPolecenieSQL
	local lOdp := .f.
	local xFormatDaty
	
	xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )

	DBUSEAREA( .T.,,"SELECT NrKolejny FROM "+cNazwaTabeli +" ORDER BY NrKolejny DESC", "temp" )
	if LastRec() == 0
		NrKolejny := 1
	else
		NrKolejny := FieldGet(1)+1
	endif
	use


	&(cNazwaTabeli+"_NrKolejny") := NrKolejny
	
	cPolecenieSQL := "INSERT INTO "+cNazwaTabeli + " (NrKolejny"
	for i:= 1 to len(aListaPol)
		
		cPolecenieSQL += ',' +aListaPol[i]
	next i
	cPolecenieSQL += ') VALUES (' + alltrim(str(NrKolejny))
	for i:= 1 to len(aListawartosci)
		cPolecenieSQL += ','+ c2sql(aListaWartosci[i])
	next i
	cPolecenieSQL += ')'
	lodp := RDDINFO(RDDI_EXECUTE, cPolecenieSQL)

	SET( _SET_DATEFORMAT, xFormatDaty)

 	
 return lOdp

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 5:27 pm

New #ID function...

Code: Select all

function SQL_NowyNrKolejny
	param cNazwaTabeli
	#IFDEF SQL_SQLMIX
			DBUSEAREA( .T.,,"SELECT NrKolejny FROM "+cNazwaTabeli +" ORDER BY NrKolejny DESC", "temp" )
			if LastRec() == 0
				NrKolejny := 1
			else
				NrKolejny := FieldGet(1)+1
			endif
			use
	#ENDIF
	
 return NrKolejny

User avatar
mol
Posts: 3185
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 211 times
Been thanked: 112 times
Contact:

Post by mol » Mon Feb 27, 2012 5:30 pm

Code: Select all


*-------------------------------------
function SQL_DeleteRecord
	param cNazwaTabeli, cWarunek
	local cPolecenieSQL
	local lOdp := .f., i, cLista := ""

	local xFormatDaty
	
	xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )

	cPolecenieSQL := "DELETE FROM " +cNazwaTabeli 
	if !empty(cWarunek)
		cPolecenieSQL += " WHERE " + cWarunek
	endif

	lodp := RDDINFO(RDDI_EXECUTE, cPolecenieSQL)
	SET( _SET_DATEFORMAT, xFormatDaty)

	
 return lOdp
*---------------

function SQL_AnySQL
	param cPolecenieSQL
	local lOdp := .f., i, cLista := ""

	local xFormatDaty
	
	xFormatDaty := SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
	lOdp := RDDINFO(RDDI_EXECUTE, cPolecenieSQL)
	SET( _SET_DATEFORMAT, xFormatDaty)	
 return lOdp


huangchenmin
Posts: 157
Joined: Mon Jun 07, 2010 2:24 am

Post by huangchenmin » Tue Feb 28, 2012 12:22 am

mol wrote:I've integrated MS SQL Express with HMG via ODBC. I Put my code, if you are interested...
[/code]
Am I interesting in that ?
Oh my GOD, this is a present from heaven.
Would you mind if I print it out and study it ?
Could I use those code in my routine ?
Best Regards
chen min

Post Reply