Cdavis316
asked on
How to execute MySql query and pass to VBscript Variable?
Dear EE,
I'm trying to write a VBS script to execute a simple Mysql query, and dump it off into a variable.... and I'm stuck with the pieces of the puzzle and no means by which to put them together. Here's what I have:
The MySQL query:
SELECT DISTINCT `Column` FROM `Database`.`table` WHERE ID=27 AND ExtraFieldID=53
This query is written in context, so that no more than one cell will be the result. I've figured out how to make the ODBC connection to MYSQL with MyODBC 3.51, but I'm stuck regarding how to execute the query, then dump the resulting record (one cell, string) into a VBS variable. Here's where I'm at:
'***** CONFIG PARAMETERS *******
strServer = "server"
strUser = "username"
strPass = "password"
strDBName = "database name"
'****** END OF CONFIG **********
Set dbConn = CreateObject("ADODB.Connec
dbConn.ConnectionString = "driver={MySQL};server=" & strServer & ";uid=" & strUser & ";pwd=" & strPassword & ";database=" & strDBName
dbConn.Open
So I think i've got the database to open, but I can't find a good example on how to execute the query, then set a variable to the contents of the one cell result. If anyone could point me in the right direction or pick up the ball where I left off, I'm all ears!
Thanks in advance EE!
I'm trying to write a VBS script to execute a simple Mysql query, and dump it off into a variable.... and I'm stuck with the pieces of the puzzle and no means by which to put them together. Here's what I have:
The MySQL query:
SELECT DISTINCT `Column` FROM `Database`.`table` WHERE ID=27 AND ExtraFieldID=53
This query is written in context, so that no more than one cell will be the result. I've figured out how to make the ODBC connection to MYSQL with MyODBC 3.51, but I'm stuck regarding how to execute the query, then dump the resulting record (one cell, string) into a VBS variable. Here's where I'm at:
'***** CONFIG PARAMETERS *******
strServer = "server"
strUser = "username"
strPass = "password"
strDBName = "database name"
'****** END OF CONFIG **********
Set dbConn = CreateObject("ADODB.Connec
dbConn.ConnectionString = "driver={MySQL};server=" & strServer & ";uid=" & strUser & ";pwd=" & strPassword & ";database=" & strDBName
dbConn.Open
So I think i've got the database to open, but I can't find a good example on how to execute the query, then set a variable to the contents of the one cell result. If anyone could point me in the right direction or pick up the ball where I left off, I'm all ears!
Thanks in advance EE!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the assist, I ended up with the following:
Set objRs = dbconn.Execute("SELECT DISTINCT `Value` FROM `labtech`.`extrafielddata`
strResult=objRs(0)
MsgBox strResult
Set objRs = dbconn.Execute("SELECT DISTINCT `Value` FROM `labtech`.`extrafielddata`
strResult=objRs(0)
MsgBox strResult
Sure, that works too, but you will get an error if you don't check for EOF.
ASKER
Why? For my own education, I'm definitely no expert...
When you execute a query, it returns a recordset object, but if no records were found, then any attempt to access any data from that recordset will result in an error, stating that the recordset is already at End Of File (EOF).
Therefore, to overcome that potential error, you just need to check for it:
If Not objRS.EOF Then
strResult=objRs(0)
MsgBox strResult
Else
MsgBox "No records found"
End If
Therefore, to overcome that potential error, you just need to check for it:
If Not objRS.EOF Then
strResult=objRs(0)
MsgBox strResult
Else
MsgBox "No records found"
End If
ASKER
Thanks for the tip!
dbConn.Open
just execute a query, and return the results to a recordset.
Set objRecordset = dbConn.Execute("SELECT DISTINCT 'Column' FROM 'Database'.'table' WHERE ID=27 AND ExtraFieldID=53")
While Not objRecordset.EOF
strValue = objRecordset.Fields("Colum
Wend
MsgBox strValue
Regards,
Rob.