net.sf.opensmus
Interface SQLGateway

All Known Implementing Classes:
MUSSQLConnection

public interface SQLGateway

Interface used by OpenSMUS for communication with the default SQL database. This object is optimized to work with lists of LValues as parameters and return values to SQL queries. Scripting users will probably prefer to work with JDBC objects directly if data manipulation is required.
SQLGateway is only present if the EnableSQLDatabase is used in the OpenSMUS.cfg file. Use the getSQLGateway() method of the ServerObject interface to acquire this object.


Method Summary
 boolean connect(java.lang.String sqldriver, java.lang.String sqlurl, java.lang.String sqluser, java.lang.String sqlpassword)
          Connects to the default SQL database.
 void disconnect()
          Disconnects from the SQL database Usually the connection is kept open for the entire duration of the OpenSMUS session
 LValue executeQuery(java.lang.String sqlquery, LList params)
          Executes an SQL query call
Use question marks in the query as placeholders for values contained in the params list.
 boolean executeUpdate(java.lang.String sqlquery, LList params)
          Executes an SQL update call
Use question marks in the query as placeholders for values contained in the params list.
 java.sql.Connection getConnection()
          Returns the default SQL database connection as a java.sql.Connection object.
 boolean isConnected()
          Checks if the SQL database connection is alive
 

Method Detail

isConnected

boolean isConnected()
Checks if the SQL database connection is alive


getConnection

java.sql.Connection getConnection()
Returns the default SQL database connection as a java.sql.Connection object.
This is useful if the script author wants to call JDBC methods directly.


connect

boolean connect(java.lang.String sqldriver,
                java.lang.String sqlurl,
                java.lang.String sqluser,
                java.lang.String sqlpassword)
Connects to the default SQL database.
This method is usually not necessary since OpenSMUS establishes the connection automatically using the following directives from OpenSMUS.cfg:
SQLDatabaseDriver
SQLDatabaseURL
SQLDatabaseUsername
SQLDatabasePassword

Parameters:
sqldriver - JDBC database driver
sqlurl - JDBC database URL
sqluser - Username for connection
sqlpassword - Password for connection

disconnect

void disconnect()
Disconnects from the SQL database Usually the connection is kept open for the entire duration of the OpenSMUS session


executeUpdate

boolean executeUpdate(java.lang.String sqlquery,
                      LList params)
Executes an SQL update call
Use question marks in the query as placeholders for values contained in the params list.
OpenSMUS automatically maps the LList values to the appropriate SQL type.
Example:
LList params= new LList();
params.addElement(new LString("John"));
params.addElement(new LInteger(25));
sqlgateway.executeUpdate("UPDATE USERS SET NAME=? WHERE USERID=?",params);

Parameters:
sqlquery - Prepared SQL query string.
params - LList of parameters to the prepared statement.
Returns:
true if no SQL error occurs

executeQuery

LValue executeQuery(java.lang.String sqlquery,
                    LList params)
Executes an SQL query call
Use question marks in the query as placeholders for values contained in the params list.
OpenSMUS automatically maps the LList values to the appropriate SQL type.

Example:
LList params= new LList();
params.addElement(new LString("John"));
params.addElement(new LInteger(25));
LValue result = sqlgateway.executeQuery("SELECT LASTNAME, AGE FROM USERS WHERE FIRSTNAME=? AND AGE>?",params);

Parameters:
sqlquery - Prepared SQL query string.
params - LList of parameters to the prepared statement.
Returns:
LList containing the query results as LValues.
Each row in the result is returned as an LList inside the main return list.
Sample output (Lingo formatted): [["Perkins",10],["Garcia",23]]