[Closed] Connecting to a MySQL database, why doesn't it work?

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.

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.

That’s correct. It requires dotNet.

However… the old method usually works on Max 8 so I’m assuming you’ve upgraded.

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

we have a big project that will soon require extensive database integration so thanks heaps for sharing this. It will be very handy!

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

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!

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

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 "%=%
" 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

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.

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:

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!

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:

This is some neat stuff. So what are you planning to do with Databases from Max?