How HMG works with SQL server express ?
Moderator: Rathinagiri
-
- Posts: 157
- Joined: Mon Jun 07, 2010 2:24 am
How HMG works with SQL server express ?
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
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
-
- Posts: 157
- Joined: Mon Jun 07, 2010 2:24 am
Re: How HMG works with SQL server express ?
Well, I found some sample code in C:\hmg.3.0.35\SAMPLES\RDD.SQL\mdbhuangchenmin wrote:Dear all:
I need to integrate data from SQL server express and DBF.
.................
chen min
Truely speaking, It is really difficult to me!
complete could not figure it out!
Re: How HMG works with SQL server express ?
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 = ""
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
Re: How HMG works with SQL server express ?
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
Re: How HMG works with SQL server express ?
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
Re: How HMG works with SQL server express ?
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
Re: How HMG works with SQL server express ?
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
Re: How HMG works with SQL server express ?
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
Re: How HMG works with SQL server express ?
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
-
- Posts: 157
- Joined: Mon Jun 07, 2010 2:24 am
Re: How HMG works with SQL server express ?
Am I interesting in that ?mol wrote:I've integrated MS SQL Express with HMG via ODBC. I Put my code, if you are interested...
[/code]
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