MySQL troubles

[zeco]

Hi All,

I've been wrestling with this several days now and have isolated the problem area; mysql connection through ADODB.

In the following code I always get a -1 as RecordCount, while in other applications the DSN works like a charm. I must be doing something wrong here...

; zeco = MySQL ODBC driver, working from other apps.

$ado = ObjCreate( "ADODB.Connection" )
$dsn = "DSN=zeco"
$ado.Open( $dsn )

$adors = ObjCreate( "ADODB.RecordSet" )

$sql = "SELECT name FROM test"
$adors.Open( $sql, $ado )

MsgBox( 0, "Result", "Nr. of Records: " & $adors.RecordCount )

$ado.close

I get the same error when trying a textfile-DSN (as in another topic) so it must be something in my code.

Anybody got an idea? :">

Regards,

Paul

[DaleHohm]

It might help if you can post a working example in VBScript...

Dale

[zeco]

I do not program in VBScript, but will have a look. I searched the MS site for info and this Forum also supplied some info. But neither solutions seem to work...

Maybe there will be a A3beta wilt SQLLite before I solve this

Regards,

Paul

[DaleHohm]

Ah, OK. So this is an ADO question, not an AutoIt question then...

This is not an issue with your DSN, it is an issue with the default cursor type used when you open the recordset.

The default of forward only won't give you what you need because it does not automaticcally traverse the records in the recordset. Another cursor, like adOpenStatic (which has an Enum value of 3) will do the trick.

Change your recordset open commandline to this:

$adors.Open( $sql, $ado, 3 )

Dale

[zeco]

adOpenStatic (which has an Enum value of 3) will do the trick.

Change your recordset open commandline to this:

$adors.Open( $sql, $ado, 3 )

Dale,

Tried this and still does not work. I searched the MS site for this ENUM value earlier , but could not find the spec. Also I thought maybe it is the default LockType or so, but also can't find some info.

Maybe if I convert it to VBS and then try to get it to work... *sigh* and I promised myself I would not touch VBS

Got any tips? :">

Thanks so far!

Regards,

Paul

[DaleHohm]

Dale,

Tried this and still does not work. I searched the MS site for this ENUM value earlier , but could not find the spec. Also I thought maybe it is the default LockType or so, but also can't find some info.

Maybe if I convert it to VBS and then try to get it to work... *sigh* and I promised myself I would not touch VBS 

Got any tips?  :">

Thanks so far!

Regards,

Paul

<{POST_SNAPBACK}>

Hmmm, OK, then yes, try the locktype as well. I'd suggest 3 (adLockOptimistic), so:

$adors.Open( $sql, $ado, 3, 3 )

I was able to reproduce the -1 with other ODBC connections with the forward cursor, but got the correct value when using Static.

If you're trying to find Enums, see my post Extract Enum Constants and create Au3 constants -- If you use "ADODB.RecordSet" as the classname you'll see all you need.

It's pretty easy to convert back and forth from AutoIt to VBScript, so I'd suggest you do that if you continue to have trouble. The COM support in AutoIt beta is pretty solid now, but that will take the finger-pointing out of the troubleshooting.

Dale

Edited June 13, 2005 by DaleHohm

[zeco]

Hmmm, OK, then yes, try the locktype as well.  I'd suggest 3 (adLockOptimistic), so:

$adors.Open( $sql, $ado, 3, 3 )

If you're trying to find Enums, see my post Extract Enum Constants and create Au3 constants -- If you use "ADODB.RecordSet" as the classname you'll see all you need.

Dale,

Thanks again for your suggestions, however, also the ,3 ,3 didn't do the trick.

So I decided to try me some vbs... The following code works like a charm:

set conn=CreateObject("ADODB.Connection")
Conn.Open "DSN=zeco"
set rs=CreateObject( "ADODB.Recordset" )
rs.Open "SELECT * FROM test", conn
do until rs.EOF
 wscript.echo rs.Fields("name")
 rs.MoveNext
loop
rs.close

But when I do the same in AutoIT is gives me 4 times (this IS the correct number of returned rows) a wrong value, it gives me"XjC" 4 times...

This is what I did in AutoIT:

$ado = ObjCreate( "ADODB.Connection" )
$ado.Open( "DSN=zeco" )
$adors = ObjCreate( "ADODB.RecordSet" )
$adors.Open( "SELECT name FROM test", $ado )
While not $adors.EOF
    MsgBox(0, "Result", "Name = " & $adors.Fields("name") )
    $adors.MoveNext
WEnd
$ado.close

Do I need to "typecast" the return value of $adors.Fields function or something? I have no expertise in this area when it comes to AutoIT....

I'm starting to beat my head against my TFT

Regards,

Paul

[zeco]

Yeahhhhhh I solved it:

MsgBox(0, "Result", "Name = " & $adors.Fields( "name" ).Value )

the .Value was the clue here...

Now see If I can het the recordcount to work tomorrow.

Thanks Dale, for all the support!

Regards,

Paul

[DaleHohm]

What happens if you force the .Value property on the recordset fields item?

MsgBox(0, "Result", "Name = " & $adors.Fields("name") .Value)

Dale

Edit: Ah, reply collision -- glad you found it too.

Edited June 13, 2005 by DaleHohm

[flyingboz]

Just a bump to see if anyone has got some working syntax for using ado.

[DaleHohm]

Not sure what you're asking for... this thread includes a working example.

Dale