[Solved] SQLException when connecting to a MySQL server i...

[volnrok]

My database macro is having serious issues. It is supposed to take data from a text file, parses it, then stores it in a MySQL database for later editing. However, whenever I try to run this macro I get an error.

Basically, the code finishes this way:

Code: Select all

Dim sProgram, sField, sType, sClass
Dim bPublic, bForeign, bCtor

sProgram = oTxtProgram.getText()
For i = 1 to j
	sField = sData(i, 2)
	sType = sData(i, 3)
	sClass = sData(i, 4)
	bPublic = False
	bForeign = False
	bCtor = False
	
	If LCase(sData(i, 5)) = "true" Then
		bPublic = True
	End If
	If LCase(sData(i, 6)) = "true" Then
		bForeign = True
	End If
	If LCase(sData(i, 7)) = "true" Then
		bCtor = True
	End If
	
	sStatement = "INSERT INTO ""Field"" (""Program"",""FieldName"",""Type"",""Class"",""PublicAccessor"",""ForeignAssignment"",""ConstructorAssignment"",""Soln_Immutability"",""Soln_UniqueRef"",""Soln_RefactorMess"",""Soln_Aspect"",""DeadGetter"",""DeadSetter"",""Inconclusive"",""Boring"") VALUES ('" + sProgram + "','" + sField + "','" + sType + "','" + sClass + "','" + bPublic + "','" + bForeign + "','" + bCtor + "','false','false','false','false','false','false','false','false');"
	msgbox(sStatement)
	oStatement.execute(sStatement)
Next i

The Messagebox in there is for debugging purposes. It spits out this:
------------------------
INSERT INTO "Field" ("Program","FieldName","Type","Class","PublicAccessor","ForeignAssignment","ConstructorAssignment","Soln_Immutability","Soln_UniqueRef","Soln_RefactorMess","Soln_Aspect","DeadGetter","DeadSetter","Inconlusive","Boring") VALUES ('argouml','org.argouml.cognitive.CompositeCM','java.util.List','mechs','False','False','False','false','false','false','false','false','false','false','false','false','false');
------------------------

I then get an error message, saying something about my syntax:
------------------------
BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException
Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Field" ("Program","FieldName","Type","Class","PublicAccessor","ForeignAssignmen' at line 1.
------------------------

Error message notes:
----There are three quotation marks at '"Field", unsure if it's single-double, double-single, or single-single-single (but I think it's single-double)
----"ForeignAssignmen' ends with a single quotation mark

Other notes:
----My macro is running on a database connected to a server at a different location. The code works when I run it on a different database file, not connected to a MySQL server. This makes me think the problem is a server compatibility issue.
----The database is running on a Ubuntu machine, and I on Windows XP, but this shouldn't cause any issues.
----I can edit the database by hand, it's the SQL statement that is exploding.
----The server is running MySQL, but I am unsure of the version. This may be where the problem lies.
----I do have a programming background just not in this OOBasic/StarBasic/Whatever it's called.

If anyone has any relevant ideas or suggestions, it would be greatly appreciated! I am quite confused about this error

[volnrok]

If it helps anyone, the MySQL version is:
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (powerpc) using readline 6.1

[volnrok]

So it turns out that MySQL does not like quotation marks, and automatically switches all booleans to bits.

After fixing it up and running it, I got ~200 message boxes, all with different reports. And the database is all full of these reports.

My revised code is as follows:

Code: Select all

Dim sProgram, sField, sType, sClass
Dim bPublic, bForeign, bCtor

sProgram = oTxtProgram.getText()
For i = 1 to j
	sField = sData(i, 2)
	sType = sData(i, 3)
	sClass = sData(i, 4)
	iPublic = 0
	iForeign = 0
	iCtor = 0
	
	If LCase(sData(i, 5)) = "true" Then
		iPublic = 1
	End If
	If LCase(sData(i, 6)) = "true" Then
		iForeign = 1
	End If
	If LCase(sData(i, 7)) = "true" Then
		iCtor = 1
	End If
	
	sStatement = "INSERT INTO test.Field (Program,FieldName,Type,Class,PublicAccessor,ForeignAssignment,ConstructorAssignment,Soln_Immutability," + "Soln_UniqueRef,Soln_RefactorMess,Soln_Aspect,DeadGetter,DeadSetter,Inconclusive,Boring) VALUES ('" + sProgram + "','" + sField + "','" + sType + "','" + sClass + "'," + iPublic + "," + iForeign + "," + iCtor + ",0,0,0,0,0,0,0,0);"
	'msgbox(sStatement) 'this line commented out because I don't need debug output anymore
	oStatement.execute(sStatement)
Next i

The problem is finally solved! Yay!