ODBC

[Colin Haig]

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

[fafi]

Try this :

Code: Select all  Expand view

#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 nil
 

Best Regards
Fafi

[Colin Haig]

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

[Gale FORd]

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

[kennedyv]

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

[Rick Lipkin]

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

[Colin Haig]

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.

Code: Select all  Expand view

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