ADODB MySql! ( Solved! )

[JC]

Dear Antonio,

I tried to executing a testxbr3.Prg sample with my database configurations... I need some assistance:

The function to connect... That's ok!

Code: Select all  Expand view

static function ConnectToMySql

   local lConnect := .f.
   //local cStr   := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ;
   //         cFilePath( hb_argv( 0 ) ) + "xbrtest.mdb;User Id=admin;Password=;"

   local cStr := 'Provider=MSDASQL.1;' +;
                 'Extended Properties="DATABASE=genesis;DRIVER={MySQL ODBC 5.1 Driver};' +;
                 'Server=localhost;User=genesis;Password=genesis;Port=3306;Option =3"'

   if oCon == nil
      oCon                     := TOleAuto():new("ADODB.Connection")
      oCon:ConnectionString   := cStr
      TRY
         oCon:Open()
         lConnect := .t.
      CATCH
         oCon                  := nil
         MsgInfo('Connect Fail')
         return nil
      END
   else
      lConnect := .t.
   endif

return lConnect

The function to get the recordset.... oRs:recordCount() returns a error!

Code: Select all  Expand view

static function GetRecSet

   static oRs

   local oRecSet

   ConnectToMySql()

   if oCon != nil .and. oRs == nil

      oRs                     := TOleAuto():new( "ADODB.RecordSet" )
//      oRs:ActiveConnection    := oCon
//      oRs:Source               := 'select * from documentos_series'
      oRs:LockType            := 4            // adLockOptimistic
      oRs:CursorLocation      := 3            //adUseClient
//      oRs:CacheSize           := 100

      TRY
         oRs:Open( 'select * from documentos_series', oCon, 3 )  // Only by this way work!
         ? oRs:fields("codigo_serie"):value, oRs:fields("descricao"):value  // Show me the respective values at first row of dataset
      CATCH
         MsgInfo('Access Table Open Failure')
         return nil
      END
   endif

  ? oRs:recordCount()  // Give me a error!
return oRs

The error on error.log

Code: Select all  Expand view

Application
===========
   Path and name: H:\apps\FWH8.10\FWH\samples\testxbr3.exe (32 bits)
   Size: 2,022,912 bytes
   Time from start: 0 hours 1 mins 11 secs
   Error occurred at: 23-01-2009, 10:01:22
   Error description: Error ADODB.RecordSet/6  DISP_E_UNKNOWNNAME: RECORDCOUNT
   Args:

Stack Calls
===========
   Called from: source\rtl\win32ole.prg => TOLEAUTO:RECORDCOUNT(0)
   Called from: testxbr3.prg => GETRECSET(1046)
   Called from: testxbr3.prg => ADOBRWALLCOLSWIN(343)
   Called from: testxbr3.prg => (b)MAINMENU(87)
   Called from: .\source\classes\MENU.PRG => TMENU:COMMAND(0)
   Called from: .\source\classes\WINDOW.PRG => TWINDOW:COMMAND(0)
   Called from: .\source\classes\MDIFRAME.PRG => TMDIFRAME:COMMAND(0)
   Called from:  => TMDIFRAME:HANDLEEVENT(0)
   Called from: .\source\classes\WINDOW.PRG => _FWH(0)
   Called from:  => WINRUN(0)
   Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE(0)
   Called from: testxbr3.prg => MAIN(42)

[Armando]

JC:

This is a sample and it works fine to me

1.- To create the conection

Code: Select all  Expand view

/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
STATIC FUNCTION Conecta()
LOCAL oError
TRY
   oApp:oCon   :=   TOleAuto():new("adodb.connection")
CATCH oError
   MsgStop( "No se ha podido crear la conexión al servidor !", oApp:cAplicacion)
   RETURN(.F.)
END

oApp:oCon:ConnectionString := "Driver={MySQL ODBC 3.51 Driver};Server=" + ALLTRIM(oApp:cServer) + ;
                              ";Port=3306;Database=" + ALLTRIM(oApp:cDataBase) + ;
                              ";User=" + ALLTRIM(oApp:cUser)+;
                              "; Password=" + ALLTRIM(oApp:cPassWord) + ";Option=3;"

TRY
   oApp:oCon:Open()
CATCH oError
   MsgInfo("No se pudo lograr la conexión al servidor, REVISE LA CONEXION DE SU RED O LA CONEXION A INTERNET !",oApp:cAplicacion)
   ShowError(oError)
   RETURN(.F.)
END
RETURN(.T.)


2.- Then you should to activate the database

Code: Select all  Expand view

// Hacemos activa la Base de Datos
cCmdSql   := "USE " + ALLTRIM(oApp:cDataBase)
TRY
   oApp:oCon:Execute(cCmdSql)
CATCH oError
   MsgInfo("No se pudo abrir la base de datos, posiblemente no exista !",oApp:cAplicacion)
   ShowError(oError)
   RETURN(.F.)
END


3.- And finally to create the record set

Code: Select all  Expand view

TRY
   oRsEmp   :=   TOleAuto():New("adodb.recordset")
CATCH oError
   MsgStop( "No se ha podido crear el RECORDSET de Empresas !", oApp:cAplicacion)
   ShowError(oError)
   oRsEmp   :=   NIL
   RETURN(.F.)
END

oRsEmp:CursorLocation   := adUseClient
oRsEmp:LockType         := adLockOptimistic
oRsEmp:CursorType         := adOpenDynamic
oRsEmp:Source            := "SELECT " +;
                              "* " +;
                           "FROM " +;
                              "empresa"
oRsEmp:ActiveConnection(oApp:oCon)

TRY
   oRsEmp:Open()
CATCH oError
   MsgStop( "No se ha podido abrir el RECORDSET de Empresas !", oApp:cAplicacion)
   ShowError(oError)
   RETURN(.F.)
END


I hope this can help you, regards

PS, pls take a look to my blog you'll find many samples
http://sqlcmd.blogspot.com/

[JC]

Armando,

Thank you very much! I appreciate your help!
I will try it!

[JC]

Armando,

Work perfectly!
But, with TxBrowse... How I can set the recordSet for it?

[Armando]

Júlio César M. Ferreira

Please visit my blog you'll find a sample with TXbrowse & Recordset

Regards

[JC]

Armando,

I make! My xBrowse now opens the oRs from my database.
But, when I execute a select... then the give me a error!


If I execute the query: "SHOW TABLES" -> That's all ok!
But, if I execute the query: "SELECT * FROM my_table" -> error RECORDCOUNT!

Code: Select all  Expand view

oCon := TOleAuto():new( "ADODB.Connection" )

oCon:connectionString := "Driver={MySQL ODBC 5.1 Driver}" + ;
                         ";Server=localhost"              + ;
                         ";DefaultDatabase=aservit"       + ;
                         ";Database=aservit"              + ;
                         ";Port=3306"                     + ;
                         ";User=myuser"                  + ;
                         ";Password=mypass"              + ;
                         ";Option=3;"
oCon:open()

Code: Select all  Expand view

oRs:cursorLocation := 3 //adUseClient
oRs:lockType       := 3 //adLockOptimistic
oRs:cursorType     := 2 //adOpenDynamic
oRs:source         := "select * from plano_contas"
oRs:activeConnection( oCon )
oRs:open()

Why?

[Armando]

Júlio César:

Are you sure the table has records ?, or is an empty table ?

Regards

[JC]

>> Are you sure the table has records ?, or is an empty table ?

Yes Armando, 17676 records into a table!

[Armando]

Julio César:

Could you post the code to create the recordset ?

Regards

[JC]

Armando,

This is the source:

Code: Select all  Expand view

TRY
   
   oRs := TOleAuto():New("adodb.recordset")

CATCH oError

   showError( oError )
   oRs := NIL
   RETURN( .F. )

END

oRs:CursorLocation := adUseClient
oRs:LockType       := adLockOptimistic
oRs:CursorType     := adOpenDynamic
oRs:Source         := "SELECT * FROM plano_contas"

oRs:ActiveConnection( oCon )

TRY

   oRs:Open()

CATCH oError

   showError(oError)
   RETURN( .F. )

END

[Armando]

Julio:

Ummmm, perhaps it must to be the permissions of the user.

Regards

[JC]

Armando,

I has used the GRANT ALL command for the user, with all privileges.. And, with the MySQL Query Browser, I can execute this query.

[Rochinha]

Julio,

put the variable oRs as static or public at the beginning of your main prg and try.

[JC]

Armando,

I tried execute this code into a database with many tables... all they empty. When the same table are filled and I execute the same code again... then return to me a error RECORCCOUNT

[JC]

Antonio and friends,

This is my example for testing!
Please, modify and check!

http://rapidshare.com/files/189783905/ADODB.zip.html