2010-07-16 20:49

/*
GUIBEGIN


WINDOW , 85, 229, 373, 140, POPUP|CAPTION|SYSMENU|MINBOX|MAXBOX, , ODBC Insert
	FONT 8, 400, MS Shell Dlg
	TEXT 3, 3, 23, 8, RIGHT|GROUP, , , , &Fields:
	LIST 0, 14, 318, 107, NOTIFY|MULTI|BORDER|VSCROLL|HSCROLL|TABSTOP, , ODBC_Fields, ALT "F"
	TEXT 0, 122, 26, 8, RIGHT|GROUP, , , , &Values:
	ENTRY 27, 118, 290, 16, H_AUTO|BORDER|TABSTOP, CLIENTEDGE, ODBC_Values, ALT "V"
	PUSH 327, 16, 40, 14, TABSTOP, , ODBC_Insert, ALT "I", &Insert
	PUSH 327, 37, 40, 14, TABSTOP, , Cancel, ALT "C", &Cancel
DEND
GUIEND
*/

OPTIONS "NOSOURCE"

/************************ ODBC_GetInsertCmd.rex *************************
 * This is a REXX GUI child (object) script that presents a (modal)
 * window containing a listbox that we fill with the column names of the
 * current table. The user can select as many columns as he desires. We
 * also have an entry control into which he can type the values for each
 * of the selected columns. Finally, there is an Ok button he presses to
 * have this script create the SQL command string needed to insert that
 * row of data into the table. We call GuiWake() and pass that SQL command
 * string as the "GuiSignal". If the user instead clicks the Cancel button
 * or closes the window, we DROP GuiSignal. If there's an ODBC error, the
 * ERROR condition is raised, and for other errors (such as REXX GUI) then
 * SYNTAX is raised. (NOTE: We assume that caller is trapping SYNTAX for
 * REXX GUI errors, and ERROR for ODBC errors, and has initialized the
 * ODBC add-on functions).
 *
 * Note that, for any row defined as date, time or timestamp, the user can
 * omit typing in the value for that row, in which case this script will
 * automatically fill in an appropriate default value.
 *
 * Before calling this script, the following variables must be set:
 * DataSrcName =		The name of the Data Source.
 * ODBC_TableName =		The name of the current table.
 * SQL_all_colname. =	A stem variable set to the column names of the current
 *						table, where SQL_all_colname.0 is a count of columns,
 *						and SQL_all_colname.1 to SQL_all_colname.XXX are the names.
 * SQL_coltype. =		A stem variable containing the datatypes for the above
 *						columns.
 * SQL_length. =		Lengths for the above columns.
 * SQL_scale. =			Scales for the above columns.
 *
 * Author: Michael Simpson Copyright March 2005
 */


/* ========================== Create ==========================
 * Called when the main script creates an object that uses this
 * Window Layout script. (ie, The main script is doing a CreateObject
 * call where the first arg is the name of this Window Layout script).
 *
 * NOTE: We EXPOSE a bunch of variables that the creator should have
 * set so all our subroutines can access them.
 */
create: PROCEDURE EXPOSE datasrcname odbc_tablename sql_all_colname. sql_coltype. sql_length. sql_scale.
	guierr = "SYNTAX"
	guiheading = 1

	/* Create the window modal, but don't show it yet. We need to fill in
	 * the ODBC_Fields listbox first.
	 */
	guicreatewindow(, -1)

	/* Fill in the ODBC_Fields listbox with the column names. */
	display_column_names()

	/* Now show the window. */
	guisetctlplacement(guiwindow,,,,,,'NORMAL')

	/* Return to our creator and let him do the (modal) message loop. */
	RETURN





/* Called when our creator DROPs us. */
destroy:
	guidestroywindow()
	RETURN





/********************* display_column_names() **********************
 * Fills the "Fields" listbox with each column name (and its
 * data type). How we format this depends upon the data source.
 */
display_column_names:
	IF POS('DB2', datasrcname) \== 0 THEN SIGNAL display_db2_names
	IF POS('SQLITE', datasrcname) \== 0 THEN SIGNAL display_sqlite_names
	RAISE SYNTAX 48 DESCRIPTION ("You must add code to extract column names and datatypes for" datasrcname)





/********************* display_db2_names() **********************
 * Fills the "Fields" listbox with each column name (and its
 * data type) for DB2. For decimal columns, we add the length and
 * scale values.
 */
display_db2_names:
	DO i = 1 TO sql_all_colname.0
		string = STRIP(sql_all_colname.i) '(' || STRIP(sql_coltype.i)
		IF sql_coltype.i == 'DECIMAL' THEN
			string = string sql_length.i || ',' || sql_scale.i || ')'
		ELSE
			string = string || ')'
		guisendmsg('ODBC_Fields', 'ADDSTRING',, string)
	END
	RETURN





/******************** display_sqlite_names() *******************
 * Fills the "Fields" listbox with each column name (and its
 * data type) for SQLite. We simply append the column type to
 * the column name.
 */
display_sqlite_names:
	DO i = 1 TO sql_all_colname.0
		guisendmsg('ODBC_Fields', 'ADDSTRING',, STRIP(sql_all_colname.i) '(' || STRIP(sql_coltype.i) || ')')
	END
	RETURN




/* Called by Reginald when our Cancel button is clicked. */
wm_click_cancel:
	/* Mimic the user clicking on the close box. */
	guisendmsg(, "POST CLOSE")

	RETURN





/* Called by Reginald when our Insert button is clicked. */
wm_click_odbc_insert:
	/* Grab the values of all controls in the window, and stuff
	 * them into their respective REXX variables.
	 */
	guigetctlvalue()
	
	/* Create the SQL command string to insert a row and pass it as the
	 * "signal" to GuiWake().
	 */
	SELECT 
		WHEN POS('DB2', datasrcname) \== 0 THEN
			guiwake(make_db2_insert_cmd())
		WHEN POS('SQLITE', datasrcname) \== 0 THEN
			guiwake(make_sqlite_insert_cmd())
		OTHERWISE
			RAISE SYNTAX 48 DESCRIPTION ("Need to add code to make an SQL command to insert a row for" datasrcname)
	END
	RETURN




/******************* make_db2_insert_cmd() **********************
 * Called by WM_CLICK_ODBC_Insert() to create the SQL command
 * string to insert a row for DB2.
 *
 * Returns: The command string if success, or an empty string if
 * an error.
 */
make_db2_insert_cmd:
	string = 'Insert into' database || '.' || odbc_tablename' values ('
	split_into_stem('0D0A'x, odbc_values, temp.)
	IF check_nr_rows(temp.) == 1 THEN RETURN ""
	
	DO i = 1 TO temp.0
	
		coltype = ' ' || TRANSLATE(sql_coltype.i)
		SELECT 
			WHEN POS(' TIMESTMP',coltype) \== 0 | POS('(TIMESTMP',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",current timestamp"
				ELSE
					new_value = ",'"temp.i"'"
			END
			WHEN POS(' TIME',coltype) \== 0 | POS('(TIME',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",current time"
				ELSE
					new_value = ",'"STRIP(temp.i)"'"
			END
			WHEN POS(' DATE',coltype) \== 0 | POS('(DATE',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",current date"
				ELSE
					new_value = ",'"temp.i"'"
			END
			WHEN POS(' CHAR',coltype) \== 0 | POS('(CHAR',coltype) \== 0 THEN
				new_value = ",'"STRIP(temp.i,'T')"'"
			WHEN POS(' VARCHAR',coltype) \== 0 | POS('(VARCHAR',coltype) \== 0 THEN
				new_value = ",'"STRIP(temp.i,'T')"'"
			OTHERWISE
				/* Some form of numeric value - add as is */
				new_value = ","temp.i
		END
		IF i == 1 THEN
			/* Throw away the comma for the first column */
			new_value = SUBSTR(new_value, 2)
	
		string = string||new_value				
	END
	
	/* Add the trailing closing paren. */
	string = string || ')'
	
	RETURN string




/**************** make_sqlite_insert_cmd() *******************
 * Called by WM_CLICK_ODBC_Insert() to create the SQL command
 * string to insert a row for SQLite.
 *
 * Returns: The command string if success, or an empty string if
 * an error.
 */
make_sqlite_insert_cmd:
	string = 'Insert into' odbc_tablename 'values ('
	split_into_stem('0D0A'x, odbc_values, temp.)
	IF check_nr_rows(temp.) == 1 THEN RETURN ""
	
	DO i = 1 TO temp.0
	
		coltype = ' ' || TRANSLATE(sql_coltype.i)
		SELECT 
			WHEN POS(' TIMESTAMP',coltype) \== 0 | POS('(TIMESTAMP',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",datetime('NOW')"
				ELSE
					new_value = ",'"temp.i"'"
			END
			WHEN POS(' TIME',coltype) \== 0 | POS('(TIME',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",TIME('NOW')"
				ELSE
					new_value = ",'"STRIP(temp.i)"'"
			END
			WHEN POS(' DATE',coltype) \== 0 | POS('(DATE',coltype) \== 0 THEN DO
				IF temp.i = "" THEN
					new_value = ",DATE('NOW')"
				ELSE
					new_value = ",'"temp.i"'"
			END
			WHEN POS(' CHAR',coltype) \== 0 | POS('(CHAR',coltype) \== 0 THEN
				new_value = ",'"STRIP(temp.i,'T')"'"
			WHEN POS(' VARCHAR',coltype) \== 0 | POS('(VARCHAR',coltype) \== 0 THEN
				new_value = ",'"STRIP(temp.i,'T')"'"
			OTHERWISE
				/* Some form of numeric value. Add it to the command string, as is. */
				new_value = "," || temp.i
		END
		IF i == 1 THEN
			/* Throw away the comma for the first column */
			new_value = SUBSTR(new_value, 2)
	
		string = string || new_value				
	END
	
	/* Add the trailing closing paren. */
	string = string || ')'
	
	RETURN string






/************************* check_nr_rows() **********************
 * Checks the number of rows we're received. If less than the
 * amount in SQL_all_colname.0, expand to that amount. If
 * greater, give an error message.
 *
 * Returns: 0 if success, or 1 if an error.
 */
  
check_nr_rows:
	SELECT
		WHEN temp.0 = sql_all_colname.0 THEN
			RETURN 0
		WHEN temp.0 < sql_all_colname.0 THEN DO
			y = temp.0 + 1
			DO i = y TO sql_all_colname.0
				temp.i = ""
			END
			temp.0 = sql_all_colname.0
		END
		OTHERWISE DO
			guisay('Too many rows specified for this table', "OK|STOP")
			RETURN 1
		END
	END
	
	RETURN 0

2010-07-16 20:49