[Closed] MySQL .NET connector tutorial

Thanks a lot! I’ll put this to use on monday for sure.

Hey Ofer,

i’ve got a question to take your tutorial a step further. You’ve demonstrated how to read from a table by fieldnames. I’d like to add new rows to a table by fieldnames. I’ve put up this question about two weeks ago:
http://forums.cgsociety.org/showthread.php?f=98&t=601548

I can create a new row, but the dotNetObject System.Data.DataRow doesn’t seem to have an explicit update command. When i read the .NET documentation it seems very simple. They just say: newRow(“fieldname”) = “someValue”. But in maxscript i can’t get this to work. Somehow it appears to me as a very simple syntax thing, but i don’t get it. thatoneguy suggested using OLE-objects, but i’d like to stay clear of them untill i’ve got no more options left. Any ideas?

Klaas

Hi Klaas,

I find the ADO.NET to be quite complex, and since most of my SQL knowledge comes from using it with PHP, I prefer to just use SQL commands instead of using all the other classes that the connector provides.
If performance is important to you (you perform thousands or millions of SQL operations), you might get better results using all the classes that the connector provides, as they are probably optimized for the different tasks. However, in my tests, my method of using just plain SQL commands to do those tasks was fast enough (at least for my needs).

Here’s an updated version of the tutorial that shows how to add and modify rows:

(
	-- This is a small tutorial on how to use the MySQL Connector to query a MySQL database.
	-- This tutorial assumes you have a MySQL server already set-up, that you installed the 
	-- MySQL Connector (available at MySQL website) and that you have basic knowledge of SQL.
	
	-- I will create a wrapper for easier usage in the near future.
	-- Enjoy. 
	-- Ofer Zelichover (www.oferz.com) 2008-03-08

	
	-- First load the dotNet assembly. Make sure the string points to the place where you installed
	-- the MySQL Connector DLL file.
	dotNet.loadAssembly "C:\\Program Files\\MySQL\\mysql-connector\\bin\\MySql.Data.dll"
	
	-- Now define the connection parameters.
	local host = "localhost" 	-- The name of the MySQL server
	local database = "test" 	-- The name of the database to use
	local user = "test" 		-- The user name to use
	local password = "test" 	-- The password to use
	
	-- And build the connection string
	local connectionString = "Database=" + database + ";Data Source=" + host + ";User Id=" + user + ";Password=" + password
	
	-- Create a connection object
	local DBConnection = dotNetObject "MySql.Data.MySqlClient.MySqlConnection"
	-- Set the connection parameters using the connection string.
	DBConnection.ConnectionString = connectionString
	-- Open the DB connection
	DBConnection.open()
	
	-- Print the connection status: 1 - connected; 0 - not connected.
	print DBConnection.state.value__
	
	
	-- Add a 2 new rows
	local cmdObject = DBConnection.CreateCommand()
	cmdObject.commandText = "INSERT INTO test_table (`name`, `phone`) VALUES ('another name','1234567890'), ('yet another name','2468101214')"
	local readerObject = cmdObject.ExecuteReader()
	-- Print the number of affected rows
	format "Added % new rows
" readerObject.RecordsAffected
	readerObject.close()

	-- Change the value of the the second row in the table:
	local cmdObject = DBConnection.CreateCommand()
	cmdObject.commandText = "UPDATE test_table tt SET tt.name='modified name', tt.phone=78978979 WHERE tt.id=2"
	local readerObject = cmdObject.ExecuteReader()
	-- Print the number of affected rows
	format "Modified % row(s)
" readerObject.RecordsAffected
	readerObject.close()

	
	-- Query the database:
	-- First create a command object. This 
	local cmdObject = DBConnection.CreateCommand()
	-- Define the SQL query you wish to execute:
	cmdObject.commandText = "SELECT * FROM test_table tt"
	-- Then execute the command to create a reader object.
	local readerObject = cmdObject.ExecuteReader()
	
	-- Print the field names from the result:
	for i = 0 to (readerObject.FieldCount - 1) do
		print (readerObject.getName i)
		
	-- Print the results:
	-- The readerObject.read() method advances to the next record in the reader object.
	while readerObject.read() do (
		local record = readerObject.item
		-- Now print the record values. This assumes you know the field names:
		format "%	%	%
" record["id"] record["name"] record["phone"]
		
		-- Another option, if you don't want to use explicit field names:
		for i = 0 to (readerObject.FieldCount - 1) do
			format "%	" record[i]
		format "
"
	)

	
	
	-- Close the reader object
	readerObject.close()
	
	-- Close the connection
	DBConnection.close()
)

cheers,
o

Thanks Ofer,

I’ll try your suggestion out.
My brother who has the actual knowledge about databases, instead of me, suggested that SQL commands can get quite messy. User input can mess up these queries. But if it’s the only way, i’ll do it!

Klaas

I’m only getting one result via the while readerObject.read() […] method.

Any ideas? It’s returning the first result just fine. But just stopping there.

Edit: I had two entries. I deleted the one it was returning. And now I get no results even with a “Select * from table” command all by iteslf with no where.

Edit2: Gets weirder. If I run the SQL command straight on the server it returns results. Also I can dump the contents of another table in the database… So it’s just this one table just through Max… odd.

Edit3: That table has an Autoincrement Primary Key. For some reason the MySQL Connect wasn’t returning any results because I had no entry with a primary key GUID of ‘1’. I created a row and forced it to have a ‘1’ in the tableGUID column and sure enough… it magically found all the entries. Looks like a bug in MySQL connect.

A little function for converting a result into a max array for more max centric parsing etc.

function SQLReturnArray SQLObj = 
  (
   local SQLDataSet = #()
   while SQLObj.read() do 
   (
	record = readerObject.item
	SQLRow = #()
	for j = 0 to (readerObject.FieldCount - 1) do
	(
	 append SQLRow record[j]
	)
	Append SQLDataSet SQLRow
   )
   return SQLDataSet
  )

Also a correction to your example:

cmdObject.commandText = "INSERT INTO test_table (`name`, `phone`) [...]

needs to read:

cmdObject.commandText = "INSERT INTO test_table (name, phone) [...]

(No ’ ’ around column names).

I’m having a bit of trouble with executing a “literal” SQL script:

cmdObject.commandText = (@“INSERT INTO cvs (JobNum,FileStatus,FileUser,FileName) VALUES (’”+JobNum+”’,‘1’,’”+sysinfo.username+”’,’”+(maxfilepath+maxfilename)+”’)”)

Everything works perfectly except it’s stripping all of the back slashes from the file name even with the @ in there. I’ll get: C:folderNextFolderSubFolderNewfolderFile.max

Edit: Whoooooops the malfunction was in the flight stick actuator. I had it set to text not varchar.

Ok so SQL wants explicit “\” defined sooo…

 
function DoubleSlashString inputstring =
(
 doubledstring = ""
 inputstring = filterstring inputstring "\\"
 for i = 1 to inputstring.count do
 (
  append doubledstring inputstring[i]
  if i != inputstring.count do append doubledstring "[\\\\]( http://forums.cgsociety.org/ )"
 )
 return doubledstring
)

Ok I enhanced the SQLReturnArray function. I don’t know why I didn’t include all of the relevant code into one function. This should be more useful and practical.

Edit2 (Made it a struct which can be called by like “SQL.Connect()”):

  struct SQL (
   function Connect host:"localhost" database:"database" user:"root" password:"" driver:"C:\\Program Files\\MySQL\\MySQL Connector Net 5.2.1\\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 DBConnection SQLRequest = 
   (
	cmdObject = DBConnection.CreateCommand()
	cmdObject.commandText = SQLRequest
	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
	readerObject = cmdObject.ExecuteReader()
	readerObject.close()
   )
  )