|
![]() | ![]() ![]() ![]() ![]() ![]() ![]() ![]() | ODBC Client |
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.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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. |
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
DISCONNECT conid [ERROR errid]Disconnects the connection.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
DELETE STATEMENT statid CONNECT conid [ERROR errid]Deletes a 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
|
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. |
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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.
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
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.
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
![]() ![]() ![]() ![]() ![]() ![]() ![]() |