Any Interest?

[spl]

Going to take a whack at querying MySQL Community Server 8.0.39 via Mysql 64bit ODBC drivers. Involves CLR objects  and I expect some issues. MySQL is a free download as are both the ODBC and .NET connector. Current version is 9.0 but testing with 8.0 version as it still has Workbench installed. Have it working on Win10 with PS, so basically a transfer of code to CLR. This would be a contribution to the forum as an alternate to SQLite, Access, SQL Server for handling data.

[spl]

Assumed:

• Mysql 8.0.39 downloaded/installed as full
• Mysql Odbc 9.0 64 bit drivers installed
• Able to ascertain access to data with Workbench
• Set up additional dbadmin user for testing

Once set up had no issues creating a simple PS script to query the sakila database. Below is code to translate the PS to CLR. Based on earlier CLR WB scripts I have written using .NET DataTables/Adapters was hoping this might work. Added code to display both the connection object and connection string... but just get an unknown name when trying to open the object. Ran in WB 64 just to be sure. Can it be explained that there might be an issue with 64bit ODBC drivers in WB.

Code Select Expand

ObjectClrOption("useany", "System")
ObjectClrOption("useany", "System.Data")
user = "dbadmin"
pw = "password"
db = 'sakila'
connstring = "Driver={MySQL ODBC 9.0 ANSI Driver};Server=localhost;Database=%db%;User=%user%;Password=%pw%;Option=3;"
query = "SELECT * FROM sakila.actor;"
conn = ObjectClrNew('System.Data.Odbc.OdbcConnection')
Message("",conn :@LF : connstring)   ;just to check no errors yet
conn.ConnectionString = connstring
conn.open()  ;error here with "Unknown Name"
cmd = ObjectClrNew('System.Data.Odbc.OdbcCommand',query,conn)
dt = ObjectClrNew('System.Data.DataTable')
adapter = ObjectClrNew('System.Data.Odbc.OdbcDataAdapter',cmd)
adapter.fill(dt)
conn.close()
;dt [convert to array]


[JTaylor]

Sorry.  Not sure I have a good way to give 64-bit a try.  Hesitant about mixing versions for stuff I have already working.   I have done this with 32-bit and ADO and it works fine.   Had to step back a a few versions on the ODBC Driver for the 32-bit.

Jim

[spl]

Sorry.  Not sure I have a good way to give 64-bit a try.  Hesitant about mixing versions for stuff I have already working.   I have done this with 32-bit and ADO and it works fine.   Had to step back a a few versions on the ODBC Driver for the 32-bit.

Jim

https://dev.mysql.com/blog-archive/32-bit-odbc-driver-8-0-for-windows-is-back/

I can give these a try. Have several older mysql odbc 5.2 scripts written circa 2009-2014. Of course, ADO will not work with 64bit but thought .net odbc would be a path.

[JTaylor]

Yes.  I am using that.  They brought it up to 8.0 but I couldn't find 32-bit for the most recent versions. Sorry, I realize that my comment was a bit ambiguous.

Jim

[spl]

Yes.  I am using that.  They brought it up to 8.0 but I couldn't find 32-bit for the most recent versions. Sorry, I realize that my comment was a bit ambiguous.

Jim

No Problem. I got ADOBB to work with both 32/64 in PS [depending upon which ISE or Command Line is called]. Of course, how to really deal with Recordset, i.e. create .csv/ create Excel tab / save as XML or Json is entirely left up to the final lines. Now, how to figure it out for straight .NET w/out ODBC??

This is PS code, but easily converted [except for maybe establishing 32/64] into WB

Code Select Expand

Switch([IntPtr]::size * 8) {
   32 { $driver = "8.0"}
   64 { $driver = "9.0" }
}
$user = "dbadmin"
$pw = "password"
$db = 'sakila'
$provider ="Driver={MySQL ODBC $driver ANSI Driver};Server=localhost;Database=$db;User=$user;Password=$pw;Option=3;"
$qry = "SELECT * FROM sakila.actor;"
$adoConnection = new-object -comObject ADODB.connection
$adoConnection.connectionString = $provider
$adoConnection.open()
$recordSet = new-Object -COMObject ADODB.Recordset
$recordSet.Open($qry,$adoConnection,3,1)
$n = $recordSet.RecordCount
$n
$gs = $recordSet.GetString(2,$n,"^","`r`n",$null)
$gs
$recordSet.close()
$adoConnection.close()
$recordset = $null
$adoConnection = $null

or in WB

Code Select Expand

user = "dbadmin"
pw = "password"
db = 'sakila'
conn = 0
conn = CreateObject("ADODB.Connection")
connstring = "Driver={MySQL ODBC 8.0 ANSI Driver};Server=localhost;Database=%db%;User=%user%;Password=%pw%;Option=3;"
conn.Open(connstring)
query = "SELECT * FROM sakila.actor;"
rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3   ; adUseClient
rs.Open(query,conn,1,4)
n= rs.RecordCount
gs = rs.GetString(2,n,"^",@LF,)
Message("Query Results",gs)
rs.Close()
conn.Close()
rs=0
conn=0
Exit