To tell a database to do something, such as create a new table, or to return all of the rows that have a certain criteria, you send "SQL statements" to the database using an ODBC function. What is an SQL statement? It is just a string. But this string contains certain "commands" and ways of specifying other information. SQL is sort of like a specialized computer language that only databases understand. So you're going to be creating strings in REXX that contain SQL statements.

Note: To learn about the SQL syntax, consult some other reference.

An example of an SQL statement to create a new table could be the string:

CREATE TABLE demotable
In order to execute an SQL statement, you need a statement handle. You can use the one that OdbcAllocDatabase (or OdbcConnect) set for you. You can reuse this same handle to execute many SQL statements, one at a time. (On the other hand, if you wish to execute several simultaneously, you would need a separate statement handle for each simultaneously executing statement. You can call OdbcAllocStmt to get a statement handle, and/or set it as the "current statement". The first arg is the name of a variable where you want the statement handle stored).

You can call the ODBC function OdbcExecute to send an SQL statement to the database. You simply pass your SQL string.

/* Execute an SQL statement to create a table named "demotable" */
statement = "CREATE TABLE demotable"
OdbcExecute(statement)
OdbcExecute not only sends your SQL statement to the database, it can also fetch some information about the returned columns for the results. The information it returns is each of the column names for the results, each column's datatype, and other attributes of each column (for example, if it is a numeric type, what precision it allows -- ie, the amount of decimal places it accepts).

The first arg is your SQL statement.

The second arg is the name of the variable where you want OdbcExecute to return the number of columns in any results from the SQL statement. Omit this arg if you do not wish to know the number of columns in the results.

The third arg is the name of the stem variable where you want OdbcExecute to return information about each column of the results. If you do not wish any column information, you can omit this arg.

Here is an example of selecting a table named demotable, and listing information about each column.

DO

   /* Send an SQL statement to select all items in that table. */
   OdbcExecute("SELECT * from demotable;", "columns", "info.")

   /* Display info about the results */
   DO i = 1 TO columns
      CHAROUT(, "Column" i "- Name:" info.!Name.i)
      CHAROUT(, " DataType:" info.!TypeStr.i "(" || info.!TypeNum.i || ")")
      CHAROUT(, " RightDigits:" info.!Size.i)
      CHAROUT(, " LeftDigits:" info.!DecDigits.i)
      SELECT info.!Nullav.i
         WHEN 0 THEN err = "Yes"
         WHEN 1 THEN err = "No"
         OTHERWISE err = "?"
      END
      SAY " Accepts null value:" err
   END

   CATCH ERROR
      CONDITION("M")
      RETURN
END