MySQL - ADO Query problem

[Marc Vanzegbroeck]

Hi,

I have an unexpected result when I run a Query in SQL with ADO.

When I run oQry = oSQL:execute("SELECT * FROM klanten")
Then I can do
FOR i = 0 to len(oqry:Fields())
?oQry:Fields(i):value
NEXT i

but when I run oQry = oSQL:execute("SELECT NAAM FROM klanten")
Then I get an error because Fields(1) doesn't exist
I thought that Fields(1) now was holding the info of the first field in the query. It seems that that is the first field in tha table.
I can get the information with oQry:Fields("NAAM"):value

I have create a subroutine that give me the result of a query in a database, but it doesn'n work when not all the fields are requested.
How can I know the fieldnames retured by a query? That's why I was using oQry:Fields(i):value instead of the name.
With Qry:Fields(i):name I know the name.

[Enrico Maria Giordano]

You have to use Recordset object. Please look at the samples.

EMG

[Marc Vanzegbroeck]

You have to use Recordset object. Please look at the samples.

EMG

My mistake

I had to use oQry:Fields(0):value instead of oQry:Fields(1):value

[Enrico Maria Giordano]

Yes, but please look at Recordset object too. It's more common to use it instead of Execute() method for row-returning queries.

EMG

[Marc Vanzegbroeck]

Yes, but please look at Recordset object too. It's more common to use it instead of Execute() method for row-returning queries.

EMG

I have try with the recordset, but with no luck.

This is my code

Code: Select all  Expand view  RUN

oRs :=TOleAuto():new("ADODB.RecordSet")
oRs:cursortype :=1
oRs:cursorlocation :=3
oRs:locktype :=3
oRs:open('SELECT * FROM planning','Provider=MySQLProv;Data Source=127.0.0.1;Initial Catalog=pla;User Id=myuser;Password=mypassword')
 

The oRs:open gives an error/

This code is what I'm using now and this is working fine, but no recordsets

Code: Select all  Expand view  RUN

cConnectSring:="Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=pla;User=myuser;Password=mypassword;Option=3;"
oSQL:=CreateObject("ADODB.Connection")
oSQL:ConnectionString:=cConnectSring
oSQL:Open()
oQry = oSQL:Execute('SELECT * FROM planning')
 

What can be the problem?

[Enrico Maria Giordano]

Code: Select all  Expand view  RUN

oRs :=TOleAuto():new("ADODB.RecordSet")

Try

Code: Select all  Expand view  RUN

oRs = CREATEOBJECT( "ADODB.Recordset" )

EMG

[Enrico Maria Giordano]

And try using the same connection string. Why are you using a different one?

EMG

[Marc Vanzegbroeck]

Code: Select all  Expand view  RUN

oRs :=TOleAuto():new("ADODB.RecordSet")

Try

Code: Select all  Expand view  RUN

oRs = CREATEOBJECT( "ADODB.Recordset" )

EMG

Same result..

Code: Select all  Expand view  RUN

Application
===========
   Path and name: c:\FWH\MySQL\ADO\testado.exe (32 bits)
   Size: 1,302,528 bytes
   Time from start: 0 hours 0 mins 1 secs
   Error occurred at: 10/09/12, 11:52:00
   Error description: Error ADODB.RecordSet/16389  E_FAIL: OPEN
   Args:
     [   1] = C   SELECT * FROM planning
     [   2] = C   Provider=MySQLProv;Data Source=127.0.0.1;Initial Catalog=pla;User Id=myuser;Password=mypassword

[Marc Vanzegbroeck]

And try using the same connection string. Why are you using a different one?

EMG

I was looking in the forum and always found an example like Driver={MySQL ODBC 5.1 ... with CreateObject("ADODB.Connection")
and 'Provider=MySQLProv... with CREATEOBJECT( "ADODB.Recordset" ) ..

It's working with the same connectionstring!!!!

[Rick Lipkin]

Marc

Have a look at these connection strings for My Sql .. here is the connection for My Sql OLEDB:

http://connectionstrings.com/mysql#mysq ... -mysqlprov

MySQL OLEDB
Type OLE DB Provider
Usage Provider=MySQLProv
Manufacturer MySQL

Set example values
Standard

Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;

Code: Select all  Expand view  RUN

xConnect := "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword"

cSql := "SELECT * FROM planning"

oRsPlan := TOleAuto():New( "ADODB.Recordset" )
oRsPlan:CursorType     := 1        // opendkeyset
oRsPlan:CursorLocation := 3        // local cache
oRsPlan:LockType       := 3        // lockoportunistic

TRY
   oRsPlan:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening PLANNING table" )
   RETURN(.F.)
END TRY

xBrowse( oRsPlan )
 

Also .. make sure you have the correct MySql Oledb provider .. check out this website and scroll down to the bottom for the different type of providers. Also look at the Vb examples .. they have some different connection strings to try as well.

http://cherrycitysoftware.com/ccs/provi ... MySQL.aspx

Rick Lipkin

[Marc Vanzegbroeck]

Marc

Have a look at these connection strings for My Sql .. here is the connection for My Sql OLEDB:

http://connectionstrings.com/mysql#mysq ... -mysqlprov

MySQL OLEDB
Type OLE DB Provider
Usage Provider=MySQLProv
Manufacturer MySQL

Set example values
Standard

Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;

Code: Select all  Expand view  RUN

xConnect := "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword"

cSql := "SELECT * FROM planning"

oRsPlan := TOleAuto():New( "ADODB.Recordset" )
oRsPlan:CursorType     := 1        // opendkeyset
oRsPlan:CursorLocation := 3        // local cache
oRsPlan:LockType       := 3        // lockoportunistic

TRY
   oRsPlan:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening PLANNING table" )
   RETURN(.F.)
END TRY

xBrowse( oRsPlan )
 

Also .. make sure you have the correct MySql Oledb provider .. check out this website and scroll down to the bottom for the different type of providers. Also look at the Vb examples .. they have some different connection strings to try as well.

http://cherrycitysoftware.com/ccs/provi ... MySQL.aspx

Rick Lipkin

Rick,

Thanks for your example, but with Provider=MySQLProv;Data Source=pla;User Id=myuser;Password=mypassword; I get also an error but {MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=pla;User=myuser;Password=mypassword;Option=3; is working fine