# Copyright (c) 2000 Roman Milner # See the file COPYING for more information import CSQLRelay class sqlrclient: """ A wrapper for the sqlrelay client API. Closely follows the C++ API. """ def __init__(self, connection_params, user, password, retrytime=1, tries=1): """ Opens a connections to the sqlrelay server and authenticates with user and password. Failed connections are retried for tries times at retrytime interval. connection_parames can either be a string for a local unix socket socket or a tuple of host and port number for tcp sockets.""" def __del__(self): def setResultSetBufferSize(self, rows): """ Sets the number of rows of the result set to buffer at a time. 0 (the default) means buffer the entire result set. """ def getResultSetBufferSize(self): """ Returns the number of result set rows that will be buffered at a time or 0 for the entire result set. """ def ping(self): """ Returns 1 if the database is up and 0 if it's down. """ def identify(self): """ Returns the type of database: oracle7, oracle8, postgresql, mysql, etc. """ def cacheOn(self): """ Sets query caching on. Future queries will be cached to local files, the names of which can be retrieved using getCacheFileName() after each query is sent. A default time-to-live of 10 minutes is also set. """ def setCacheTtl(self, ttl): """ Sets the time-to-live for cached result sets. The sqlr-cachemanger will remove each cached result set "ttl" seconds after it's created. """ def getCacheFileName(self): """ Returns the name of the file containing the most recently cached result set. """ def cacheOff(self): """ Sets query caching off. """ """ If you don't need to use substitution or bind variables in your queries, use these two methods. """ def sendQuery(self, query): """ Send a SQL query to the server and gets a result set. """ def sendFileQuery(self, path, file): """ Send the SQL query in path/file to the server and gets a result set. """ """ If you need to use substitution or bind variables, in your queries use the following methods. See the footnote for information about substitution and bind variables. """ def prepareQuery(self, query): """ Prepare to execute query. """ def prepareFileQuery(self, path, file): """ Prepare to execute the contents of path/filename. """ def substitution(self, variable, value, precision=0, scale=0): """ Define a substitution variable. """ def inputBind(self, variable, value, precision=0, scale=0): """ Define an input bind varaible. """ def defineOutputBind(self, variable, length): """ Define an output bind varaible. """ def substitutions(self, variables, values, precisions=None, scales=None): """ Define substitution variables. """ def inputBinds(self, variables, values, precisions=None, scales=None): """ Define input bind variables. """ def validateBinds(self): """ If you are binding to any variables that might not actually be in your query, call this to ensure that the database won't try to bind them unless they really are in the query. """ def executeQuery(self): """ Execute the query that was previously prepared and bound. """ def getOutputBind(self, variable): """ Retrieve the value of an output bind variable. """ def openCachedResultSet(self, filename): """ Open a result set after a sendCachedQeury """ def openCachedResultSetWithOffset(self, filename, offset, row): """ Opens a cached result set and skips to "offset". The "row" parameter tells the method which row that offset corresponds to. Returns 1 on success and 0 on failure. """ def rowCount(self): """ Returns the number of rows in the current result set. """ def colCount(self): """ Returns the number of columns in the current result set. """ def totalRows(self): """ Returns the total number of rows that will be returned in the result set. Not all databases support this call. Don't use it for applications which are designed to be portable across databases. -1 is returned by databases which don't support this option. """ def affectedRows(self): """ Returns the number of rows that were updated, inserted or deleted by the query. Not all databases support this call. Don't use it for applications which are designed to be portable across databases. -1 is returned by databases which don't support this option. """ def firstRowIndex(self): """ Returns the index of the first buffered row. This is useful when buffering only part of the result set at a time. """ def errorMessage(self): """ If the query failed, the error message will be returned from this method. Otherwise, it returns None. """ def endSession(self): """ Ends the current session. """ def suspendSession(self): """ Disconnects this client from the current session but leaves the session open so that another client can connect to it using resumeSession(). """ def resumeSession(self, port, socket): """ Resumes a session previously left open using suspendSession(). Returns 1 on success and 0 on failure. """ def resumeCachedSession(self, port, socket, cachefilename): """ Resumes a session previously left open using suspendSession() and continues caching the result set to "cachefilename". Returns 1 on success and 0 on failure. """ def getConnectionPort(self): """ Returns the inet port that the client is communicating over. This parameter may be passed to another client for use in the resumeSession() method. """ def getConnectionSocket(self): """ Returns the unix socket that the client is communicating over. This parameter may be passed to another client for use in the resumeSession() method. """ def getCacheFileOffset(self): """ Returns the offset in the currently open cache file. This parameter may be used in the openCachedResultSet() method to jump to a particular offset in the file. """ def getNullsAsEmptyStrings(self): """ Tells the client to return NULL fields and output bind variables as empty strings. This is the default. """ def getNullsAsNone(self): """ Tells the client to return NULL fields and output bind variables as NULL's. """ def getField(self, row, col): """ Returns the value of the specified row and column. col may be a column name or number. """ def getRow(self, row): """ Returns a list of values in the given row. """ def getRowRange(self, beg, end): """ Returns a list of lists of the rows between beg and end. Note: this function has no equivalent in other SQL Relay API's. """ def getRowDictionary(self, row): """ Returns the requested row as values in a dictionary with column names for keys. """ def getColumnName(self, col): """ Returns the name of column number col. """ def getColumnNames(self): """ Returns a list of column names in the current result set. """ def getColumnType(self, col): """ Returns the type of the specified column. col may be a name or number. """ def getColumnLength(self, col): """ Returns the length of the specified column. col may be a name or number. """ def getLongest(self, col): """ Returns the length of the specified column. col may be a name or number. """ def debugOn(self): """ Turn verbose debugging on. """ def debugOff(self): """ Turn verbose debugging off. """ def getDebug(self): """ Returns 1 if debugging is turned on and 0 if debugging is turned off. """ """ Footnote about Substitution and Bind variables... Example query: select first_name, middle_initial, last_name from $(schema).people where person_id=:id and age>=:youngage and age<=:oldage In this query, $(schema) is a substitution variable and :id, :youngage and :oldage are input bind variables. Substitution and bind variables allow values to be passed from your program into queries or procedural code. Example procedural code: BEGIN :returnval:=100*50; END; In this code, :returnval is an output bind variable. Output bind variables allow values to be passed from procedural code into your program. The substitutions() and inputBinds() methods have array parameters. Variable and value arrays must be NULL terminated arrays of strings. Buffer arrays must be NULL terminated arrays of (char *)'s. Length arrays must be NULL terminated arrays of integers. For example: char *variablearray[]={"var1","var2","var3",NULL}; char *valuearray[]={"val1","val2","val3",NULL}; char *buffer1=new char[100]; char *buffer2=new char[100]; char *buffer3=new char[100]; char *bufferarray[]={buffer1,buffer2,buffer3,NULL}; char *lengtharray[]={100,100,100,NULL}; or char buffer1[100]; char buffer2[100]; char buffer3[100]; char *bufferarray[]={&buffer1,&buffer2,&buffer3,NULL}; char *lengtharray[]={100,100,100,NULL}; The substitution(), inputBind() and defineOutputBind() methods have individual parameters. Individual variable and values must be strings. Individual buffers must be (char *)'s. Individual lengths must be integers. For example: char *variable1="var1"; char *variable2="var2"; char *variable3="var3"; char *value1="val1"; char *value2="val2"; char *value3="val3"; char *buffer1=new char[100]; char *buffer2=new char[100]; char *buffer3=new char[100]; int length1=100; int length2=100; int length3=100; What exactly are substitution and bind variables? Substitution and input bind variables are both methods for replacing a variable in a query or procedural code with a corresponding value from your program. Output bind variables allow values to be passed from procedural code into buffers in your program. Substitution variable format: $(variable) Bind variable format: :variable Substitution variables are processed first, by the API. Input bind variables are processed second, by the underlying RDBMS or by the connection daemon in the event that the RDBMS doesn't support bind variables. Output bind variables are processed by the RDBMS as the query or procedural code is executed. Substitution variables may be anything and appear anywhere in the query. They are frequently used to ammend where clauses with additional constraints and specify schemas. Input bind variables may only be used as rvalue's either in the where clause of a query or in procedural statements. Output bind variables may only be used as lvalue's in procedural statements. A substitution value (such as an additional where clause constraint or chunk of procedural code) may contain bind variables. Why should I use input bind variables instead of just using substitution variables for everything? To improve performance. RDBMS's which support bind variables parse the query then plug input bind variables into the already parsed code. If the same query is run a bunch of times, even with different values for the input bind variables, the RDBMS will have the code cached and won't have to parse the query again. If you don't use input bind variables, the RDBMS will parse the query each time because the where clause will be slightly different each time and the code for all those slightly different queries will clog the cache. Generally speaking, you should use input bind variables for substitutions in the where clause. What if my RDBMS doesn't support bind variables? Use substitution variables instead, the bind variable API calls will be useless to you. """