Page 1 of 2
ADODB MySql! ( Solved! )
Posted: Fri Jan 23, 2009 1:00 pm
by 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
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
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
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)
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 3:46 pm
by Armando
JC:
This is a sample and it works fine to me
1.- To create the conection
Code: Select all
/*
* --------------------------------------------------------------------------*
* --------------------------------------------------------------------------*
*/
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
// 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
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/
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 3:51 pm
by JC
Armando,
Thank you very much! I appreciate your help!
I will try it!
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 7:21 pm
by JC
Armando,
Work perfectly!
But, with TxBrowse... How I can set the recordSet for it?
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 7:34 pm
by Armando
Júlio César M. Ferreira
Please visit my blog you'll find a sample with TXbrowse & Recordset
Regards
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 9:05 pm
by 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
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
oRs:cursorLocation := 3 //adUseClient
oRs:lockType := 3 //adLockOptimistic
oRs:cursorType := 2 //adOpenDynamic
oRs:source := "select * from plano_contas"
oRs:activeConnection( oCon )
oRs:open()
Why?
Re: Doubt on ADO MySql!?
Posted: Fri Jan 23, 2009 11:27 pm
by Armando
Júlio César:
Are you sure the table has records ?, or is an empty table ?
Regards
Re: Doubt on ADO MySql!?
Posted: Sat Jan 24, 2009 12:02 am
by JC
>> Are you sure the table has records ?, or is an empty table ?
Yes Armando, 17676 records into a table!
Re: Doubt on ADO MySql!?
Posted: Sat Jan 24, 2009 12:57 am
by Armando
Julio César:
Could you post the code to create the recordset ?
Regards
Re: Doubt on ADO MySql!?
Posted: Sat Jan 24, 2009 1:41 am
by JC
Armando,
This is the source:
Code: Select all
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
Re: Doubt on ADO MySql!?
Posted: Sun Jan 25, 2009 4:15 pm
by Armando
Julio:
Ummmm, perhaps it must to be the permissions of the user.
Regards
Re: Doubt on ADO MySql!?
Posted: Sun Jan 25, 2009 5:37 pm
by 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.
Re: Doubt on ADO MySql!?
Posted: Sun Jan 25, 2009 10:36 pm
by Rochinha
Julio,
put the variable oRs as static or public at the beginning of your main prg and try.
Re: Doubt on ADO MySql!?
Posted: Mon Jan 26, 2009 4:56 pm
by 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
Re: ADODB MySql error!
Posted: Mon Jan 26, 2009 6:17 pm
by JC
Antonio and friends,
This is my example for testing!
Please, modify and check!
http://rapidshare.com/files/189783905/ADODB.zip.html