DATABASE ACCESS

UnForm supports access to databases from rule set code, using one of two techniques.  Note the preferred technique is Server-based Access.

Server-based Access

The second technique, added in UnForm 9.0, supports access to database sources directly within the UnForm server.  When using 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 "uf101c -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$}

Windows Support Server Access

An older technique uses the Windows Support Server to access data sources available on the machine where the support server runs.  The Windows Support Server configuration window enables database connections to be configured and given a name, and two code block functions: dbconnect() and dbexecute() are provided to communicate with the named connection to return the results of a query.  The syntax of these two functions is:

[success=]dbconnect(name$[,timeout[,errmsg$]])

[success=]dbexecute(name$, command$, timeout, fdelim$, rdelim$, response$ [,errmsg$])

Both functions return 1 if successful, 0 it not.  This technique was available in starting with UnForm 7.0.