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.
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.
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]
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
Quote from: JTaylor on September 23, 2024, 08:58:21 AMSorry. 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.
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
Quote from: JTaylor on September 23, 2024, 10:24:04 AMYes. 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
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
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