SQL Driver Sample Programs - Revision history

Skrach at 20:22, 10 December 2009

2009-12-10T20:22:10Z

← Older revision		Revision as of 20:22, 10 December 2009
Line 210:		Line 210:
	==Interactive SQL Interpreter==		==Interactive SQL Interpreter==

-	Source: [[Media:~~sqli~~.ibs\|~~sqli~~.ibs]]	+	Source: [[Media:Sqli.ibs\|Sqli.ibs]]

	This program prompts you for MySql database connection info, then once connected allows you to execute Query and Non-Query SQL statements. Once connected, choose "Q" for query, or "N" for non-query, and enter the SQL statement you would like to execute. If you perform an SQLEXECUTE, the number of rows affected will be printed on the bottom. If you perform an SQLQUERY, the query results will be displayed in a pop-up window - hit any key to close the window and return to the main screen. From the prompt enter "E" to exit from the program.		This program prompts you for MySql database connection info, then once connected allows you to execute Query and Non-Query SQL statements. Once connected, choose "Q" for query, or "N" for non-query, and enter the SQL statement you would like to execute. If you perform an SQLEXECUTE, the number of rows affected will be printed on the bottom. If you perform an SQLQUERY, the query results will be displayed in a pop-up window - hit any key to close the window and return to the main screen. From the prompt enter "E" to exit from the program.

Skrach at 20:21, 10 December 2009

2009-12-10T20:21:32Z

← Older revision		Revision as of 20:21, 10 December 2009
Line 207:		Line 207:
	EOF:		EOF:
	</pre>		</pre>
		+
		+	==Interactive SQL Interpreter==
		+
		+	Source: [[Media:sqli.ibs\|sqli.ibs]]
		+
		+	This program prompts you for MySql database connection info, then once connected allows you to execute Query and Non-Query SQL statements. Once connected, choose "Q" for query, or "N" for non-query, and enter the SQL statement you would like to execute. If you perform an SQLEXECUTE, the number of rows affected will be printed on the bottom. If you perform an SQLQUERY, the query results will be displayed in a pop-up window - hit any key to close the window and return to the main screen. From the prompt enter "E" to exit from the program.

Skrach: /* Example IB Programs */

2009-12-10T16:21:22Z

Example IB Programs

← Older revision		Revision as of 16:21, 10 December 2009
Line 52:		Line 52:
	===Sample Program 1: Insert and Read===		===Sample Program 1: Insert and Read===

-	[[Media:Sql1.ibs]]	+	Source: [[Media:Sql1.ibs\|Sql1.ibs]]

	In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:		In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:
Line 133:		Line 133:

	===Sample Program 2: Update and GETNEXTROW===		===Sample Program 2: Update and GETNEXTROW===
		+
		+	Source: [[Media:Sql2.ibs\|Sql2.ibs]]

	This example program is structured similar to sample program 1, except for the internal SQL interface operations. This time, we will update the table with some new info and select the rows that have changed. All of the connect, disconnect, and error handling code has not changed. After connecting to the database, we start by updating the 'simple' table:		This example program is structured similar to sample program 1, except for the internal SQL interface operations. This time, we will update the table with some new info and select the rows that have changed. All of the connect, disconnect, and error handling code has not changed. After connecting to the database, we start by updating the 'simple' table:
Line 170:		Line 172:

	===Sample Program 3: GETALLROWS===		===Sample Program 3: GETALLROWS===
		+
		+	Source: [[Media:Sql3.ibs\|Sql3.ibs]]

	This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:		This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:

Skrach: /* Example IB Programs */

2009-12-10T16:20:16Z

Example IB Programs

← Older revision		Revision as of 16:20, 10 December 2009
Line 51:		Line 51:

	===Sample Program 1: Insert and Read===		===Sample Program 1: Insert and Read===
		+
		+	[[Media:Sql1.ibs]]

	In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:		In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:

Skrach: /* Sample Program 3: GETALLROWS */

2009-12-09T21:45:28Z

Sample Program 3: GETALLROWS

← Older revision		Revision as of 21:45, 9 December 2009
Line 170:		Line 170:

	This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:		This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:
		+
		+	<pre>
		+	result$ = Control( 10, "SQLQUERY SELECT * FROM custtable" )
		+	</pre>
		+
		+	A simple query to get all the data from the customer table. Next we execute the control to get the entire result table back in one string:
		+
		+	<pre>
		+	result$ = Control( 10, "GETALLROWS @0D@ ," )
		+	</pre>
		+
		+	This control takes two character delimiters as arguments, one to separate rows and one for columns. For this example we are separating columns with commas like before, but we are putting 0x0D (a carriage return) between the rows. This way we can print the entire string to a file, and each row will be on its own line:
		+
		+	<pre>
		+	erase "sqldump.txt", dir="tmp" noexcp
		+	create "sqldump.txt", dir="tmp" noexcp
		+	close( 11 ) & open( 11 ) "sqldump.txt", dir="tmp"
		+
		+	printfile( 11 ) result$
		+	</pre>
		+
		+	And once the file is populated with our result rows, we can read the file back line by line to print them out. We only do this here to demonstrate that the results are there, normally you would want to do some non-trivial processing or report generating here.
		+
		+	<pre>
		+	file( 11) pos=bof
		+	do
		+	input( 11 ) data$,excp=EOF
		+	print '>>>'; data$
		+	loop
		+	EOF:
		+	</pre>

Skrach at 19:53, 9 December 2009

2009-12-09T19:53:19Z

← Older revision		Revision as of 19:53, 9 December 2009
Line 50:		Line 50:
	To bypass the password prompt, comment the first line and set password$ to your MySql root password. In the following programs, we assume password$ contains an unencrypted string with your root password when we connect to the MySql database.		To bypass the password prompt, comment the first line and set password$ to your MySql root password. In the following programs, we assume password$ contains an unencrypted string with your root password when we connect to the MySql database.

-	===Sample Program 1: ~~Read and~~ Insert===	+	===Sample Program 1: Insert and Read===

	In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:		In this example, we demonstrate how to connect and disconnect from a MySql database, as well as insert and query the database. It is meant to demonstrate the basic operations you might use in any IB program that needs to interface with MySql. We will take you step by step through the code for this program, explaining what each function does. First we connect to the 'sampledb' database that was imported in the section above:
Line 166:		Line 166:

	Instead of reading the row into the variable list like in sample program 1, we read all of the columns into a single result string, delimited by commas (','). If the control results in an error string (which starts with the minus '-' character), then there are no more rows to read and we break out.		Instead of reading the row into the variable list like in sample program 1, we read all of the columns into a single result string, delimited by commas (','). If the control results in an error string (which starts with the minus '-' character), then there are no more rows to read and we break out.
		+
		+	===Sample Program 3: GETALLROWS===
		+
		+	This example program demonstrates how you can use the GETALLROWS control to retrieve all of the rows in your result set in one string. We will use a different database table that is less trivial than our simple table from the last examples. All of the connection options are the same as before, so we will jump right in with the query:

Skrach: /* Sample Program 2: Update and GETNEXTROW */

2009-12-08T19:46:44Z

Sample Program 2: Update and GETNEXTROW

← Older revision		Revision as of 19:46, 8 December 2009
Line 157:		Line 157:
	<pre>		<pre>
	do		do
-	result$ = Control( 10, "GETNEXTROW ," )	+	result$ = Control( 10, "GETNEXTROW ," )
-	if sub(result$,1,1) EQ "-" Goto EOF	+	if sub(result$,1,1) EQ "-" Goto EOF
-	print ">> "; result$; " <<"	+	print ">> "; result$; " <<"
	Loop		Loop

Skrach: /* Example IB Programs */

2009-12-08T19:37:51Z

Example IB Programs

← Older revision		Revision as of 19:37, 8 December 2009
Line 128:		Line 128:
	>> 9 \| sampleprog1 \| 92 \| 2009-12-08 13:26:05 <<		>> 9 \| sampleprog1 \| 92 \| 2009-12-08 13:26:05 <<
	Disconnect: +OK Disconnected from database and server.		Disconnect: +OK Disconnected from database and server.
		+	</pre>

	===Sample Program 2: Update and GETNEXTROW===		===Sample Program 2: Update and GETNEXTROW===

Skrach: /* Example IB Programs */

2009-12-08T19:37:00Z

Example IB Programs

← Older revision		Revision as of 19:37, 8 December 2009
Line 95:		Line 95:
	print ">> "; id$; " \| "; string$; " \| "; number$; " \| "; timestamp$; " <<"		print ">> "; id$; " \| "; string$; " \| "; number$; " \| "; timestamp$; " <<"
	Loop		Loop
		+
		+	EOF:
	</pre>		</pre>

Line 127:		Line 129:
	Disconnect: +OK Disconnected from database and server.		Disconnect: +OK Disconnected from database and server.

-	~~Done!~~	+	===Sample Program 2: Update and GETNEXTROW===
		+
		+	This example program is structured similar to sample program 1, except for the internal SQL interface operations. This time, we will update the table with some new info and select the rows that have changed. All of the connect, disconnect, and error handling code has not changed. After connecting to the database, we start by updating the 'simple' table:
		+
		+	<pre>
		+	result$ = Control( 10, "SQLEXECUTE UPDATE simple SET string='sampleprog2' _
		+	WHERE number > 50" )
	</pre>		</pre>
		+
		+	Here we alter all rows that have their number column greater than 50 by setting the string column. Again, because this is a non-query SQL statement, we must use the SQLEXECUTE control. In order to find out how many rows were affected by this UPDATE command, we execute the GETROWSAFFECTED control:
		+
		+	<pre>
		+	result$ = Control( 10, "GETROWSAFFECTED" )
		+	print "Number of rows updated: "; result$
		+	</pre>
		+
		+	The GETROWSAFFECTED control returns the number of rows that were affected by the last SQLEXECUTE control. Any rows that were altered by the SQL statement are counted. Next, we query the database to retrieve all of the rows that have the new string column:
		+
		+	<pre>
		+	result$ = Control( 10, "SQLQUERY SELECT * FROM simple WHERE string='sampleprog2' _
		+	ORDER BY timestamp DESC LIMIT 10" )
		+	</pre>
		+
		+	We again sort the rows in descending timestamp order and limit the results to 10 rows. This time, instead of using the formatted Read() function to retrieve the result data, we use the GETNEXTROW control to retrieve each row, where each column is delimited by the character provided:
		+
		+	<pre>
		+	do
		+	result$ = Control( 10, "GETNEXTROW ," )
		+	if sub(result$,1,1) EQ "-" Goto EOF
		+	print ">> "; result$; " <<"
		+	Loop
		+
		+	EOF:
		+	</pre>
		+
		+	Instead of reading the row into the variable list like in sample program 1, we read all of the columns into a single result string, delimited by commas (','). If the control results in an error string (which starts with the minus '-' character), then there are no more rows to read and we break out.

Skrach: /* Sample Program 1: Read and Insert */

2009-12-08T18:35:41Z

Sample Program 1: Read and Insert

← Older revision		Revision as of 18:35, 8 December 2009
Line 105:		Line 105:

	And that's it! This sample program should serve as the prototype for any Internet Basic programs you want to write that interface with a MySql database.		And that's it! This sample program should serve as the prototype for any Internet Basic programs you want to write that interface with a MySql database.
		+
		+	Below is an example of the output you might receive after running the program a few times:
		+
		+	<pre>
		+	Begin sample program 1
		+
		+	Enter MySql password: testingpassword
		+	Connect: +OK Successfully connected to server and database.
		+	Insert: +OK Statement successfully executed.
		+	Select: +OK Query successfully executed.
		+	>> 18 \| sampleprog1 \| 6 \| 2009-12-08 13:34:09 <<
		+	>> 17 \| sampleprog1 \| 41 \| 2009-12-08 13:34:05 <<
		+	>> 16 \| sampleprog1 \| 91 \| 2009-12-08 13:34:03 <<
		+	>> 15 \| sampleprog1 \| 1 \| 2009-12-08 13:34:00 <<
		+	>> 14 \| sampleprog1 \| 80 \| 2009-12-08 13:33:29 <<
		+	>> 13 \| sampleprog1 \| 52 \| 2009-12-08 13:26:37 <<
		+	>> 12 \| sampleprog1 \| 12 \| 2009-12-08 13:26:20 <<
		+	>> 11 \| sampleprog1 \| 41 \| 2009-12-08 13:26:14 <<
		+	>> 10 \| sampleprog1 \| 19 \| 2009-12-08 13:26:10 <<
		+	>> 9 \| sampleprog1 \| 92 \| 2009-12-08 13:26:05 <<
		+	Disconnect: +OK Disconnected from database and server.
		+
		+	Done!
		+	</pre>