VisualPQL 
|
| VisualPQL | | 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 SQLserver.
conid is a numeric variable that returns an arbitrary number assigned by the system and subsequently used to identify the connection.
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.
SERVER name is either the string ODBC (must be uppercase) or the TCP/IP address of the server.
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 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 ODBC sources.
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 ODBC sources.
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 allow/disallow SQL statements that update the data source. The default is read.
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 name]Disconnects the connection.
| |
STATEMENT statid CONNECT conid [ERROR name]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 name]Deletes a statement
PREPARE STATEMENT statid CONNECT conid
{COMMAND text_expression | BUFFER buffer_name}
[ERROR name]
Sends the text of an SQL statement. This can either be a string expression (e.g. a string variable in the VisualPQL) that contains text up to 254 characters or it can be the name of a buffer that contains the text of a long SQL statement (up to 4K).
BIND STATEMENT statid CONNECT conid (param_no,value) [ERROR name]SQL queries may contain parametised values, that us the value is not specified directly in the query but separately via a
BIND command. 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, that is the first question mark is parameter 1, the second 2, etc.
The BIND 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 or after a statement is prepared. 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 name]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
| |