-
Notifications
You must be signed in to change notification settings - Fork 2
sql_methods
HyperSQL methods provide an interface similar to Oracle's SQLPlus, with important differences:
- the semicolon statement terminator is not required, and will generate an error if used.
- the COMMIT SQL command is built into each transaction you perform with the HS sql method.
- HS variable names are automatically mapped to SELECT variables in SELECT statements.
- the SQL statement can contain BIND variables which are mapped to HS variables.
The sql_connect method logs your HS session onto an Oracle account. You cannot execute any SQL commands until you have successfully connected to your Oracle account.
SYNTAX::
sql_connect ( username, password [,database, handle] ) ;
``
_
EXAMPLES:
/* Connect to local ORACLE database as user scott */
sql_connect ( "scott", "mypassword" ) ;
/* Connect to remote ORACLE database as user scott */
sql_connect ( "scott", "mypassword", "remotedb", "mydb" ) ;
``
_
The sql method allows you to issue any Oracle SQL command. Generally, most SQL commands that you can issue using SQLplus can also be issued using the HS sql method.
FORMAT:
sql ( sql_statement, [handle] ) ;
``
_
EXAMPLES:
/* Create a new table called test with the specified fields */
sql ( "create table test (string char(20), intval number(15),
2 floatval number(7,2), dateval date)" ) ;
/* Insert values into the table */
sql ( "insert into test values ('funny', 10, 5.2, '11-SEP-97' )" ) ;
sql ( "insert into test values ('happy', 20, 10.8, '23-MAY-69' )" ) ;
``
_
The SQL statements issued in the sql method are passive with respect to HS. In other words, they are not dependent on any HS variables or their values. For a more dynamic SQL statement, you can map HS variables to Oracle column names using SELECT Variables, or bind HS variables within the SQL statement using BIND Variables.
The sql_statement argument to the sql method can also be a list. For variable names from HS, use the & reference. operator. For variable values from HS, simply reference the variable name (or expression). However, in the case of string values, SQL syntax requires that string literals be enclosed in single quotes. This is an inconvenience and thus it is better to use SQL BIND Variables.
The following examples are valid uses of the sql method:
EXAMPLE 1:
str S = "'funny'" ;
int I = 10 ;
float F = 5.2 ;
str D = "'11-SEP-97'" ;
str stmt1 = { "INSERT INTO TEST VALUES (", S, I, F, D, ")" } ;
/* 'stmt1' is equivalent to:
* "insert into test values ('funny', 10, 5.2, '11-SEP-97' )"
*/
sql ( stmt1 ) ; /* Issue the SQL statement, stmt1 */
``
_
EXAMPLE 2:
str stmt2 = {"SELECT", &STRVAL, &FLOATVAL, "from TEST where", &INTVAL, "", 20 } ;
/* 'stmt2' is equivalent to:
* "SELECT STRVAL, FLOATVAL from TEST where INTVAL 20"
*/
sql ( stmt2 ) ; /* Issue the SQL statement, stmt2 */
``
_