PDA

View Full Version : Connecting to a MySQL database, why doesn't it work?


loocas
05-13-2008, 02:17 PM
Hi there,

I've been trying to connect to a MySQL database that I've setup on my localhost. I can connect to it through Python, even through Python in Maya, the only problem is Max and Maxscript. I have no idea why it doesn't work. I've spent hours on internet trying to figure it out, but this given example:

DogConn=createOLEObject "ADODB.Connection"
DogConn.Open "driver={SQL Server}; server=localhost; database=test"

in the help file doesn't work and outputs after a few seconds:

<OLEObject:ADODB.Connection>
-- Runtime error: OLEObject method call failed:
Error Message 1: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
method: open()

I even tried a provider syntax:
Provider=OleMySql.MySqlSource.1;

however, it outputs:

-- Runtime error: OLEObject method call failed:
Error Message 1: Provider cannot be found. It may not be properly installed.
method: open()

I'm slowly getting desperate. I'm trying to integrate Max into our pipe via MySQL db, but this seriously is an annoying issue.

The db works, I can connect to it through other systems, so there's not a problem there, it must be Max or the syntax.

Does anybody have any idea? Thanks in advance, cheers,

- loocas

thatoneguy
05-13-2008, 07:54 PM
I recommend not using the ODBC connection. It sucks monkey balls and I can't connect to any of my databases through it any more.

First you'll want to nistll the MySQL .net Driver
http://dev.mysql.com/downloads/connector/net/5.2.html

and install it. It's really small and easily deployable. (MUCH EASIER than the ODBC driver madness.)

Then you use .net controls instead of Max controls. But since that's annoying I created a couple of basic functions:

global SQL
struct SQL (
function Connect host:"localhost" database:"DBName" user:"root" password:"" driver:"T:\\Software\\Pipeline\\MySQLDriver\\MySql.Data.dll"=
(
-- fn existFile fname = (getfiles fname).count != 0

-- if (existFile driver) then
-- (
-- )
-- else
-- (
-- driver = "C:\\Program Files (x86)\\MySQL\\MySQL Connector Net 5.1.5\\Binaries\\.NET 2.0\\MySql.Data.dll"
-- )

dotNet.loadAssembly driver
connectionString = ("Database=" + database + ";Data Source=" + host + ";User Id=" + user + ";Password=" + password)
DBConnection = dotNetObject "MySql.Data.MySqlClient.MySqlConnection"
DBConnection.ConnectionString = connectionString
DBConnection.open()
return DBConnection
),
function Query input SQLRequest =
(
cmdObject = input.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.ExecuteReader()

SQLDataSet = #()
while readerObject.read() do
(
record =ReaderObject.item
SQLRow = #()
for j = 0 to (ReaderObject.FieldCount - 1) do
(
append SQLRow record[j]
)
Append SQLDataSet SQLRow
)
readerobject.close()
return SQLDataSet
),
function Execute DBConnection SQLRequest =
(
cmdObject = DBConnection.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.ExecuteReader()
readerObject.close()
)
)


First you open a connection:

Connectionvar = SQL.Connect host:"192.168.32.4" database:"DBName" user:"Root" password:"password"
SQL.Execute ConnectionVar "INSERT...etc"
TableArray = SQL.Query "SELECT Bob FROM...etc"


---

Now one comment on the code. Our system has it setup so that the driver is actually residing on a shared folder (which you'll need to point the default to in your implementation or else define its location on every execution.) I think you still have to locally install the driver though on every computer that uses the code. You can experiment with that. But be aware that you need to specify a shared location for a copy of the driver and install it locally.

thatoneguy
05-13-2008, 07:55 PM
When I edit posts is breaks them....

I had a small error in my example code. The query needs to first specify a DBConnection object and then the query string just like the execute code.

loocas
05-13-2008, 09:32 PM
I recommend not using the ODBC connection. It sucks monkey balls and I can't connect to any of my databases through it any more.

First you'll want to nistll the MySQL .net Driver
http://dev.mysql.com/downloads/connector/net/5.2.html

and install it. It's really small and easily deployable. (MUCH EASIER than the ODBC driver madness.)

Then you use .net controls instead of Max controls. But since that's annoying I created a couple of basic functions:

global SQL
struct SQL (
function Connect host:"localhost" database:"DBName" user:"root" password:"" driver:"T:\\Software\\Pipeline\\MySQLDriver\\MySql.Data.dll"=
(
-- fn existFile fname = (getfiles fname).count != 0

-- if (existFile driver) then
-- (
-- )
-- else
-- (
-- driver = "C:\\Program Files (x86)\\MySQL\\MySQL Connector Net 5.1.5\\Binaries\\.NET 2.0\\MySql.Data.dll"
-- )

dotNet.loadAssembly driver
connectionString = ("Database=" + database + ";Data Source=" + host + ";User Id=" + user + ";Password=" + password)
DBConnection = dotNetObject "MySql.Data.MySqlClient.MySqlConnection"
DBConnection.ConnectionString = connectionString
DBConnection.open()
return DBConnection
),
function Query input SQLRequest =
(
cmdObject = input.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.ExecuteReader()

SQLDataSet = #()
while readerObject.read() do
(
record =ReaderObject.item
SQLRow = #()
for j = 0 to (ReaderObject.FieldCount - 1) do
(
append SQLRow record[j]
)
Append SQLDataSet SQLRow
)
readerobject.close()
return SQLDataSet
),
function Execute DBConnection SQLRequest =
(
cmdObject = DBConnection.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.ExecuteReader()
readerObject.close()
)
)


First you open a connection:

Connectionvar = SQL.Connect host:"192.168.32.4" database:"DBName" user:"Root" password:"password"
SQL.Execute ConnectionVar "INSERT...etc"
TableArray = SQL.Query "SELECT Bob FROM...etc"


---

Now one comment on the code. Our system has it setup so that the driver is actually residing on a shared folder (which you'll need to point the default to in your implementation or else define its location on every execution.) I think you still have to locally install the driver though on every computer that uses the code. You can experiment with that. But be aware that you need to specify a shared location for a copy of the driver and install it locally.

Tahnk you very much for your insight, thatoneguy, I'll give it a shot at our studio tomorrow as I don't have a suitable environment to test it on at home. I assume this is Max 9+ workflow as Max 8 doesn't have access to dotNET objects, am I right?

Thanks again, very much appertiated! :buttrock:

thatoneguy
05-14-2008, 12:20 AM
That's correct. It requires dotNet.

However... the old method usually works on Max 8 so I'm assuming you've upgraded. :D

btw. A really great home SQL environment is "EasyPHP". Super easy to install and equally important uninstall.

Gravey
05-14-2008, 11:34 AM
we have a big project that will soon require extensive database integration so thanks heaps for sharing this. It will be very handy!

loocas
05-14-2008, 01:52 PM
That's correct. It requires dotNet.

However... the old method usually works on Max 8 so I'm assuming you've upgraded. :D

btw. A really great home SQL environment is "EasyPHP". Super easy to install and equally important uninstall.

Actually not :) We haven't updated during the project we're working on as that's usually not a good idea, besides I have a lot of proprietary tools written using ActiveX, so re-writing them for dotNET would be a pain :D

Anyways, I have a Max 2008 installed on my machine, but I don't want to mix up scenes with different versions, so I'll only use the 2008 for generating the data and writing them to the db as Max is only acting as a host for this pipe to work, I don't need it later. I source the db data in Maya and XSI later and both support Python, so no problem there.

The Max 8 methods didn't work for me, unfortunately and I have no idea what's going on :(

Thanks again, mate! :thumbsup:

ofer_z
05-15-2008, 10:34 AM
Hi,

I also had issues trying to connect to MySQL with max 8 using ODBC. The main problem is not connecting, but the incomplete values conversion of maxscript's safeArrayWrappers, which caused many types of data return from MySQL to cause errors in maxscript.
The solution I found was using SciBit's MyX activeX plugin (http://www.scibit.com/products/mysqlx/). It's a commercial product, but not too expensive, and gave me the best results (I tested a couple). We finally abandoned the idea of using it, because be were to move to max 9/2008 which has dotNet support, and that makes life a lot easier, but still, if you need to access MySQL with max 8, I would recommend that activeX COM Object.

Cheers,
o

loocas
05-15-2008, 11:17 AM
Hi,

I also had issues trying to connect to MySQL with max 8 using ODBC. The main problem is not connecting, but the incomplete values conversion of maxscript's safeArrayWrappers, which caused many types of data return from MySQL to cause errors in maxscript.
The solution I found was using SciBit's MyX activeX plugin (http://www.scibit.com/products/mysqlx/). It's a commercial product, but not too expensive, and gave me the best results (I tested a couple). We finally abandoned the idea of using it, because be were to move to max 9/2008 which has dotNet support, and that makes life a lot easier, but still, if you need to access MySQL with max 8, I would recommend that activeX COM Object.

Cheers,
o

Thanks a lot! :thumbsup: Will give it a go.

loocas
06-17-2008, 04:53 PM
I finally got to working on this mini-project of mine (the database stuff) and I downloaded the MyX COM obj, but I have problems connecting to the database through the object.

When I invoke the .connect method, it returns:


-- Runtime error: OLEObject method call failed:
Error Message 1: The MyX script object [Copyright (c) 2004, SciBit, http://www.scibit.com] used on this website has an invalid release code: Code Invalid. Please contact the website owner.
Error Message 2: Member not found.
method: Connect()


Do you have any idea what may be the problem? I tried various modifications of the parameters, but nothing seems to work.

Hi,

I also had issues trying to connect to MySQL with max 8 using ODBC. The main problem is not connecting, but the incomplete values conversion of maxscript's safeArrayWrappers, which caused many types of data return from MySQL to cause errors in maxscript.
The solution I found was using SciBit's MyX activeX plugin (http://www.scibit.com/products/mysqlx/). It's a commercial product, but not too expensive, and gave me the best results (I tested a couple). We finally abandoned the idea of using it, because be were to move to max 9/2008 which has dotNet support, and that makes life a lot easier, but still, if you need to access MySQL with max 8, I would recommend that activeX COM Object.

Cheers,
o

ofer_z
06-19-2008, 11:01 PM
Hi,

I don't know what might be the problem. It's been a while since I last used it, and I don't have it setup anymore, so I can't test. However, my former employers at snowball|VFX (www.snowballvfx.com (http://www.snowballvfx.com)) were kind enough to let me post the structs I've developed for connecting to MySQL using MyX OLE object, and it is provided below. Please note that this code is provided as-is, so use at your own risk. Usage example is at the end:


--**********************************************************************************************
--* snowball|VFX
--*
--* Database Connection Struct
--* Version 0.5
--* Last update: 5:11 PM Sunday, November 26, 2006
--*
--*
--*
--* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
--* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
--* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
--* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
--* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
--* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
--* SUBSTITUE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
--* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
--* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
--* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
--* POSSIBILITY OF SUCH DAMAGE.
--**********************************************************************************************

--*********************************************************************************
--* s_Pipeline_DBQuery is a struct to handle a DB query and the result set returned
--* from that query.
--* Use the useRowStruct property to define whether t o return the results as
--* a struct or array. Setting the property to true (the default) will return
--* a row as a struct where each field name is a property of the returned row.
--*********************************************************************************

global s_Pipeline_DBQuery
struct s_Pipeline_DBQuery (
dataSet, -- The MyX OLE dataset.
DBObject = undefined, -- A reference to the genetral DB object to get the time date format function.
TimeDateFormat = "YYYY/MM/DD HH:MM:SS", -- The format in which time and date field will be returned.
isEmpty = undefined, -- Property: Is the query result set empty
AffectedRows = undefined, -- Property: The number of affected rows in the last query (when updating).
FieldCount = undefined, -- Property: The number of fields in the result set.
LastInsertID = undefined, -- Property: The last query's insert ID (in case the SQL query was an insert operation).
fieldNames = undefined, -- Property: An array of the result's field names.
rowStruct = undefined, -- Property: Used internally to store the row struct.
useRowStruct = true, -- Property: Whether to get the rows in the results as struct or not (as array).



-- Returns an array of the field names in the result set.
fn getFieldNames =
(
for i = 0 to (FieldCount - 1) collect (dataSet.Fields i).name
),


-- Returns a field's index using the field's name.
-- This is useful when getting the results as arrays to find out
-- the index number of a specific field using it's name.
fn getFieldIndex f =
(
if isKindOf fieldNames Array then (
f = (f as string) as name
findItem (for i in fieldNames collect i as name) f
) else
0
),


-- Creates a struct based on the result's field names for use in
-- the fetchRow method.
-- This method generates a string that declares a global struct, exceutes it
-- and stores the struct in the local rowStruct property.
fn createRowStruct =
(
-- Declare the global struct.
global s_DBTempResultRowStruct

-- Check to make sure there are field names, so we don't create the stcut for nothing.
if isKindOf fieldNames Array and fieldNames.count > 0 then (
-- Create an empty string and format the struct
-- decleration command into it.
local s = stringStream ""
format "struct s_DBTempResultRowStruct (" to:s
for i = 1 to FieldCount do (
local f = fieldNames[i]
format "%=undefined" f to:s

if i < FieldCount then
format "," to:s
)
format ")" to:s

-- Execute the string to create the struct.
execute (s as string)

-- Set the local property rowStruct with the global struct definition.
rowStruct = s_DBTempResultRowStruct
-- Clear the global struct.
s_DBTempResultRowStruct = undefined
)
),


-- This method retrives a field value and parses it for compatibility with maxscript.
-- The MyX activeX control uses these field types:
-- 1 ftString
-- 2 ftSmallInt
-- 3 ftInteger
-- 4 ftWord
-- 6 ftFloat
-- 9 ftDate
-- 10 ftTime
-- 11 ftDateTime
-- 14 ftAutoInc
-- 15 ftBlob
-- 16 ftMemo
-- 25 ftLargeInt
-- 32 ftVariant (NULL)
fn parseFieldValue val =
(
if val.isNull then
undefined
else if val.type == 1 or val.type == 15 or val.type == 16 then
val.asString
else if val.type == 9 or val.type == 10 or val.type == 11 then
if DBObject != undefined then DBObject.FormatDateTime TimeDateFormat val.asString else val.asString
else
val.value
),


-- This method retrives the next row in the result set.
-- The row is retrived as a struct by default, unless no struct was
-- defined when the query was performed (using the useRowStruct property).
-- The row can be forced to be retrived as an array by passing
-- the 'asArray' parameter as true.
-- If the end of the result set was reached and there are no more rows to
-- fetch the method will return undefined.
fn fetchRow asArray:false =
(
-- Check if the end of the result set was reached.
if not dataSet.EOF then (
-- The end of the result set was not reached, so go on to fetch the row.

if asArray != true and rowStruct != undefined then (
-- asArray is not true and rowStruct is defined so
-- parse the row as a struct.

-- Set the returned row as an instance of the rowStruct.
local row = rowStruct()
-- For each field in fieldNames set the property with the same name
-- in the struct with the value of that field.
for f in fieldNames do (
local val = parseFieldValue (dataSet.FieldByName f)
setProperty row (f as name) val
)
) else (
-- Either asArray is set to true or rowStruct is undefined,
-- so parse the row and return it as an array.

-- For each field in fieldNames collect the field's value into row.
local row = for f in fieldNames collect (
parseFieldValue (dataSet.FieldByName f)
)
)
-- Move the row pointer to the next row.
dataSet.MoveNext()
-- Return the row
row
) else
-- The end of the result set was reached, so return undefined.
undefined
),


-- Return the amount of records in the result set.
fn recordCount =
(
dataSet.recordCount
),


-- Returns true if the row pointer is past the end of the result set.
fn EOF =
(
dataSet.EOF
),


-- Move the row pointer to the first row in the result set.
fn moveFirst =
(
dataSet.moveFirst()
),


-- Move the row pointer to the next row in the result set.
fn moveNext =
(
dataSet.moveNext()
),


-- Move the row pointer to the previous row in the result set.
fn movePrevious =
(
dataSet.movePrevious()
),


-- Move the row pointer to the Last row in the result set.
fn moveLast =
(
dataSet.moveLast()
),


-- Returns the state of the dataSet.
fn state =
(
dataSet.state
),


-- Initialize the internal properies.
fn init =
(
dataSet.readOnly = true
isEmpty = dataSet.isEmpty
AffectedRows = dataSet.AffectedRows
FieldCount = dataSet.FieldCount
LastInsertID = dataSet.LastInsertID
fieldNames = getFieldNames()
if useRowStruct == true then
createRowStruct()
),


-- Reset the internal properties, close and release the recordSet object.
fn close =
(
try(recordSet.close)catch()
-- try(releaseOLEObject recordSet)catch()
isEmpty = undefined
AffectedRows = undefined
FieldCount = undefined
LastInsertID = undefined
fieldNames = undefined
rowStruct = undefined
)

) -- end of s_Pipeline_DBQuery struct




--*********************************************************************************
--* s_Pipeline_DBConnection is a wrapper struct to handle a database connection.
--* Once a connection is made, use the query method to query the DB. The result
--* of the query method is a s_Pipeline_DBQuery which can be used to get the
--* results as an array or a struct.
--*********************************************************************************
global s_Pipeline_DBConnection
struct s_Pipeline_DBConnection (
host = "MySQLServer", -- The DB server name.
database = "DBName", -- The database name to use.
username = "user", -- The DB username for the connection.
password = "password", -- The DB password for the connection.
port = "3306", -- The DB port for the connection.


SciBit_ReleaseCode = "EnterYouCodeHere", -- SciBit MyX Relese Code (valid until: 2006/11/29)
SciBit_SerialNumber = "Trial", -- SciBit MyX Serial Number



DBObject = createOLEObject "SciBit.MySQLX", -- The OLE DB connection object
QueryObject = undefined,


-- Connect to the DB.
-- If force is true, the DBObject will first be reset
-- and only then connected again.
fn Connect force:true =
(
if force then (
try(releaseOLEObject DBObject)catch()
DBObject = undefined
)
if not isKindOf DBObject OLEObject then
DBObject = createOLEObject "SciBit.MySQLX"
DBObject.register SciBit_ReleaseCode SciBit_SerialNumber
DBObject.connect host username password port database
DBObject.FormatDateTime TimeDateFormat
DBObject.server.connected
),


-- Disconnect from the DB.
fn Disconnect =
(
try(QueryObject.close())catch()
try(releaseOLEObject QueryObject)catch()
try(DBObject.close())catch()
try(releaseOLEObject DBObject)catch()
),


-- Returns the state of the DBObject connection.
fn state =
(
DBObject.server.connected
),


-- Send a SQL query to the DB.
-- The function returns the results as s_Pipeline_DBQuery struct.
fn Query SQL useRowStruct:true =
(
-- First, make sure the SQL query is a string.
if not isKindOf SQL string then
SQL = SQL as string
-- Create a s_Pipeline_DBQuery using a DBObject query result as the dataset.
if QueryObject == undefined then (
QueryObject = DBObject.dataSetFrom SQL
) else (
QueryObject.close()
QueryObject.SQL.Text = SQL
QueryObject.open()
)
local q = s_Pipeline_DBQuery QueryObject DBObject:DBObject
q.useRowStruct = (useRowStruct == true)
q.init()
-- return the s_Pipeline_DBQuery variable with the SQL results.
q
)

) -- end of s_Pipeline_DBConnection struct



-- ********* Usage example:
-- connect and query the db:
db = s_Pipeline_DBConnection()
db.connect()
print (db.state())

-- Query the DB
r = db.query "SELECT * FROM testtable"

print (r.recordCount())
-- Loop through the results and print them.
-- NOTE: the rowStruct is used here, this means each row is
-- returned as a struct with the field names as the properties.
-- This allows to write more readable code, at the expense of
-- slight performance hit.
-- There is also a way to get the results as simple array (see example below)
while (row=r.fetchRow()) != undefined do
for p in (getPropNames row) do
format "%=%\n" p (getProperty row p)

-- To get the results as arrays:
while (row=r.fetchRow asArray:true) != undefined do
for i = 1 to row.count do
print p[i]


-- Release the result set:
r.close()

-- Close the DB connection
db.close()




Cheers,
o

loocas
06-20-2008, 09:19 AM
Hi,

I don't know what might be the problem. It's been a while since I last used it, and I don't have it setup anymore, so I can't test. However, my former employers at snowball|VFX (www.snowballvfx.com (http://www.snowballvfx.com)) were kind enough to let me post the structs I've developed for connecting to MySQL using MyX OLE object, and it is provided below. Please note that this code is provided as-is, so use at your own risk. Usage example is at the end:

Thank you very much, I'll dive into it as soon as I find some spare time and post my results.

Thanks again, much appretiated! :thumbsup:

loocas
08-09-2008, 09:44 PM
Finally, all of a sudden, I found a way of connecting to a DB through ODBC connectors.

All you really need (in my case at least) for connecting to a MySQL database is an ODBC connector provided by SUN (http://dev.mysql.com/downloads/connector/odbc/5.1.html).

After installing it, make sure the ODBC connector runs by looking into Administrative Tools/Data Sources (ODBC) in your Control Panel.

Then, take this connection string from the great, great!, site www.connectionStrings.com (http://www.connectionstrings.com/):

Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;

and modify it:

Driver={MySQL ODBC 5.1 Driver};Server=data.domain.com;Database=myDataBase;User=myUsername; Password=myPassword

All of a sudden, the code example in the documentation simply works as advertised and returns the correct stuff from the db! :beer:

It's still wiser and easier to work with dotNET objects/connectors, however, since Max8 simply doesn't integrate dotNET, I'm forced to use the ODBC, now when it works! YEEHAA! :buttrock:

labbejason
08-09-2008, 10:25 PM
This is some neat stuff. So what are you planning to do with Databases from Max?

ofer_z
08-09-2008, 11:45 PM
Hi loocas,

If this works for you, great!
When I tried it, I had no problem connection to MySQL through the ODBC connector. The issue was that some data type didn't convert to mxs values, and crashed the script. That's why I went with myX solution.
I don't remember all the types that worked and the ones that didn't work, but time-date types and blobs gave me hell :)

Cheers,
o

thatoneguy
08-09-2008, 11:54 PM
The downside being that the .NET translation is about 10 times slower than the ODBC connection.

The ODBC connection can get 2,000 records in a fraction of a second.

Converting a .NET dataarray to a maxarray takes about 1-1.5 seconds.

If you convert it to a struct then it takes even longer ~1.25.175 seconds.

I have gotten to writing all of my SQL requests by hand for .net to be as lean as possible while with ODBC I would just grab all of the data and let max deal with it later.

Some things are faster with .Net for instance if you use a ExecuteScalar instead of ExecuteReader on the command object it returns a single value which is lightning fast.

loocas
08-10-2008, 01:00 AM
This is some neat stuff. So what are you planning to do with Databases from Max?

I'm working on an asset management/distribution system for our studio, which will be based around Python, MAXScript and open source software like MySQL etc... ;)

Btw: thanks for the valuable tips, thatoneguy! :beer:

CGTalk Moderation
08-10-2008, 01:00 AM
This thread has been automatically closed as it remained inactive for 12 months. If you wish to continue the discussion, please create a new thread in the appropriate forum.