/* An example script that FUNCDEFs the functions in the
 * SQL Lite DLL (ie, SQLITE.DLL) and calls them to
 * open and send SQL statements to an SQL database.
 *
 * This example queries the columns/values in a TABLE
 * called "tbl1" in a database named "mydatabase.db"
 * in the current directory.
 *
 * This requires the SQLITE.DLL library file available
 * at www.sqlite.org
 */

OPTIONS "WINFUNC NOSOURCE C_CALL"

/* Some DLL functions we FUNCDEF need to return addresses that may have upto 10 digits */
NUMERIC DIGITS 10

/* ============ FUNCDEF the functions in the SQL Lite DLL ============ */
DO
	FUNCDEF("STRPTR", "str *")
	FUNCDEF("SqlOpen", "void, str, 32, void", "sqlite", "sqlite_open")
	FUNCDEF("SqlClose", ", void", "sqlite", "sqlite_close")
	FUNCDEF("SqlError", "str, 32", "sqlite", "sqlite_error_string")
	FUNCDEF("SqlVersion", "str", "sqlite", "sqlite_libversion")
	FUNCDEF("SqlEncoding", "str", "sqlite", "sqlite_libencoding")
	FUNCDEF("SqlCompile", "32, void, str, void stor, void stor, void stor", "sqlite", "sqlite_compile")
	FUNCDEF("SqlFreeMem", ",void", "sqlite", "sqlite_freemem")
	FUNCDEF("SqlStep", "32, void, 32u stor, void stor, void stor", "sqlite", "sqlite_step")
	FUNCDEF("SqlFinalize", "32, void, void stor", "sqlite", "sqlite_finalize")
	FUNCDEF("SqlInterrupt", ",void", "sqlite", "sqlite_interrupt")
	FUNCDEF("SqlComplete", "32u,str", "sqlite", "sqlite_complete")
	/*
	 FUNCDEF("SqlLastInsertRowID", "32u,void", "sqlite", "sqlite_last_insert_rowid")
	*/
	/*
	SQLPROGRESS = "32,void"
	FUNCDEF("SqlProgressHandler", ",void,32u,func SQLPROGRESS,void", "sqlite", "sqlite_progress_handler")
	*/
	CATCH FAILURE
		CONDITION("M")
		RETURN
END







/* ================== An example of using the SQL Lite functions ================= */

/* Display some info about the SQL DLL */
SAY "SQL Version =" SqlVersion()
SAY "SQL Encoding =" SqlEncoding()

/* Open the database named "mydatabase.db" (in the current directory) or
 * create it if it doesn't yet exist
 */
SqlMsg = 0
sql = SqlOpen("mydatabase.db", 0, SqlMsg)

/* Check for an error */
IF sql == 0 THEN DO

   IF SqlMsg \== 0 THEN DO

      /* Convert the error message to something we can access in REXX */
	  CONVERTDATA(SqlMsg, 'MSG', 'str *')
	  SAY msg

	  /* Sql lite wants us to free the string it returns */
	  SqlFreeMem(SqlMsg)
   END

   ELSE SAY "Error opening mydatabase.db"
   RETURN
END

/*
 * Tell the SQL Lite DLL to call our progress callback (which we'll name
 * SqlProgress) periodically while SqlStep() is doing some lengthy operation.
 * This is optional. You don't need to have this callback.
 *
 * The first argument is what was returned by SqlOpen().
 *
 * The second argument is the frequency at which we want to be called. Smaller
 * numbers will allow our callback to be called more often, but will slow down
 * the execution of SQL statements.
 *
 * The third argument is the name of our REXX function we want SqlStep() to call.
 * Here we specify a function named "SqlProgress".
 *
 * If you wish, you can pass a fourth arg which will then be passed to your
 * callback. Or you can omit it.
 */
/*
SqlProgressHandler(sql, 10, SqlProgress)
*/

/* Execute an SQL statement and get results back */
err = SqlExecuteString(sql, "select * from tbl1;", results., columnNames., dataTypes., columns, rows)
IF err \= "" THEN SAY err

/* Say the results */
ELSE DO
   /* Do all the rows */
   DO i = 1 TO rows
   
	  /* Do all the columns of the next row */
	  DO p = 1 TO columns

		 /* Does this column have a value? */
		 IF SYMBOL('results.' || i || '.' || p) == 'VAR' THEN CHAROUT(, columnNames.i.p '=' results.i.p '(' || dataTypes.i.p || ')')
		 ELSE CHAROUT(, columnNames.i.p "has no value.")

		 /* If this is the last column of the row, then end the line. Otherwise, just display a comma */
		 IF p = columns THEN SAY
		 ELSE CHAROUT(, ", ")

	  END /* columns */
   END /* rows */
END

/* Here you could execute more SQL statements. */

/* Close the database now that we're done. */
SqlClose(sql)
DROP sql

/* All Done! */
RETURN





/* =========================== SqlExecuteString() ==========================
 * Executes an SQL string, and returns the results if any.
 *
 * err = SqlExecuteString(Database, SqlString, ResultStem, ColNameStem, DataTypeStem, Columns, Rows)
 *
 * Database =  The database handle returned by SqlOpen().
 *
 * SqlString = The SQL statement string to execute. For example "select * from tbl1;".
 *		This can be passed as a literal string, or a variable's value.
 *
 * ResultStem = The name of the variable where you want the results stored.
 *		Do not quote it. Omit it if you do not desire results stored.
 *
 * ColNameStem = The name of the variable where you want the column names stored.
 *		Do not quote it. Omit it if you do not desire names stored.
 *
 * DataTypeStem = The name of the variable where you want the datatype stored.
 *		Do not quote it. Omit it if you do not desire datatype stored.
 *
 * Columns = The name of the variable where you want the number of columns (in
 *			each row) stored. Do not quote it. Omit it if you do not desire number of columns.
 *
 * Rows = The name of the variable where you want the total number of rows stored.
 *		Do not quote it. Omit it if you do not desire number of rows.
 *
 * RETURNS: An empty string if success, or an error message.
 *
 * NOTES: SqlOpen() must have been called.
 */

SqlExecuteString: PROCEDURE

USE ARG Sql, String, Results., ColumnNames., DataTypes., Columns, Rows

/* Initially no rows */
Rows = 0

/* Compile the SQL statement into a form that can be passed to SqlStep to execute
 * it. Note: You can check if an SQL statement string is a complete statement by
 * passing your string to SqlComplete() and seeing if you get back a 1.
 *
 * The first argument is what was returned by SqlOpen().
 *
 * The second argument is the SQL statement to execute. See the SQL syntax
 * for the format of SQL statements. We can either pass a literal string directly,
 * or supply the name of a variable whose value is the desired SQL statement.
 * Here we pass a literal string.
 *
 * The third argument is the name of our REXX variable where SqlCompile() will
 * store a pointer to the end of the compiled SQL statement. Here we specify a
 * variable named "SqlEnd".
 *
 * The fourth argument is the name of our REXX variable where SqlCompile() will
 * store a handle to its "virtual machine". Here we specify a variable named
 * "SqlMachine".
 *
 * The fifth argument is the name of our REXX variable where SqlCompile() will
 * store a handle to any error message that SqlCompile() generates if there is an
 * error. Here we specify a variable we've named "SqlMsg".
 *
 * SqlCompile() returns 0 if success. Otherwise, it returns a non-zero error number.
 */
sqlMsg = 0
msg = "Error compiling the SQL statement"
err = SqlCompile(Sql, String, Columns, sqlMachine, sqlMsg)
IF err \== 0 THEN SIGNAL SqlExecuteStringError

/* If a non-GUI app, we should trap HALT before we begin calling SqlStep(),
 * and make sure our handler returns something other than 0. For a GUI
 * app, we likely don't need this. If we have no progress handler, we should
 * omit this
 */
/*    
   SIGNAL ON HALT NAME AbortSql
*/

   /* Execute the SQL statement.
    *
    * The first argument is the virtual machine returned by SqlCompile().
    *
    * The second argument is the name of our REXX function where SqlStep() will store
    * a count of the number of columns in the result (of executing our SQL statement).
    * Here we specify a variable named "SqlCount".
    *
    * The third argument is the name of our REXX function where SqlStep() will store
    * a handle to the column data. Here we specify a variable named "SqlColData".
    *
    * The fourth argument is the name of our REXX function where SqlStep() will store
    * a handle to the column names and datatypes. Here we specify a variable named
    * "SqlColNames".
    *
    * SqlStep() will return one of the following the numeric values:
    * 101 (SQLITE_DONE)
    *		SqlStep() has successfully finished executing the SQL statement
    *		without error. SqlStep() should not be called again for the same virtual
    *		machine.
    *
    * 100 (SQLITE_ROW)
    *		A single row of the result has been returned. The data is stored in
    *		SqlColData. SqlCount is set to the number of columns in the result
    *		set and SqlColNames is set to an array of strings that describe the
    *		column names and datatypes.
    *
    * 1 (SQLITE_ERROR)
    *		The virtual machine encountered a run-time error. SqlStep() should not be
    *		called again for the same virtual machine. Call SqlFinalize() to obtain the
    *		specific error code and the error message text for the error.
    *
    * 5 (SQLITE_BUSY)
    *		An attempt to open the database failed because another thread or process
    *		is holding a lock. You can try to open the database by calling SqlStep()
    *		again. SqlStep() returns 5 only if you haven't registered a busy callback
    *		using SqlBusyHandler() or SqlBusyTimeout(). If you have registered a busy
    *		callback, and it returns 0, then SqlStep() will return 1 and SqlFinalize()
    *		will return 5 when it is called.
    *
    * 21 (SQLITE_MISUSE)
    *		SqlStep() is called incorrectly. For example, if you call SqlStep() after
    *		a prior call to SqlStep() has returned 101.
    */
DO UNTIL err \== 100	/* Keep going until we have an error or no more rows */

   err = SqlStep(sqlMachine, Columns, sqlColData, sqlColNames)

   /* Did we get another row of results? */
   IF err == '100' THEN DO

	  /* Increment Rows */
	  Rows = Rows + 1

	  /* We need to get an offset for CONVERTDATA for the DataTypes */
	  sqlColDataType = sqlColNames + (4 * Columns)

	  /* Store each column's value, with its column name, and datatype */
	  i = 1
	  DO Columns

		 /* We need to use CONVERTDATA to convert the C pointer (to
		  * a string) into an actual REXX string we can access.
		  */
		 IF CONVERTDATA(sqlColData, 'ANSWER') \== "" & ,
			CONVERTDATA(sqlColDataType, 'TYPE', 'struct STRPTR') \== "" & ,
			CONVERTDATA(sqlColNames, 'HEADING', 'struct STRPTR') \== "" THEN DO

			/* Save the Column name and datatype */
			ColumnNames.Rows.i = heading.1
			DataTypes.Rows.i = type.1
 
			/* Now let's check if the result was a "null pointer". If so, this
    		 * means that there is no value for this column. We'll drop the
    		 * appropriate Results variable
			 */
			IF answer == 0 THEN DROP Results.Rows.i

			/* We got a result. Convert and store it */
			ELSE DO
			   IF CONVERTDATA(answer, 'ANSWER', 'str') == "" THEN DO
SqlExecuteStringError2:
 				  SqlFinalize(sqlMachine, sqlMsg)
				  RETURN "Error converting data"
			   END
			   Results.Rows.i = answer
			END

			/* Increment column */
			i = i + i

			/* Move to the next result, column, and datatype. Note: On a 32-bit machine
			 * a pointer is 4 bytes long. This example is for a 32-bit version
			 * of Reginald. A 64-bit version would need 8 bytes.
			 */
			sqlColDataType = sqlColDataType + 4
			sqlColNames = sqlColNames + 4
			sqlColData = sqlColData + 4
   		 END
		 ELSE SIGNAL SqlExecuteStringError2
	  END /* DO Columns */
   END /* 100 */
END /* UNTIL err \== 100 */

/* Delete the virtual machine now that it has finished executing the SQL
 * statement.
 *
 * The first argument is the virtual machine returned by SqlCompile().
 *
 * The second argument is the name of our REXX variable where SqlFinalize() will
 * store a handle to any error message that may have been generated if there was
 * an error with SqlStep(). Here we specify a variable we've named "SqlMsg".
 *
 * SqlFinalize() returns 0 if success. Otherwise, it returns a non-zero error
 * number, and fills in SqlMsg.
 *
 * SqlFinalize can be called at any point during the execution of the
 * SQL statement. If the virtual machine has not completed execution
 * when this function is called, that is like encountering an error or
 * an interrupt. (See SqlInterrupt()). Incomplete updates may be rolled back
 * and transactions cancelled, depending on the circumstances, and SqlFinalize
 * will return 4 (SQLITE_ABORT).
*/
sqlMsg = 0
err = SqlFinalize(sqlMachine, sqlMsg)
msg = "Error in SqlFinalize()"
SqlExecuteStringError:
IF err \== 0 THEN DO
   IF sqlMsg \== 0 THEN DO
	  CONVERTDATA(sqlMsg, 'MSG', 'str *')
	  SqlFreeMem(sqlMsg)
   END
   RETURN msg
END

/* Success */
RETURN ""




/* ====================== Our SQL Progress callback ======================
 * This is our progress callback. SqlStep() calls this periodically while it
 * is bogged down in operations. We can use this to abort SqlStep() and have
 * it roll back any database changes and return a 4 (ABORT). We do that by
 * returning anything other than 0. If we return 0, then SqlStep() continues
 * normally.
 */

SqlProgress:

   /* It's important we trap SYNTAX and HALT here, so
    * that our script doesn't abort before we can close
    * the database.
    */
   SIGNAL ON SYNTAX NAME AbortSql
   SIGNAL ON HALT NAME AbortSql

   /* Note: For a GUI app we would now check if there were any messages
    * waiting at our window, and see if the user did something to indicate
    * he wants to quit the transaction. For example, with REXX Dialog
    * we would probably do a:
    *
    * CALL RXMSG ,'PROCESS'
    *
    * And then follow it with an RXQUERY on some button he could click
    * to abort. If he clicked that button, we would return anything but
    * 0.
    *
    * For a non-GUI app, if the guy has pressed CTRL-C, then the HALT
    * condition has already been raised when we get here. We would
    * therefore call whatever handler we set for HALT before we
    * called SqlStep().
    */
 
	/* We return 0 to let SqlStep() continue. */
	RETURN 0

	/* Cause SqlStep() to abort, and return a non-zero number. */
AbortSql:
	RETURN -1



FINALLY
	/* Make sure the database is closed */
	IF EXISTS('sql') THEN SqlClose(sql)