2010-07-16 20:49

/*
GUIBEGIN

STRING SqlCmds
Select
Insert
Update
Delete
Create table
Drop table
DEND

WINDOW , 140, 202, 377, 316, POPUP|CAPTION|SYSMENU|MINBOX|MAXBOX|THICK, , ODBC Example
	FONT 8, 400, MS Shell Dlg
	ENTRY 43, 4, 49, 13, H_AUTO|BORDER|TABSTOP, CLIENTEDGE, ODBC_DatabaseName, ALT "D"
	TEXT 7, 6, 34, 8, RIGHT|GROUP, , , , &Database:
	TEXT 101, 6, 37, 8, RIGHT|GROUP, , , , SQL &Table:
	COMBO 140, 4, 102, 205, DROP|DISABLED|VSCROLL|TABSTOP, , ODBC_TableName, CTRL "T"
	PUSH 248, 4, 15, 14, LEFT|RIGHT|DISABLED|TABSTOP, , ODBC_Browse, , ...
	TEXT 272, 6, 38, 8, RIGHT|GROUP, , , , &Operation:
	COMBO 313, 4, 60, 111, PRESET|DISABLED|VSCROLL|TABSTOP, , ODBC_Operation, CTRL "O", SqlCmds
	TEXT 8, 31, 50, 8, RIGHT|GROUP, , , , SQL &statement:
	ENTRY 60, 28, 204, 14, H_AUTO|DISABLED|BORDER|TABSTOP, CLIENTEDGE, ODBC_Statement, ALT "S"
	GROUP 273, 21, 99, 26, , , , , Columns
	RADIO 278, 32, 28, 10, AUTO|DISABLED|GROUP, , ODBC_AllCols, ALT "A", &All
	RADIO 314, 32, 44, 10, AUTO|DISABLED, , ODBC_SelCols, ALT "L", Se&lected
	TEXT 3, 300, 47, 8, RIGHT|GROUP, , , , ODBC Driver:
	TEXT 51, 299, 216, 10, NOPREFIX|SUNKEN|GROUP, , ODBC_driver
	PUSH 282, 298, 40, 14, TABSTOP, , ODBC_Ok, , OK
	PUSH 329, 298, 40, 14, TABSTOP, , ODBC_Cancel, , Cancel
	TEXT 10, 50, 26, 8, GROUP, , , , Results:
	FONT 8, 400, FixedSys
	LIST 6, 56, 270, 200, NOTIFY|MULTI|BORDER|VSCROLL|HSCROLL|GROUP|TABSTOP, INDEX, ODBC_Result
DEND
GUIEND
*/

/* An OPTIONS "NOSOURCE" may appear here, but _not_ before the GUIBEGIN/GUIEND. */
OPTIONS "C_CALL LABELCHECK NOSOURCE"

/* We plan to use the FUNCDEF interface, and some operating system
 * functions may return unsigned values that are greater than the
 * precision offered by NUMERIC DIGITS 9.
 */
NUMERIC DIGITS 10

/* Load needed add-on functions. */
LIBRARY rexxgui, rxodbc

/* Get the path to my script. */
PARSE SOURCE . . file_name

/* The config file ODBC_defaults.txt contains the default ODBC driver
 * we want to use. Construct the full path of the config file.
 */
file_name = FILESPEC('D', file_name) || FILESPEC('P', file_name) || "ODBC_defaults.txt"

/* Read in all lines from the config file, if found. */
i = LOADTEXT("MS.", file_name, 'TLB') 
IF i == 1 THEN DO i = 1 TO ms.0
	IF LEFT(ms.i, 1) \== '*' THEN INTERPRET ms.i
END
/* Assume SQL Lite if no config file. */
ELSE defaultdatabase = 'SQLite3 Datasource'

/* Let Reginald raise ERROR condition for any error in a Odbc function call */
odbcerr = "ERROR"
odbcheading = 1

DO
	/* Get the ODBC environment handle. */
	odbcallocenv()

	/* The ODBC functions raise ERROR. So to handle error checking and cleanup, we
	 * simply CATCH ERROR.
	 */
	CATCH ERROR
		CONDITION('M')
		RETURN
END

/* Let Reginald raise SYNTAX condition for any error in a Gui function call */
guierr = "SYNTAX"
guiheading = 1

/* Choose "Select" initially in the "Operation" listbox. */
odbc_operation = "Select" 

/* Choose "All" initially for the "Columns". */
odbc_allcols = 1

/* Here you may want to set GuiX, GuiY, GuiWidth, and GuiHeight variables from some
 * configuration file or saved registry key.
 */

/* Create our window, and activate/show it */
guicreatewindow('NORMAL')

/* Uncomment to check for function name conflicts. */
/* RxFuncCheck() */

/* Initially not connected to any database. */
database = 0

/* Choose the ODBC Data source (name), if the default choice is not available. */

datasrcname = choose_datasource()
IF datasrcname == "" THEN SIGNAL RETURN

again:
/* Do our event loop for our main window. */
DO FOREVER

	guigetmsg()

	IF EXISTS('GuiObject') == 0 THEN DO
		IF EXISTS('GuiSignal') THEN DO
		END
	END

	ELSE DO

		IF EXISTS('GuiSignal') == 0 THEN DROP (guiobject)

		ELSE SELECT guiobject

			WHEN 0 THEN NOP


			OTHERWISE
		END /* SELECT GuiObject */

	END /* Some child script signaled us */

	CATCH SYNTAX
		IF guiinfo() \== "" THEN DO /* Don't do the following if all windows are closed */
			CONDITION('M')
			SIGNAL again
		END

	CATCH HALT
	FINALLY
		guidestroywindow()
END
RETURN




/************************ perform_SQLcode() **********************
 * Performs the SQL operation based upon the user's settings.
 *
 * The following variables must be set prior to this call:
 * DataSrcName =		The name of the data source.
 */

perform_sqlcode:

/* Get the current "Columns" setting. */
guigetctlvalue("ODBC_AllCols")

/* Get the chosen "Operation". */
guigetctlvalue("ODBC_Operation")
UPPER odbc_operation

/* Get the current "SQL Table" (name). */
guigetctlvalue("ODBC_TableName")

/* No column names yet fetched. */
sql_all_colname.0 = 0

/* For an insert or select operation, we need the column names.
 * So first, get the names of the columns in the current table(s).
 * Fill in the "SQL_all_colname." stem with those names.
 */
IF odbc_operation == 'SELECT' | odbc_operation == 'INSERT' THEN DO
	IF fetch_all_columns() \== 0 THEN RETURN
END

/* Does the user want to operate upon specific columns? */
IF odbc_allcols == 2 THEN DO

	/* Allow the user to select the specified columns he wants. Fill in the
	 * "SQL_selcols." stem with those selections (by INDEX).
	 */
	IF sql_pick_columns() \== 0 THEN SIGNAL perform_sqlcode_end
END
ELSE
	/* Select all columns. */
	sql_selcols.0 = ""

/* Perform the SQL operation. */
SELECT odbc_operation
	WHEN 'DELETE'		THEN sql_delete_code()  
	WHEN 'SELECT'		THEN sql_select_code()
	WHEN 'INSERT'		THEN sql_insert_row()
	WHEN 'UPDATE'		THEN sql_update_code()			
	WHEN 'CREATE TABLE'	THEN sql_create_table()	
	WHEN 'DROP TABLE'	THEN sql_drop_table()
	OTHERWISE DO
		guisay("Need to add code to handle a" odbc_operation "command.", "OK|STOP")
		RETURN
	END
END

perform_sqlcode_end:
/* End the transaction. */
odbcfreestmt()

RETURN





/*********************** sql_pick_columns() ***********************
 * Called by perform_SQLcode() to let the user choose the columns
 * to operate upon.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =		The name of the ODBC data source.
 * Stmt =				Statement handle set by ODBCInit().
 * Database = 			Database handle set by connect_to_database().
 * ODBC_DatabaseName = 	The database name.
 * ODBC_TableName =		The table name specified.
 * SQL_all_colname. =	A list of the column names, where SQL_all_colname.0
 *						is a count of names, and SQL_all_colname.1 to
 *						SQL_all_colname.XXX are those names.
 */

sql_pick_columns:
/* Allow the user to choose the rows. */
createobject("ODBC_PickColumns.rex")

/* Do an event loop until ODBC_PickColumns.rex calls GuiWake(). NOTE:
 * If the user closes the "Pick columns" window, this implicitly
 * calls GuiWake with GuiObject == 'ODBC_PICKCOLUMNS' and DROPs GuiSignal.
 */
DO UNTIL guiobject == 'ODBC_PICKCOLUMNS'

	/* Wait for some event to happen. */
	guigetmsg()

	/* Did our "Pick columns" window call GuiWake()? */
	IF guiobject == 'ODBC_PICKCOLUMNS' THEN DO

		/* If GuiSignal is DROP'ed, then the user clicked the close box, or the
		 * cancel button. Otherwise, it must be the Ok button so GuiSignal is
		 * "OK".
		 */
		IF guisignal == "OK" THEN DO

			/* Call the object's FillStem() to fill in our stem with the
			 * list of selected columns (by INDEX).
			 */
			odbc_pickcolumns~fillstem(sql_selcols.)

			RETURN 0

		END
	END

	FINALLY
		/* We don't need the object any more. Make sure it is DROP'ed. */
		DROP odbc_pickcolumns

END /* UNTIL */
RETURN 1





/*********************** sql_insert_row() ***********************
 * Called by perform_SQLcode() to insert a row into the current
 * database.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =		The name of the ODBC data source.
 * Stmt =				Statement handle set by ODBCInit().
 * Database = 			Database handle set by connect_to_database().
 * ODBC_DatabaseName = 	The database name.
 * ODBC_TableName =		The table name specified.
 * SQL_all_colname. =	A list of the column names, where SQL_all_colname.0
 *						is a count of names, and SQL_all_colname.1 to
 *						SQL_all_colname.XXX are those names.
 */

sql_insert_row:
/* Allow the user to choose the rows. */
createobject("ODBC_GetInsertCmd.rex")

againpickrow:
/* Do an event loop until ODBC_GetInsertCmd.rex calls GuiWake(). NOTE:
 * If the user closes the "Choose Database" window, this implicitly
 * calls GuiWake with GuiObject == 'ODBC_GETINSERTCMD' and DROPs GuiSignal.
 */
DO UNTIL guiobject == 'ODBC_GETINSERTCMD'

	/* Wait for some event to happen. */
	guigetmsg()

	/* Did our "Insert row" window call GuiWake()? */
	IF guiobject == 'ODBC_GETINSERTCMD' THEN DO

		/* If GuiSignal is DROP'ed, then the user clicked the close box, or the
		 * cancel button. Otherwise, it must be the Insert button so GuiSignal is
		 * the SQL command string we need to insert.
		 */
		IF EXISTS("GuiSignal") THEN DO

			/* Send the SQL command to insert the row into the database, and check for an error. */
			odbcexecute(guisignal)

			CATCH ERROR
				guisay("Insert row failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")

		END
	END

	FINALLY
		/* We don't need the object anymore. */
		DROP odbc_getinsertcmd

END /* UNTIL */
RETURN





/******************** fetch_all_columns() ********************
 * Fetches the column names and descriptors for the specified
 * table.
 *
 * error = fetch_all_columns(Stmt, System, TableName)
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =		The name of the ODBC data source.
 * ODBC_DatabaseName = 	The database name.
 * ODBC_TableName =		The table name specified.
 * ODBC_Operation =		The operation, for example "INSERT".
 *
 * RETURNS: 0 if success, or 1 if an error.
 *
 * Also sets SQL_all_colname. stem variable to the column names, where
 * SQL_all_colname.0 is a count of columns, and SQL_all_colname.1 to
 * SQL_all_colname.X are those column names. Sets SQL_coltype. to the
 * column types.
 */

fetch_all_columns: PROCEDURE EXPOSE datasrcname odbc_databasename odbc_tablename odbc_operation sql_all_colname. sql_coltype. sql_length. sql_scale.

/* Format the SQL command to fetch the desired column names. What the command
 * string looks like depends upon the Data source.
 */
SELECT
	WHEN POS('DB2', datasrcname) \== 0 THEN DO
		IF odbc_operation == 'INSERT' THEN
			sql_statement = "Select name,coltype,length,scale from sysibm.syscolumns "
		ELSE DO
			sql_statement = "Select name from sysibm.syscolumns ",
		 			"where tbcreator = '"odbc_databasename"'",
	    			"and tbname = '" || TRANSLATE(odbc_tablename) || "'",
	    			/* Without this ordering, the column headers and column data will
	    			 * almost certainly NOT be fetched in the same order
	    			 */
	    			"order by colno asc"
	    END
	END
	WHEN POS('SQLITE', datasrcname) \== 0 THEN DO
		sql_statement = "Select sql from sqlite_master where ((type = 'table') and (name = '"odbc_tablename"'))",
					"and sql like 'CREATE TABLE %' "
	END
	OTHERWISE DO
		guisay("Must write code to format an SQL command to fetch the column names for" datasrcname,  "OK|STOP")
		RETURN 1
	END
END

/* NOTE: We CATCH ERROR which may be raised by OdbcExecute or OdbcFetch. */
DO
	/* Execute the SQL command to fetch all the column names. */
	odbcexecute(sql_statement, "columns")

	/* Now we need to actually fetch each line of the returned results (ie, column names). */

	/* Get the next row of data (ie, column name/description). */
	DO WHILE odbcfetch("data", columns) \== "DONE"

		/* Add this column name to the SQL_all_colname. stem. */
		sql_all_colname.0 = sql_all_colname.0 + 1
		sql_all_colname.[sql_all_colname.0] = data.1
		extract_col_names()

 		/* If SQLite, some fields returned by OdbcFetch have no meaningful values. */
		IF POS('SQLITE', datasrcname) == 0 THEN DO

			/* If an INSERT operation, we'll store some additional info
			 * to display to the user (in the Insert row dialog).
			 */
			IF odbc_operation == 'INSERT' THEN DO

				/* Store the column datatype, length, and scale. */
				sql_coltype.[sql_all_colname.0] = data.4
				sql_length.[sql_all_colname.0] = data.5
				sql_scale.[sql_all_colname.0] = data.7
			END
		END
	END

	/* Check if we found the table. (If so, it should have more than 1 column). */
	IF sql_all_colname.0 == 1 THEN DO
		err = 100	/* Standard SQL error number for "nothing found" */
		guisay(odbc_tablename "not found for system" odbc_databasename, "OK|STOP")
	END

	/* Success! */
	ELSE err = 0

	/**********************************************************************
	 * ERROR handling
	 *********************************************************************/
	CATCH ERROR
		guisay("Fetch columns failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")
		err = 1
END

/* Free some memory allocated for the operation. */
odbcfreestmt()

RETURN err





/*********************** extract_col_names() ***************************
 * Depending on the current database, we might need to extract the
 * actual column names from whatever we've fetched from the system
 * table. A typical example of this is SQLite which returns the
 * columns as a string such as "(col_one varchar(10), col_two smallint)".
 */ 

extract_col_names:

IF POS('SQLITE', datasrcname) \== 0 THEN SIGNAL extract_sqlite_columns
RETURN





/******************** extract_sqlite_columns() ********************
 * Extracts the SQLite column names from an SQL result string.
 */

extract_sqlite_columns:

/* Sometimes SQLite can return the table definition as multiple lines if the user
 * created it via SQLite3 and input the definition on  multiple lines. So we need
 * to replace all '0D0A'X (ie, the marker for the end of each line) with a space.
 */
sql_all_colname.1 = CHANGESTR('0D0A'x, sql_all_colname.1, ' ')

/* Parse out the column names/descriptions (which start after the first open parentheses). */
PARSE VAR sql_all_colname.1 . '(' col_defs

sql_all_colname.0 = 0

DO WHILE col_defs \= ""

	/* Parse the next column name/description. It is separated from subsequent ones by a comma. */
	PARSE VAR col_defs next_col ',' col_defs

	/* Do we have matching left/right parentheses? */
	IF POS('(', next_col) \== 0 & POS(')', next_col) == 0 THEN DO

		/* Nope. We have a decimal type of column defined such as
		 * deccol int(10,2). In that case, our PARSE VAR above
		 * mistakenly broke off only part of this column's definition.
		 * We have to grab the stuff _after_ the first comma (upto
		 * the _next_ comma too).
		 */
		PARSE VAR col_defs temp ',' col_defs
		next_col = next_col || ',' || temp
	END

	/* Did we get another column name/description? If not, we're done. */
	IF next_col = "" THEN LEAVE

	/* Break apart the column name from its description. */
	PARSE VAR next_col col_name temp

	/* Increment count of columns, and save the name. */
	sql_all_colname.0 = sql_all_colname.0 + 1
	sql_all_colname.[sql_all_colname.0] = STRIP(col_name)

	/* If an INSERT operation, also save the type to display
	 * to the user (in the Insert row dialog).
	 */
	IF odbc_operation == 'INSERT' THEN sql_coltype.[sql_all_colname.0] = STRIP(temp)
END

RETURN





/*********************** sql_delete_code() ***********************
 * Called by perform_SQLcode() to delete rows from the current
 * database based upon what the user typed into the "SQL statement"
 * entry control. He may have typed something like:
 *
 * where column_x = 'value1' and column_y > 20
 *
 * The following variables must be set prior to calling this:
 * Stmt =			Statement handle set by ODBCInit().
 * ODBC_TableName =	Name of the current table.
 */

sql_delete_code: PROCEDURE EXPOSE stmt odbc_tablename

/* Get what the user typed into "SQL statement". */
guigetctlvalue("ODBC_Statement")

IF odbc_statement = "" THEN
	IF guisay('No selection specified - do you REALLY want to delete ALL rows ?', "YES") \== 'YES' THEN RETURN

/* Form the SQL command to delete the rows. */
odbc_statement = 'Delete from' odbc_tablename odbc_statement

DO
	/* Send the command and check for an error. */
	odbcexecute(odbc_statement)

	CATCH ERROR
		guisay("Delete failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")
END
RETURN






/*********************** sql_update_code() **********************
 * Called by perform_SQLcode() to update rows in the current
 * database based upon what the user typed into the "SQL statement"
 * entry control. He may have typed something like:
 *
 * set column_x = 'value1' where condition
 *
 * The following variables must be set prior to calling this:
 * Stmt =			Statement handle set by ODBCInit().
 * ODBC_TableName =	Name of the current table.
 */

sql_update_code: PROCEDURE EXPOSE stmt odbc_tablename

/* Get what the user typed into "SQL statement". */
guigetctlvalue("ODBC_Statement")

IF odbc_statement = "" THEN DO
	guisay('No selection specified. Nothing to update.', "OK|INFO")
	RETURN
END

/* Form the SQL command to update the rows. */
odbc_statement = 'Update' odbc_tablename odbc_statement

DO
	/* Send the command and check for an error. */
	odbcexecute(odbc_statement)

	CATCH ERROR
		guisay("Update failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")
END
RETURN





/******************** sql_create_table() **********************
 * Called by perform_SQLcode() to create a table with an
 * example layout.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =	The name of the ODBC data source.
 * ODBC_TableName =	Name of the current table.
 */

sql_create_table: PROCEDURE EXPOSE datasrcname odbc_tablename

/* Form the SQL command to create a table with some default fields. */

/* The table name may be different depending upon the Data Source. */
SELECT
	WHEN POS('DB2', datasrcname) \== 0 THEN DO
		sw_db = 1
		/* For DB2, we need to specify the Database handle in the command. */
		odbc_statement = 'Create table' database || '.' || odbc_tablename
	END
	WHEN POS('SQLITE', datasrcname) \== 0 THEN DO
		sw_db = 2
		odbc_statement = 'Create table' odbc_tablename
	END
	OTHERWISE DO
		guisay("Must write code to format an SQL command to create a table for" datasrcname, "OK|STOP")
		RETURN
	END
END

/* This part of the command is the same for all Data Sources. */
odbc_statement = odbc_statement '(  keycol1 smallint not null unique  ,timecol time ,datecol date ,timestampcol timestamp ,charcol char(10)'

/* The integer definition may be different depending upon the Data Source. */
SELECT sw_db
	WHEN 1 THEN
		odbc_statement = odbc_statement ',deccol decimal(10,2)'
	WHEN 2 THEN
		odbc_statement = odbc_statement ',deccol int(10,2)'
	OTHERWISE DO
		guisay("Must write code to format an integer in an SQL command for" datasrcname, "OK|STOP")
		RETURN
	END
END

odbc_statement = odbc_statement || ')'

DO
	/* Send the command to create the table and check for an error. */
	odbcexecute(odbc_statement)

	/* Tell the user what we did. */
	guisay('Table created with the definition:' || '0D0A'x || odbc_statement, "OK|INFO")

	CATCH ERROR
		guisay("Create table failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")
END
RETURN





/********************** sql_drop_table() ************************
 * Called by perform_SQLcode() to drop the currently selected
 * table.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =	The name of the ODBC data source.
 * ODBC_TableName =	Name of the table to drop.
 *
 * NOTE: User is prompted for confirmation.
 */

sql_drop_table: PROCEDURE EXPOSE datasrcname odbc_tablename

/* Format the SQL Command to drop a particular table by name. */
SELECT
	WHEN POS('DB2', datasrcname) \== 0 THEN
		odbc_statement = 'Drop table' database || '.' || odbc_tablename
	WHEN POS('SQLITE', datasrcname) \== 0 THEN 
		odbc_statement = 'Drop table' odbc_tablename
	OTHERWISE DO
		guisay("Must write code to format an SQL command to create a table for" datasrcname, "OK|STOP")
		RETURN
	END
END

IF guisay('Do you REALLY want to drop this table?', "YES") == 'YES' THEN DO

	/* Send the command and check for an error. */
	odbcexecute(odbc_statement)
	guisay('Table dropped.', "OK|INFO")

	CATCH ERROR
		guisay("DROP TABLE failed:" || '0D0A'x || CONDITION('D'), "OK|STOP")
END	
RETURN





/********************** sql_select_code() **********************
 * Called by perform_SQLcode() to Select a max of 50 rows from
 * the current table.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =		The name of the ODBC data source.
 * ODBC_TableName =		Name of the table to drop.
 * SQL_all_colname. =	A list of the column names, where SQL_all_colname.0
 *						is a count of names, and SQL_all_colname.1 to
 *						SQL_all_colname.XXX are those names.
 * SQL_selcols. =		A stem listing the INDEX of each selected column.
 */

sql_select_code: PROCEDURE EXPOSE datasrcname odbc_tablename sql_all_colname. sql_selcols.

/* Empty out the "Results" listbox. */
guiremovectltext("ODBC_Result")

/* Get what the user typed into "SQL statement". */
guigetctlvalue("ODBC_Statement")

/* SQL_selcols.0 = "" if all columns are selected. Otherwise, SQL_selcols.0 is a
 * count of how many columns are selected, and SQL_selcols.1 to SQL_selcols.XXX
 * are those selections (by INDEX)
 */
IF sql_selcols.0 == "" THEN
	selected_cols = '*'
ELSE DO
	selected_cols = ""
	/* Loop round creating the string of required columns. */
	DO i = 1 TO sql_selcols.0
		n = sql_selcols.i + 1
		selected_cols = selected_cols || ',' sql_all_colname.n
	END
	/* Throw away the first comma character (and following blank). */
	selected_cols = SUBSTR(selected_cols, 2)
END

SELECT
	WHEN POS('DB2', datasrcname) \== 0 THEN
		sql_select = "Select" selected_cols "from" database || "." || odbc_tablename
	WHEN POS('SQLITE', datasrcname) \== 0 THEN
		sql_select = "Select" selected_cols "from" odbc_tablename
	OTHERWISE DO
		guisay("Must write code to format an SQL command to select rows for" datasrcname, "OK|STOP")
		RETURN
	END
END

/* If he typed something into the statement box, append it to the command. */
IF odbc_statement \= "" THEN sql_select = sql_select odbc_statement

rows = 0

/* NOTE: We CATCH ERROR which may be raised by OdbcExecute or OdbcFetch. */
DO
	/* Execute the command, so we can subsequently fetch the rows. This is (sort of)
	 * the equivalent of opening a cursor in normal DB2 terms.
	 */
	odbcexecute(sql_select, "columns")

	/* Get the display width for each column. This is just for the purpose of
	 * displaying the results to the enduser in an appealing, tabular layout.
	 */
	DO i = 1 TO columns
		odbccolattribute("sql_sizes." || i, i, "DISPLAY")
		sql_sizes.i = sql_sizes.i + 2
	END

	/* Create a string containing the column names, each padded out with
	 * spaces to the width of its widest data row. This string becomes the
	 * heading of our layout.
	 */
	selected_cols = ""
	IF sql_selcols.0 == "" THEN DO i = 1 TO columns
		IF LENGTH(sql_all_colname.i) > sql_sizes.i THEN sql_sizes.i = LENGTH(sql_all_colname.i) + 2
		selected_cols = selected_cols CENTER(sql_all_colname.i, sql_sizes.i)
	END
	ELSE DO i = 1 TO sql_selcols.0
		n = sql_selcols.i + 1
		IF LENGTH(sql_all_colname.n) > sql_sizes.i THEN sql_sizes.i = LENGTH(sql_all_colname.n) + 2
		selected_cols = selected_cols CENTER(sql_all_colname.n, sql_sizes.i)
	END
	guisendmsg("ODBC_Result", "ADDSTRING", , selected_cols)
	guisendmsg("ODBC_Result", "ADDSTRING", , COPIES("=", LENGTH(selected_cols)))

	/* Loop around getting all rows (upto a maximum of 50). */

	/* Fetch the next row (line) of results. */
	DO WHILE odbcfetch("data", columns) \== "DONE" & rows < 51

		/* Increment row count. */
		rows = rows + 1

		/* Create a string of all the data items in this row, and add to
		 * the listbox.
		 */
		selected_cols = ""
		DO i = 1 TO columns
			selected_cols = selected_cols CENTER(STRIP(data.i), sql_sizes.i)
			guisendmsg("ODBC_Result", "ADDSTRING", , selected_cols)
		END
	END

	/**********************************************************************
	 * ERROR handling
	 *********************************************************************/
	CATCH ERROR
		guisay("SELECT failed:" || "0D0A"x || CONDITION('D'), "OK|STOP")
		rows = 0
END

odbcfreestmt()

/* Check that we managed to find the table. */
IF rows == 1 THEN guisay('No rows found in table.', "OK|INFO")

RETURN




/******************** sql_show_tables() *********************
 * Sends an SQL command to get the list of table names, and
 * displays them in the Main window's "SQL tables" listbox.
 *
 * The following variables must be set prior to calling this:
 * DataSrcName =		The name of the ODBC data source.
 *
 * NOTE: connect_to_database() must have been called already.
 */ 

sql_show_tables: PROCEDURE EXPOSE datasrcname

/* Create the SQL command to ask for the table names. The exact format
 * of this command depends upon the Data Source.
 */
SELECT
	WHEN POS('DB2', datasrcname) \== 0 THEN DO
		sw_db = 1
		sql_select = "Select name from sysibm.systables where creator = '" || TRANSLATE(USERID()),
					 || "' and type = 'T' order by name asc"
	END
	WHEN POS('SQLITE', datasrcname) \== 0 THEN DO
		sw_db = 2
		sql_select = "Select sql from sqlite_master where type = 'table' and sql like 'CREATE TABLE %' "
	END
	OTHERWISE DO
		guisay("Must write code to format an SQL command to query table names for" datasrcname, "OK|STOP")
		RETURN
	END
END

/* NOTE: We CATCH ERROR which may be raised by OdbcExecute or OdbcFetch. */
DO
	/* Perform the command. */
	odbcexecute(sql_select, "columns")

	/* Empty out the "SQL tables" listbox. */
	guiremovectltext("ODBC_TableName")

	/* Read the results (ie, table names returned by the database software) until no more. */

	/* Get the next table name ("fetch cursor"). */
	DO WHILE odbcfetch("data", columns) \== "DONE"

		table_name = ""
		SELECT sw_db
			WHEN 1 THEN
				/* If it contains an underscore, then it's not a REAL table,
				 * so don't add it to the listbox.
				 */
				IF POS('_', data.1) == 0 THEN table_name = data.1
			WHEN 2 THEN DO
				PARSE VAR data.1 . 'TABLE ' table_name '(' .
				table_name = STRIP(table_name)
			END
			OTHERWISE DO
				guisay("Must write code to extract the table name for" datasrcname, "OK|STOP")
				RETURN
			END
		END

		/* Add it to the "SQL tables" listbox. */
		IF table_name \= "" THEN guisendmsg("ODBC_TableName", "ADDSTRING", , table_name)
	END

	/* Select the first item in the list */
	guisendmsg("ODBC_TableName", "SETCURSEL", 0 /* The first item starts at 0, the second item is 1, etc */)

	/**********************************************************************
	 * ERROR handling
	 *********************************************************************/
	CATCH ERROR
		guisay("Show tables failed:" || "0D0A"x || CONDITION('D'), "OK|STOP")
END

/* End the transaction */
odbcfreestmt()

RETURN





/********************* connect_to_database() *******************
 * Connects to the ODBC database.
 *
 * database = connect_to_database()
 * 
 * The following variables must be set prior to calling this:
 * Environment =		The environment handle set by ODBCInit().
 * DataSrcName =		The name of the ODBC data source.
 * Stmt =				Statement handle set by ODBCInit().
 *
 * RETURNS: The database handle if success, or 0 if an error. Also
 * sets "OBDC_DatabaseName" to the database name entered by the user
 * for Data sources that need to reference it (such as SQLite).
 *
 * If an error, this posts a message.
 *
 * NOTE: choose_datasource() must have been called already.
 */

connect_to_database: PROCEDURE EXPOSE guiwindow stmt datasrcname obdc_databasename
/* All our supported Data Sources require the User ID for the
 * connection string, so let's get that now.
 */
user = USERID()

/* Form the connection string. The contents of this string depends
 * upon which Data Source we're using.
 */
IF POS('SQLITE', datasrcname) \== 0 THEN DO
	/* For SQLite, the connection string includes the database name,
	 * so get what the user entered into the "Database" entry.
	 */
	guigetctlvalue("ODBC_DatabaseName")
	IF odbc_databasename == "" THEN DO
		guisay("Enter a database name first.")
		RETURN 0
	END
	odbc_databasename = TRANSLATE(STRIP(odbc_databasename))

	/* Append the extension .db if he didn't specify it on the database name. */
	IF FILESPEC('E', odbc_databasename) \== '.DB' THEN odbc_databasename = odbc_databasename || '.DB'

	full = "DSN=" || datasrcname || ";Database=" || odbc_databasename ";UID=" || user || ";"
END	
ELSE
	/* For DB2, we simply need the Data Source name, and user ID. */
	full = "DSN=" || datasrcname || ";UID=" || user || ";"

DO
	/* Open/connect to that Data Source. Pass our connection string.
	 * OdbcAllocDatabase() will present a dialog if it needs to get extra
	 * information from the user such as his password. "full" will be set
	 * to the full connection string. A statement handle will be gotten
	 * and put in "Stmt". The database handle is put in "database".
	 */
	odbcallocdatabase("database", "full", "Stmt", guiwindow)

	/**********************************************************************
	 * ERROR handling
	 *********************************************************************/
	CATCH ERROR

		/* If the error message specifies a macro DLL that had an error, let's
		 * strip off that part of the error message. We don't need to know
		 * which ODBC helper function had the error.
		 */
		err = CONDITION('D')

		/* Let's see if the userid or password specified was invalid 
		 * Of course, these texts MIGHT change over time, but...
		 * (In reality, these are probably relevant only when
		 * accessing mainframe DB2).
		 */
		SELECT
			WHEN POS("PASSWORD INVALID",err) \== 0 THEN
				guisay("Invalid password. Try again", 'OK|STOP')
			WHEN POS("USERID INVALID",err) \== 0 THEN
				guisay("Invalid user id. Try again", 'OK|STOP')
			OTHERWISE	
				guisay("Connect to database failed:" || '0D0A'x || err, 'OK|STOP')
		END

		/* Failure */
		RETURN 0
END

/* Success */
RETURN database





/********************* choose_datasource() ********************
 * Checks whether the "defaultDatabase" is the name of an data
 * source installed upon this computer. If not, a dialog is
 * presented to let the user choose the desired datasource.
 *
 * The following variables must be set prior to calling this:
 * Environment =		The environment handle set by ODBCInit().
 * DefaultDatabase =	The name of the default database to connect to.
 *
 * RETURNS: The chosen Data Source name if success, or an empty
 * string if an error.
 *
 * If an error, this posts a message.
 */

choose_datasource: PROCEDURE EXPOSE environment defaultdatabase
/* Allow the user to choose his data source (if the default
 * one isn't already installed on this system).
 */
createobject("ODBC_PickSource.rex", , , defaultdatabase)

choose_datasource_again:
/* Do an event loop until ODBC_PickSource.rex calls GuiWake(). NOTE:
 * If the user closes the "Choose Database" window, this implicitly
 * calls GuiWake with GuiObject == 'ODBC_PICKSOURCE' and DROPs GuiSignal.
 */
DO UNTIL guiobject == 'ODBC_PICKSOURCE'

	/* Wait for some event to happen. */
	guigetmsg()

	/* Did our "Choose Database" window call GuiWake()? */
	IF guiobject == 'ODBC_PICKSOURCE' THEN DO

		/* Is it telling us no Data sources are installed? */
		IF guisignal == "" THEN SIGNAL choose_datasource_bad
		
		/* If GuiSignal is DROP'ed, then the user clicked the close box, or the
		 * cancel button. Otherwise, it must be the Ok button so we return the data
		 * source name.
		 */
		IF EXISTS("GuiSignal") THEN DO
		
			datasrcname = TRANSLATE(guisignal)

			/* We don't need the object anymore. */
			DROP odbc_picksource

			/* Display the chosen source. */
			guiaddctltext("ODBC_driver", datasrcname)

			/* Return it. */
			RETURN datasrcname
			
		END
	END

	CATCH SYNTAX
		IF guiinfo() \== "" THEN DO /* Don't display error if all windows are closed. Just end the loop. */
			CONDITION('M')
			SIGNAL choose_datasource_again
		END

END /* UNTIL */

choose_datasource_bad:

/* We don't need the object anymore. */
DROP odbc_picksource

/* Error. */
RETURN ""





/******************** WM_CLICK_ODBC_Browse() ********************
 * Called by Reginald when the "ODBC_Browse" button is clicked.
 * Fills the "ODBC_TableName" combo box with the names of all tables
 * in the current database.
 */
 
wm_click_odbc_browse:
	/* Make sure we're connected to the database. */
	IF database == 0 THEN DO
		database = connect_to_database()
		IF database = 0 THEN RETURN
	END

	/* Get the table names, and fill the "SQL Table" combo box with them. */
	sql_show_tables()
			
	RETURN





/******************* WM_CLICK_ODBC_Cancel() *******************
 * Called by Reginald when the "ODBC_Cancel" button is clicked.
 */

wm_click_odbc_cancel:
	/* Send a message to CLOSE (and destroy) the window. */
	guisendmsg(guiwindow, "CLOSE")

	RETURN





/********************** WM_CLICK_ODBC_Ok() *********************
 * Called by Reginald when the "ODBC_Ok" button is clicked.
 */

wm_click_odbc_ok:
	/* Make sure we're connected to the database. */
	IF database == 0 THEN DO
		database = connect_to_database()
		IF database = 0 THEN RETURN
	END

	/* See if the "Operation" combo box is enabled. If not, then he
	 * must have just entered the Database name, and clicked the
	 * ok button. We've just successfully connected to the database
	 * above, so let's enable everything now.
	 */
	guigetctlplacement("ODBC_Operation",,,,,'test')
	IF test == "DISABLE" THEN DO
		guisetctlplacement("ODBC_Operation",,,,,"ENABLE")
		guisetctlplacement("ODBC_TableName",,,,,"ENABLE")
		guisetctlplacement("ODBC_Statement",,,,,"ENABLE")
		guisetctlplacement("ODBC_Browse",,,,,"ENABLE")
		wm_select_odbc_operation()
	END
	
	/* Perform the SQL command. */
	ELSE perform_sqlcode()
		
	RETURN





/****************** WM_SELECT_ODBC_Operation() ******************
 * Called by Reginald when the user selects a choice in the
 * "Operation" combo box.
 */

wm_select_odbc_operation:
	/* Get what the user chose. */
	guigetctlvalue("ODBC_Operation")
	UPPER odbc_operation

	/* For these operations, we must operate upon all columns, so check that the
	 * user hasn't chosen "Selected" columns.
	 */
	IF odbc_operation == 'DELETE' | odbc_operation == 'UPDATE' | odbc_operation == 'DROP TABLE' | odbc_operation == 'CREATE TABLE' THEN DO

		/* Set the "Columns" radio buttons to "All". Then disable the radio buttons so he can't select them. */
		odbc_allcols = 1
		guisetctlvalue("ODBC_AllCols")

		guisetctlplacement("ODBC_AllCols", , , , , "DISABLE")
		guisetctlplacement("ODBC_SelCols", , , , , "DISABLE")
	END

	/* For other operations, enable the buttons so he can select them. */
	ELSE DO
		guisetctlplacement("ODBC_AllCols", , , , , "ENABLE")
		guisetctlplacement("ODBC_SelCols", , , , , "ENABLE")
	END

	/* For some operations, the "SQL Statement" isn't used, so disable/enable it. */
	IF odbc_operation == 'DELETE' | odbc_operation == 'UPDATE' | odbc_operation == 'SELECT' THEN
		guisetctlplacement("ODBC_Statement", , , , , "ENABLE")
	ELSE
		guisetctlplacement("ODBC_Statement", , , , , "DISABLE")

	RETURN

2010-07-16 20:49