SQL Driver - Revision history

Skrach: /* Known Issues */

2013-06-01T00:41:10Z

Known Issues

← Older revision		Revision as of 00:41, 1 June 2013
Line 338:		Line 338:

	Right now the maximum length of a result string returned from the Control() function is 8192 (note this is for all Control() calls, not specific to SQL). This means that if you perform a GETALLROWS or GETNEXTROW and the result string is longer than 8192, you will get a buffer overflow exception. We are working to resolve this, so in the meantime, please tailor your retrieval methods accordingly. Either place an appropriate LIMIT on your queries, or stick to the Read() function.		Right now the maximum length of a result string returned from the Control() function is 8192 (note this is for all Control() calls, not specific to SQL). This means that if you perform a GETALLROWS or GETNEXTROW and the result string is longer than 8192, you will get a buffer overflow exception. We are working to resolve this, so in the meantime, please tailor your retrieval methods accordingly. Either place an appropriate LIMIT on your queries, or stick to the Read() function.
		+
		+	===Binary Data (blobs)===
		+
		+	Binary data is inherently tricky to work with in string format because some characters (single/double quotes, backslashes, and NULL) are considered special. In order to insert these into mysql using the SQL driver, you will need to "escape" these characters by prepending backslashes (and for NULL replacing with '\0'). If you do not escape these characters, and they are contained within your field data, the mysql driver will interpret the wrong data and your results will be undefined at best (possibly crash the system).
		+
		+	We recommend using base64 and base256 to encode your binary data when inserting into mysql. Please keep in mind the above issue that buffer sizes are still limited to 8192 bytes.

	===SQLEXECUTE vs. SQLQUERY Mixup===		===SQLEXECUTE vs. SQLQUERY Mixup===

Skrach: /* Insert/Update/Replace with Write() */

2011-03-17T18:47:42Z

Insert/Update/Replace with Write()

← Older revision		Revision as of 18:47, 17 March 2011
Line 84:		Line 84:

	===Insert/Update/Replace with Write()===		===Insert/Update/Replace with Write()===
-
-	~~''' Note: This section is in progress, incomplete, and all in my head. Check back in a day or two. '''~~

	If you know the SQL syntax, using SQLEXECUTE gives you the most control when writing to a table. You are able to build the SQL string yourself, and get the most detailed about how you would like the database to perform.		If you know the SQL syntax, using SQLEXECUTE gives you the most control when writing to a table. You are able to build the SQL string yourself, and get the most detailed about how you would like the database to perform.

Skrach: /* Insert/Update/Replace with Write() */

2011-03-17T18:47:06Z

Insert/Update/Replace with Write()

← Older revision		Revision as of 18:47, 17 March 2011
Line 114:		Line 114:
	====Specify the SQL table with SETWRITETABLE====		====Specify the SQL table with SETWRITETABLE====

-	The SETWRITETABLE control specifies which table the Write() function should use.	+	The SETWRITETABLE control specifies which table the Write() function should use. The function does '''not''' check that the table exists in the SQL database. Once you perform the Write(), if the table does not exist, the error will be reported in SQLGETLASTERROR. The syntax is as follows:

		+	<pre>
		+	result$ = Control( SqlLun, "SETWRITETABLE example" )
		+	</pre>
		+
		+	====Using a default value with Write()====
		+
		+	In SQL, you can specify that a column should be the default value by specifying "default" (without quotes) in the values list. This can be done easily using the SQLEXECUTE statement like this:
		+
		+	<pre>
		+	result$ = Control( SqlLun, "SQLEXECUTE REPLACE INTO example VALUES( default, -12, 'default id' )" )
		+	</pre>
		+
		+	for a default id column or for the default string column:
		+
		+	<pre>
		+	result$ = Control( SqlLun, "SQLEXECUTE REPLACE INTO example VALUES( 47, -12, default )" )
		+	</pre>
		+
		+	Unfortunately you cannot specify "default" with the Write() function, because for numerics it would be the wrong type, and for string columns, it would just write the string "default" instead of the actual default value.
		+
		+	Instead, we use the hex value 0xFF ("@FF@") to specify that the default value. This also means that in some cases, you will need to have a separate format list to specify the default value. For example:
		+
		+	<pre>
		+	SqlFmtDefID: format default$; intdata; strdata$
		+
		+	! Open and connect...
		+
		+	result$ = Control( SqlLun, "SETWRITETABLE example" )
		+
		+	default$ = "@FF@"
		+	intdata = -12
		+	strdata$ = "test string"
		+
		+	write( SqlLun, SqlFmtDefID) excp=err
		+
		+	</pre>

	===Query SQL Controls===		===Query SQL Controls===

Skrach at 18:20, 17 March 2011

2011-03-17T18:20:18Z

← Older revision		Revision as of 18:20, 17 March 2011
Line 63:		Line 63:
	There are two categories of statements that can be sent to the SQL server. The first are queries and commands that return data in tables (SELECT, DESCRIBE, etc.), the second are manipulation and other commands that return simple results (INSERT, CREATE, etc.). Because these 2 categories of statements are ideologically different and they return different kinds of results, we have created 2 different controls for issuing SQL statements to the database/server. SQLEXECUTE must be used to issue all non-query simple result statements, whereas SQLQUERY must be used to issue queries and statements that return tables as results. You must use the correct control with its corresponding SQL statement, or you will not be able to access the results of your SQL statement.		There are two categories of statements that can be sent to the SQL server. The first are queries and commands that return data in tables (SELECT, DESCRIBE, etc.), the second are manipulation and other commands that return simple results (INSERT, CREATE, etc.). Because these 2 categories of statements are ideologically different and they return different kinds of results, we have created 2 different controls for issuing SQL statements to the database/server. SQLEXECUTE must be used to issue all non-query simple result statements, whereas SQLQUERY must be used to issue queries and statements that return tables as results. You must use the correct control with its corresponding SQL statement, or you will not be able to access the results of your SQL statement.

-	===Non-Query SQL Controls===	+	===Non-Query SQL Controls with SQLEXECUTE===

	Let's say you want to update your customer table so that all customers in a certain zipcode have a new shipping price. Because SQL's UPDATE does not return a table of data as a result (like SELECT would), you must use the SQLEXECUTE control:		Let's say you want to update your customer table so that all customers in a certain zipcode have a new shipping price. Because SQL's UPDATE does not return a table of data as a result (like SELECT would), you must use the SQLEXECUTE control:
Line 89:		Line 89:
	If you know the SQL syntax, using SQLEXECUTE gives you the most control when writing to a table. You are able to build the SQL string yourself, and get the most detailed about how you would like the database to perform.		If you know the SQL syntax, using SQLEXECUTE gives you the most control when writing to a table. You are able to build the SQL string yourself, and get the most detailed about how you would like the database to perform.

-	The other option is to use the Write() function with a format. In order to do this, you must define your format list with the correct variable types, and specify which table you want to write to prior to inserting the data. See below for an example:	+	The other option is to use the Write() function with a format. In order to do this, you must define your format list with the correct variable types. This variable list '''must''' correspond to the columns in the SQL table. That is, for each column in the table, you must provide a value in the format list, they must be in the correct column order, and they must match the data type (numeric or string types). You must also specify which table you want to write to prior to inserting the data using the SETWRITETABLE control. See below for an example:

	<pre>		<pre>
Line 98:		Line 98:
	result$ = Control( SqlLun, "SETWRITETABLE example" )		result$ = Control( SqlLun, "SETWRITETABLE example" )

-	id$ = 47	+	id = 47
	intdata = -12		intdata = -12
	strdata$ = "test string"		strdata$ = "test string"
Line 105:		Line 105:

	</pre>		</pre>
		+
		+	Essentially, the Write() function is a wrapper around the SQLEXECUTE statement. Internally, we take the format list, and build an SQL statement to write the values into the specified table. The write is performed using the SQL "REPLACE" method, which performs a "DELETE" then "INSERT" if the row already exists (based on the table's keys and unique fields). So for example, the above Write() would be equivalent to the following SQLEXECUTE command:
		+
		+	<pre>
		+	result$ = Control( SqlLun, "SQLEXECUTE REPLACE INTO example VALUES( 47, -12, 'test string' )" )
		+	</pre>
		+
		+	====Specify the SQL table with SETWRITETABLE====
		+
		+	The SETWRITETABLE control specifies which table the Write() function should use.

Skrach: Started the write() function documentation

2011-03-17T17:34:03Z

Started the write() function documentation

← Older revision		Revision as of 17:34, 17 March 2011
Line 82:		Line 82:

	In this case, after the SQLEXECUTE control is issued, result$ contains a string reporting whether the SQL statement was executed successfully. If the UPDATE was issued to the database/server successfully, you can then use the GETROWSAFFECTED control to get the number of rows in the table that were affected by this update. In this case, result$ would contain the number of customers (rows) in the 'custtable' table that had the zipcode 98765.		In this case, after the SQLEXECUTE control is issued, result$ contains a string reporting whether the SQL statement was executed successfully. If the UPDATE was issued to the database/server successfully, you can then use the GETROWSAFFECTED control to get the number of rows in the table that were affected by this update. In this case, result$ would contain the number of customers (rows) in the 'custtable' table that had the zipcode 98765.
		+
		+	===Insert/Update/Replace with Write()===
		+
		+	''' Note: This section is in progress, incomplete, and all in my head. Check back in a day or two. '''
		+
		+	If you know the SQL syntax, using SQLEXECUTE gives you the most control when writing to a table. You are able to build the SQL string yourself, and get the most detailed about how you would like the database to perform.
		+
		+	The other option is to use the Write() function with a format. In order to do this, you must define your format list with the correct variable types, and specify which table you want to write to prior to inserting the data. See below for an example:
		+
		+	<pre>
		+	SqlFmt: format id; intdata; strdata$
		+
		+	! Open and connect...
		+
		+	result$ = Control( SqlLun, "SETWRITETABLE example" )
		+
		+	id$ = 47
		+	intdata = -12
		+	strdata$ = "test string"
		+
		+	write( SqlLun, SqlFmt) excp=err
		+
		+	</pre>
		+

	===Query SQL Controls===		===Query SQL Controls===

Skrach: /* Known Issues */

2009-12-10T19:29:00Z

Known Issues

← Older revision		Revision as of 19:29, 10 December 2009
Line 270:		Line 270:

	Right now the maximum length of a result string returned from the Control() function is 8192 (note this is for all Control() calls, not specific to SQL). This means that if you perform a GETALLROWS or GETNEXTROW and the result string is longer than 8192, you will get a buffer overflow exception. We are working to resolve this, so in the meantime, please tailor your retrieval methods accordingly. Either place an appropriate LIMIT on your queries, or stick to the Read() function.		Right now the maximum length of a result string returned from the Control() function is 8192 (note this is for all Control() calls, not specific to SQL). This means that if you perform a GETALLROWS or GETNEXTROW and the result string is longer than 8192, you will get a buffer overflow exception. We are working to resolve this, so in the meantime, please tailor your retrieval methods accordingly. Either place an appropriate LIMIT on your queries, or stick to the Read() function.
		+
		+	===SQLEXECUTE vs. SQLQUERY Mixup===
		+
		+	An obvious question you might ask is, "what happens when you perform a query with SQLEXECUTE or a non-query (for instance INSERT) with SQLQUERY?" "Undefined" would be the general answer, but here is a little more detail for the most likely scenarios. Because we do not check your SQL syntax (and the underlying library only does enough to make sure it is processing a valid statement, not the correct kind of statement), '''your statement will be processed by the MySql database'''.
		+
		+	For SQLQUERY, this means that if you perform a data modification statement, the data '''WILL''' be modified! Any controls used to handle results will '''not''' work, however, because it will be checking an invalid result type. Therefore you cannot rely on your program to know whether the control was successful.
		+
		+	For SQLEXECUTE, this means that if you perform a query, the query '''WILL''' be processed (thus theoretically performing any stored procedures, triggers, etc) on the server. The result table, however, will not be returned to the local workstation, effectively locking the library because you cannot perform another SQL operation until this one has been completed. You will have to disconnect and reconnect to the server in order to perform any more SQL operations.
		+
		+	Unfortunately there is no easy way to solve this issue without hard coding checks for all SQL syntax to ensure you are using the correct control. Instead we leave it up to you to know which control to use, and to use them correctly. If you run across any other undefined behavior, please post it here or on the discussion page.

	==Implementation Details==		==Implementation Details==

Skrach: /* MySql Server Required */

2009-12-04T17:28:04Z

MySql Server Required

← Older revision		Revision as of 17:28, 4 December 2009
Line 9:		Line 9:
	===MySql Server Required===		===MySql Server Required===

-	In order to use the SQL device, you must have a MySql Server configured and installed someplace accessible to your workstation. If you do not already have one, you can download and install one for free locally. First get the proper Mysql 5.1 Community Server installation at the [http://dev.mysql.com/downloads/mysql/5.1.html#downloads MySql Website]. Although the SQL device should work with all versions of MySql server, we advise you to get version 5.1 because that is what we have been using for testing. Follow the directions through the installation wizard and refer to the [http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard.html MySql Docs] for reference. Once you have the Community Server installed and configured, you will need to configure a databases and tables to work with using the [http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html CREATE DATABASE and CREATE TABLE] operators in the MySql command-line client.	+	In order to use the SQL device, you must have a MySql Server configured and installed someplace accessible to your workstation. If you do not already have one, you can download and install one for free locally. First get the proper Mysql 5.1 Community Server installation at the [http://dev.mysql.com/downloads/mysql/5.1.html#downloads MySql Website]. Although the SQL device should work with all versions of MySql server, we advise you to get version 5.1 because that is what we have been using for testing. Follow the directions through the installation wizard and refer to the [http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard.html MySql Docs] for reference. Once you have the Community Server installed and configured, you will need to configure databases and tables to work with using the [http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html CREATE DATABASE and CREATE TABLE] operators in the MySql command-line client.

	===Sample Programs===		===Sample Programs===

Skrach: /* Sample Programs */

2009-12-04T17:24:00Z

Sample Programs

← Older revision		Revision as of 17:24, 4 December 2009
Line 13:		Line 13:
	===Sample Programs===		===Sample Programs===

-	~~Soon we will include a link~~ to ~~some sample IB programs with~~ MySql ~~table definition files..~~.	+	Please see the [[SQL Driver Sample Programs]] page for examples on how to set up your MySql database and using Internet Basic to utilize it.

	==Using the SQL Device==		==Using the SQL Device==

Skrach at 21:36, 2 December 2009

2009-12-02T21:36:03Z

← Older revision		Revision as of 21:36, 2 December 2009
Line 10:		Line 10:

	In order to use the SQL device, you must have a MySql Server configured and installed someplace accessible to your workstation. If you do not already have one, you can download and install one for free locally. First get the proper Mysql 5.1 Community Server installation at the [http://dev.mysql.com/downloads/mysql/5.1.html#downloads MySql Website]. Although the SQL device should work with all versions of MySql server, we advise you to get version 5.1 because that is what we have been using for testing. Follow the directions through the installation wizard and refer to the [http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard.html MySql Docs] for reference. Once you have the Community Server installed and configured, you will need to configure a databases and tables to work with using the [http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html CREATE DATABASE and CREATE TABLE] operators in the MySql command-line client.		In order to use the SQL device, you must have a MySql Server configured and installed someplace accessible to your workstation. If you do not already have one, you can download and install one for free locally. First get the proper Mysql 5.1 Community Server installation at the [http://dev.mysql.com/downloads/mysql/5.1.html#downloads MySql Website]. Although the SQL device should work with all versions of MySql server, we advise you to get version 5.1 because that is what we have been using for testing. Follow the directions through the installation wizard and refer to the [http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard.html MySql Docs] for reference. Once you have the Community Server installed and configured, you will need to configure a databases and tables to work with using the [http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html CREATE DATABASE and CREATE TABLE] operators in the MySql command-line client.
-
-	~~==Using the SQL Device==~~

	===Sample Programs===		===Sample Programs===

	Soon we will include a link to some sample IB programs with MySql table definition files...		Soon we will include a link to some sample IB programs with MySql table definition files...
		+
		+	==Using the SQL Device==

	===Basic Syntax===		===Basic Syntax===

Skrach at 21:34, 2 December 2009

2009-12-02T21:34:25Z

← Older revision		Revision as of 21:34, 2 December 2009
Line 41:		Line 41:

	result$ contains a string reporting whether each of these controls were executed successfully. The disconnect control takes no arguments. It disconnects from the current server/database, and readies the SQL device for another connection. You must disconnect a connected SQL device before you try to connect to another database/server.		result$ contains a string reporting whether each of these controls were executed successfully. The disconnect control takes no arguments. It disconnects from the current server/database, and readies the SQL device for another connection. You must disconnect a connected SQL device before you try to connect to another database/server.
		+
		+	===Error Reporting===
		+
		+	Most errors you need to know about are contained in the return value from the Control() function, preceded by "-ERR" for convenience. If more information is needed, some times you can use the GETLASTERROR control to get more details about the last internal error encountered:
		+
		+	<pre>
		+	Open( SqlLun ) "SQL"
		+	result$ = Control( SqlLun, "CONNECT sampledb localhost testuser testpassword" ) ! Default port 3306 is OK
		+
		+	! Check for error
		+	if sub( result$, 1, 1 ) EQ "-" error$ = Control( SqlLun, "GETLASTERROR" )
		+
		+	! Report errors
		+	print result$; " >> "; error$
		+
		+	Close( SqlLun )
		+	</pre>

	===Issuing SQL statements to the database/server===		===Issuing SQL statements to the database/server===