Sign in to follow this  
Followers 0
zeco

MySQL troubles

11 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Dale


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by DaleHohm

Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by DaleHohm

Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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


Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

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

Dale


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0