DATABASE ACCESS

UnForm supports access to databases from rule set code.  To use this method, you connect to a data source identified with a string construction, optionally supplying a user and password login, as well as other optional arguments.  The sqlconnect() function provides the functionality, and returns a connection channel number.

Note that secure passwords can be configured in the browser interface and referenced in the sqlconnect() function, using the syntax "store:ID" rather than a plain text password.

After connecting, send SQL commands to the database channel using the sqlexecute() function.  Access the data returned by the command, if any, using the sqlfetch() function, which can return one, many, or all rows from the query, in a delimited string.

When done with the data source, you can close the channel with the close(chan) command.

The syntax of the three functions is:

chan=sqlconnect(datasource$[,user$ ,pswd$ [,otheroptions$ [,errmsg$]]])

[success=]sqlexecute(chan,command$[,errmsg$[,result$[,fdelim$[,rdelim$]]]])

count=sqlfetch(chan,result$[,count [,errmsg$ [,fdelim$ [,rdelim$]]]])

There are four types of databases supported, though not all types are supported on all platforms.  The "uf110c -v" command shows which database types are supported.  The four types are ODBC, Oracle, DB2, and MySQL.  Note that ODBC is supported on Unix/Linux, as well as Windows, if either the unixODBC or iODBC package is installed.

The syntax of the datasource$ argument identifies the database type and data source:

•odbc:dsn connects to the ODBC data source name (also called the DSN), as configured in the Windows ODBC administrator or in the unixODBC/iODBC configuration.

•oracle:sid connects to the Oracle System ID, using local Oracle client libraries.

•db2:database connects to the DB2 database specified.

•mysql:database[:hostname] connects to the MySQL database named, optionally on the host specified.

Most databases require a login and password in order to access a database.  The user and password must be supplied in those cases.

Additional options that can be supplied in the otheroptions$ argument, as a semicolon-delimited list.  Options include:

•access=read|write

•strip  (if present, causes trailing spaces to be trimmed from fields)

•textmax=val (sets the maximum amount of text returned from a text field, default=4096)

•timeout=seconds

Once a connection channel has been created, you can then send SQL commands to the channel using the sqlexecute() function.  That function can optionally fill a results variable with all the rows returned by the query, or you can use the sqlfetch() function to return rows one or many at a time.

Below is a simple example showing how to use the three functions:

prejob{

chan=sqlconnect("odbc:sampdb","userid","password")

if chan>0 then:

 e=sqlexecute(chan,"select member_id, last_name, first_name from member")

 if e>0 then:

         while sqlfetch(chan,row$)

                 row$=sub(row$,$09$,"|")

                 allrows$+=row$+$0a$

         wend

 end if

 close(chan)

end if

}

text 10,2,{allrows$}