HomeStartingEnvironmentDBMSVisualPQLProceduresSQLFormsHost/APIIndex
VisualPQL homecontents start chapter top of pagebottom of pagenext page index ODBC Client

ODBC Client

VisualPQL programs can access ODBC data sources (using ODBC) and can directly access the SIRSQL Server. The VisualPQL program establishes a connection id and a statement id that are the key identifiers for other operations. It then passes the text of an SQL query and executes this. The program can enquire as to the columns and rows available from the query and can get data from each column, stepping through the rows one at a time.

When using the SIRSQL server to do a query across more than one data source, the program establishes a connection to the multiple data sources with the same user name and password.

A program can have multiple connections open at one time. A connection can have multiple statements. Query results are by connection/statement.

Every command has an ERROR clause that returns a status that indicates success or failure. The functions return an error code. Further information about the error can be retrieved by the GETERR function.

homecontents start chapter top of pagebottom of pagenext page index

CONNECT

CONNECT conid SERVER name {DATABASE name | TABFILE name}
[USER name]
[PASSWORD name]
[PREFIX name] [UPDATE | READ]
[ERROR errid]
Creates a connection to ODBC or to a SIR/XS SQLserver. A name as used in various parts of the command is a string expression i.e. a string variable or a constant enclosed in quotes.

conid conid is a numeric variable that returns an arbitrary number assigned by the system and subsequently used to identify the connection.

SERVER name SERVER name is either the string ODBC (must be uppercase) or the TCP/IP address of the server.

DATABASE name | TABFILE name DATABASE name | TABFILE name is the name of the data source as used by ODBC or the server. This is nothing to do with any databases or tabfiles directly connected by SIR/XS.

USER name USER name has three possible components. The first is an arbitrary name used to identify that this connection is one of those associated with a single 'user' (i.e. this program) if a query is done across multiple connections. If a tabfile is being connected, the second and third components are used to specify any group and user name for that tabfile. Separate multiple components by commas.
This is typically blank for non-SIR/XS ODBC sources.

PASSWORD PASSWORD name has four possible components. The first is a password for the connection associated with a single 'user' (i.e. this program) if a query is done across multiple connections. If a database is being connected, the second, third and fourth components are used to specify the database password, the read password and the write password. If a tabfile is being connected, the second and third components are used to specify any group and/or user passwords for that tabfile. Separate multiple components by commas.
This is typically blank for non-SIR/XS ODBC sources.

PREFIX PREFIX is the directory prefix for the SIRSQL server to find the database. If this is not fully qualified, the SQLSQL Server takes it to apply from its own local directory structure. If the server is set to ODBC then the prefix ignored as it is already specified in the data source setup.

UPDATE | READ UPDATE | READ allow/disallow SQL statements that update the data source. The default is read.

ERROR errid ERROR errid is a numeric variable that returns a status code. A value of zero or less indicates that the connection failed.

homecontents start chapter top of pagebottom of pagenext page index

DISCONNECT

DISCONNECT conid [ERROR errid]
Disconnects the connection.

homecontents start chapter top of pagebottom of pagenext page index

STATEMENT

STATEMENT statid CONNECT conid [ERROR errid]
Creates an arbitrary statement number for a connection that is subsequently used to identify the statement.

statid is a numeric variable that returns the statement number.

homecontents start chapter top of pagebottom of pagenext page index

DELETE STATEMENT

DELETE STATEMENT statid CONNECT conid [ERROR errid]
Deletes a statement

homecontents start chapter top of pagebottom of pagenext page index

PREPARE STATEMENT

PREPARE STATEMENT statid CONNECT conid
{COMMAND string_expression | BUFFER buffer_name}
[ERROR errid]
Sends the text of an SQL statement to ODBC or to the SQLServer. This does not run the SQL command but does check it for validity.

COMMAND string_expression
BUFFER buffer_name
The keyword COMMAND specifies that this is a string expression which can resolve to a string up to 4094 characters. The keyword BUFFER specifies that this is a string expression which can resolve the name of a buffer which contains text up to 4094 characters. The text is expected to be a valid SQL statement.

ERROR errid ERROR errid is a numeric variable that returns a status code. A value of zero or less indicates that the SQL statement was not valid.

homecontents start chapter top of pagebottom of pagenext page index

BIND STATEMENT

BIND STATEMENT statid CONNECT conid (param_no,value)
[ERROR errid]
SQL queries may contain parameterised values where a value is not specified directly. A parameter is shown in the SQL query as a question mark ? e.g.
SELECT * FROM EMPLOYEE WHERE ID EQ ?
If a statement has multiple parameters, they are identified positionally; the first question mark is parameter 1, the second 2, etc.

The BIND STATEMENT supplies values for the parameters. Values may either be numeric or string expressions. e.g.

BIND STATEMENT statid CONNECT conid (1,10)
BIND STATEMENT statid CONNECT conid (2,'John')
Parameters may be bound before the PREPARE STATEMENT or before the EXECUTE STATEMENT. Parameters may also be bound using the
BINDPARM function.

Because the type (numeric or string) of the parameter is not known at compile time, make sure it matches the data type of the variable that it applies to. Data for string, categorical vars, dates and times must be string expressions.

homecontents start chapter top of pagebottom of pagenext page index

EXECUTE STATEMENT

EXECUTE STATEMENT statid CONNECT conid [ERROR errid]
This runs the prepared statement and produces a set of output. This might take some time depending on the size of the data source and the query.

The output can be examined using the VisualPQL client/server functions.

Example

PROGRAM
INTEGER*4 errid conid statid rnum cnum
STRING*20 cname colval
STRING*80 qtext errstr
CONNECT conid SERVER 'ODBC'
              DATABASE 'Company'
              USER     'me'
              PASSWORD  'mypwd,COMPANY,HIGH,HIGH'
              PREFIX    ''
              ERROR     errid
STATEMENT statid  CONNECT conid ERROR errid
WRITE errid
PREPARE STATEMENT statid CONNECT conid
        COMMAND 'SELECT * FROM EMPLOYEE'
        ERROR errid
WRITE errid
EXECUTE STATEMENT statid CONNECT conid ERROR errid
WRITE errid
COMPUTE cnum = COLCOUNT (conid,statid)
COMPUTE rnum = ROWCOUNT (conid,statid)
WRITE 'Columns returned ' cnum ' Rows returned ' rnum
FOR I = 1,cnum
. COMPUTE cname = COLNAME (conid,statid,i)
. WRITE cname
END FOR
SET J (0)
LOOP
. COMPUTE j = j+1
. COMPUTE res = NEXTROW (conid,statid)
. IF (res LE 0) EXIT LOOP
. FOR I = 1,cnum
. IFTHEN (COLTYPE(conid,statid,i) eq 1)
.   COMPUTE colval = COLVALS (conid,statid,j,i)
. ELSE
.   COMPUTE colval = FORMAT (COLVALN (conid,statid,j,i))
. ENDIF
. WRITE colval
. END FOR
END LOOP
DELETE STATEMENT statid CONNECT conid
DISCONNECT conid
END PROGRAM

homecontents start chapter top of pagebottom of pagenext page index