Hi All
I am trying to pass information from my software into a SQL database using ODBC - I have
set up the DNS connection and get a successful connection.
How do I do an insert statement and commit.
Regards
Colin
FiveTech Support Forums
FiveWin / Harbour / xBase community
Try this :
Best Regards
Fafi
#INCLUDE "FIVEWIN.CH"
// Cursor Type
#define adOpenForwardOnly 0
#define adOpenKeyset 1
#define adOpenDynamic 2
#define adOpenStatic 3
// Lock Types
#define adLockReadOnly 1
#define adLockPessimistic 2
#define adLockOptimistic 3
#define adLockBatchOptimistic 4
// Field Types
#define adEmpty 0
#define adTinyInt 16
#define adSmallInt 2
#define adInteger 3
#define adBigInt 20
#define adUnsignedTinyInt 17
#define adUnsignedSmallInt 18
#define adUnsignedInt 19
#define adUnsignedBigInt 21
#define adSingle 4
#define adDouble 5
#define adCurrency 6
#define adDecimal 14
#define adNumeric 131
#define adBoolean 11
#define adError 10
#define adUserDefined 132
#define adVariant 12
#define adIDispatch 9
#define adIUnknown 13
#define adGUID 72
#define adDate 7
#define adDBDate 133
#define adDBTime 134
#define adDBTimeStamp 135
#define adBSTR 8
#define adChar 129
#define adVarChar 200
#define adLongVarChar 201
#define adWChar 130
#define adVarWChar 202
#define adLongVarWChar 203
#define adBinary 128
#define adVarBinary 204
#define adLongVarBinary 205
#define adChapter 136
#define adFileTime 64
#define adPropVariant 138
#define adVarNumeric 139
#define adArray // &H2000
#define adRecDeleted 4
#define adSearchForward 2
#define adSearchBackward 1
#define adUseNone 1
#define adUseServer 2
#define adUseClient 3
#define adUseClientBatch 4
#define adKeyForeign 2
static oRecordSet,oConnection,oBrow
FUNCTION MAIN()
oConnection := TOleAuto():New( "ADODB.Connection" )
TRY
oConnection:Open("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test; User=root;Password=;Option=3;")
CATCH oErr
MsgAlert( "Error to konek basepro" )
return nil
END TRY
oRecordSet := TOleAuto():New( "ADODB.Recordset" )
oRecordSet:CursorType := adOpenDynamic
oRecordSet:CursorLocation := adUseClient
oRecordSet:LockType := adLockOptimistic
oRecordSet:Index := "id_code"
TRY
oRecordSet:Open( "SELECT * FROM EMPLOYEE", oConnection )
CATCH oErr
MsgAlert( "Error to Open Employee" )
return nil
END TRY
if oRecordSet:BOF() .or. oRecordSet:Eof()
MsgAlert( "Record Empty, create one" )
oRecordSet:AddNew()
oRecordSet:Fields("id_code" ):Value := "001"
oRecordSet:Fields("id_name" ):Value := "FAFI"
oRecordSet:Fields("id_date" ):Value := date()
oRecordSet:Fields("id_age" ):Value := 10
oRecordSet:Fields("id_memo" ):Value := "xharbour with fivewin"
oRecordSet:Update()
endif
define dialog oDlg from 1,1 to 400,700 pixel
@ 0, 0 LISTBOX oBrow FIELDS ;
oRecordset:Fields("id_code" ):Value,;
oRecordset:Fields("id_name" ):Value, ;
dtoc(oRecordset:Fields("id_date" ):Value), ;
str(oRecordset:Fields("id_age" ):Value,3), ;
oRecordset:Fields("id_memo" ):Value ;
SIZES 80,200,80,60,200 ;
HEADERS "Code","Name","Date","Age","Info" SIZE 300,200 of oDlg
oBrow:bLogicLen := { || oRecordset:RecordCount }
oBrow:bGoTop := { || oRecordset:MoveFirst() }
oBrow:bGoBottom := { || oRecordset:MoveLast() }
oBrow:bSkip := { | nSkip | Skipper( oRecordset, nSkip ) }
oBrow:cAlias := "ARRAY1"
@5,310 button "Add" size 30,12 of oDlg pixel action RecordAction(.t.)
@25,310 button "Edit" size 30,12 of oDlg pixel action RecordAction(.f.)
@40,310 button "Find" size 30,12 of oDlg pixel action RecordFind()
@60,310 button "Delete" size 30,12 of oDlg pixel action RecordDelete()
ACTIVATE DIALOG oDlg centered
oRecordset:Close()
RETURN NIL
STATIC FUNCTION SKIPPER( oRsx, nSkip )
LOCAL nRec := oRsx:AbsolutePosition
oRsx:Move( nSkip )
IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF
RETURN( oRsx:AbsolutePosition - nRec )
static function RecordAction(lAdd)
local lSave := .f.
if lAdd
cId_code := spac(3)
cId_name := spac(10)
cId_date := date()
cId_age := 10
cId_memo := spac(100)
else
cId_code := oRecordSet:Fields("id_code" ):Value
cId_name := oRecordSet:Fields("id_name" ):Value
cId_date := oRecordSet:Fields("id_date" ):Value
cId_age := oRecordSet:Fields("id_age" ):Value
cIde_meno:= oRecordSet:Fields("id_memo" ):Value
endif
define dialog oDlgRecord from 1,1 to 200,200 pixel title if(lAdd,"Add Record","Edit Record")
@1,1 say "Code" size 50,12 of oDlgRecord pixel
@15,1 say "Name" size 50,12 of oDlgRecord pixel
@1,30 get cId_Code size 20,12 of oDlgRecord pixel
@15,30 get cId_Name size 50,12 of oDlgRecord pixel
@30,20 button "Save" size 30,12 of oDlgRecord pixel action ( lSave := .t.,oDlgRecord:End() )
@30,60 button "Cancel" size 30,12 of oDlgRecord pixel action ( lSave := .f.,oDlgRecord:End() )
activate dialog oDlgRecord centered
if empty(cId_Code) .or. empty(cId_Name)
lSave := .f.
endif
if lSave
if lAdd
oRecordSet:AddNew()
endif
oRecordSet:Fields("id_code" ):Value := cId_code
oRecordSet:Fields("id_name" ):Value := cId_name
oRecordSet:Fields("id_date" ):Value := date()
oRecordSet:Fields("id_age" ):Value := 0
oRecordSet:Fields("id_memo" ):Value := ""
oRecordSet:Update()
if lAdd
oBrow:goBottom()
else
oBrow:Refresh()
endif
endif
return nil
static function RecordFind()
local lSave := .f.
cId_Name := oRecordSet:Fields("id_name" ):Value
define dialog oDlgRecord from 1,1 to 200,200 pixel title "Find Record"
@1,1 say "Name" size 50,12 of oDlgRecord pixel
@1,30 get cId_Code size 40,12 of oDlgRecord pixel
@30,20 button "Find" size 30,12 of oDlgRecord pixel action ( lSave := .t.,oDlgRecord:End() )
@30,60 button "Cancel" size 30,12 of oDlgRecord pixel action ( lSave := .f.,oDlgRecord:End() )
activate dialog oDlgRecord centered
if lSave
oRecordSet:Close()
if empty(cId_name)
oRecordSet:Open( "SELECT * FROM EMPLOYEE", oConnection )
oBrow:goTop()
else
cId_name := "'"+alltrim(cId_name)+"'"
oRecordSet:Open( "SELECT * FROM EMPLOYEE WHERE ID_NAME="+cId_name, oConnection )
if oRecordSet:Eof()
oRecordSet:Close()
oRecordSet:Open( "SELECT * FROM EMPLOYEE", oConnection )
oBrow:goTop()
endif
endif
oBrow:Refresh()
endif
return nil
static function RecordDelete()
if MsgYesNo("Delete ?")
nRec := oRecordSet:AbsolutePosition()
nLast := oRecordSet:RecordCount()
oRecordSet:delete()
if nRec == nLast
oRecordSet:MovePrevious()
else
oRecordSet:MoveNext()
endif
oBrow:Refresh()
endif
return nilBest Regards
Fafi
Fafi
Thanks for the sample code - I am trying to connect to a MYOB ( accounting package) - I think it is
an access data file - the developers kit I purchased suggests using ODBC but I will check and see if your
sample code will open the database - your code looks a lot more useful than what came with the developers kit.
Cheers
Colin
You can use DNS or DNS-less connection. I can get you an example of both if you need it. I prefer a DNS-less connection so I don't have to set it up on each workstation. I can also change it easier.
Here is a good link to see the ADO connect strings for many different database systems.
http://www.codemaker.co.uk/it/tips/ado_conn.htm
Here is a good link to see the ADO connect strings for many different database systems.
http://www.codemaker.co.uk/it/tips/ado_conn.htm
Assuming you are using FiveWin's TODBC class use its :execute() method passing the SQL command as its first parameter - all other parameters are optional.
This method returns .T. if the comand succeded or .F. if it failed. If the SQL command failed an error message is displyaed if :lShowError is set to .T. and a runtime error will result if :lAbort is set to .T.
Vincent
Colin
ADO is the best way to connect to most RDMS, Sql Server, Oracle and Access. Gale Ford pointed out a good web site that has examples of connection strings ..
Here is the wiki on ADO and there are some good examples of using and connecting to an Access database.
http://wiki.fivetechsoft.com/doku.php?i ... ted_stuffs
Rick Lipkin
Hi All
I am not sure who re openned this thread but I have been interfacing with the MYOB accounting
package for ages now - using sql inserts - the MYOB datafile does not allow Updates or Deletes.
Cheers
Colin
I am not sure who re openned this thread but I have been interfacing with the MYOB accounting
package for ages now - using sql inserts - the MYOB datafile does not allow Updates or Deletes.
function fnConnect(oCon,aPrivs)
local cStr,oError,nAdoErrors := 0,oAdoErr,bDataFile := {||alltrim(aPrivs[33]) },bMyobProg := {|| alltrim(aPrivs[32]) },;
bMyobUser := {|| alltrim(aPrivs[30]) }, bMyobPass := {|| alltrim(aPrivs[31]) }
cStr := "Driver={MYOAU1001}; ACCESS_TYPE=READ_WRITE; TYPE=MYOB; UID=" + eval(bMyobUser) + "; PWD=" + eval(bMyobPass) +"; DATABASE=" + eval(bDataFile) + "; HOST_EXE_PATH=" + eval(bMyobProg) + "; NETWORK_PROTOCOL=TCPIP; DRIVER_COMPLETION=DRIVER_NOPROMPT"
if oCon == nil
oCon := TOleAuto():new("ADODB.Connection")
oCon:ConnectionString := cStr
TRY
oCon:Open()
lConnect := .t.
CATCH oError
nAdoErrors := oCon:errors:Count()
IF nAdoErrors > 0
oAdoErr := oCon:Errors(nAdoErrors-1)
ELSE
msginfo( 'Not MYOB Error' )
ENDIF
oCon := nil
return nil
END
else
lConnect := .t.
endif
return(oCon)
//---------------------------------------------------------------------------------------------------------------------------------------//
Function ErrorCatch( oError, cTitle )
LOCAL cMessage := ""
local cArg, cArgs := ""
if ValType( oError:Args ) == "A"
for each cArg in oError:Args
cArgs += cValToChar( cArg ) + CRLF
NEXT
endif
cMessage := "[Subsystem]" + oError:SubSystem + CRLF +;
"[SubCode]" + alltrim( str( oError:SubCode ) ) + CRLF +;
"[Operation]" + oError:Operation + CRLF +;
"[Description]" + oError:Description + CRLF +;
"[Arguments]" + cArgs
RETURN( msgStop( cMessage, cTitle ) )
//----------------------------------------------------------------------------------------------------------------//Cheers
Colin