/* Copyright (c) 2000 David Muse See the file COPYING for more information */ #ifndef SQLRCLIENTWRAPPER_H #define SQLRCLIENTWRAPPER_H typedef long sqlrc; sqlrc sqlrc_alloc(char *server, int port, char *socket, char *user, char *password, int retrytime, int tries); /* Initiates a connection to "server" on "port" or to the unix "socket" on the local machine and authenticates with "user" and "password". Failed connections will be retried for "tries" times on interval "retrytime" or on for a default number of times on a default interval if left unspecified. If the "socket" parameter is nether NULL nor "" then an attempt will be made to connect through it before attempting to connect to "server" on "port". If it is NULL or "" then no attempt will be made to connect through the socket.*/ void sqlrc_free(sqlrc sqlrcref); /* Disconnects and ends the session if it hasn't been terminated already. */ void sqlrc_setResultSetBufferSize(sqlrc sqlrcref, int rows); /* Sets the number of rows of the result set to buffer at a time. 0 (the default) means buffer the entire result set. */ int sqlrc_getResultSetBufferSize(sqlrc sqlrcref); /* Returns the number of result set rows that will be buffered at a time or 0 for the entire result set. */ int sqlrc_ping(sqlrc sqlrcref); /* Returns 1 if the database is up and 0 if it's down. */ char *sqlrc_identify(sqlrc sqlrcref); /* Returns the type of database: oracle7, oracle8, postgresql, mysql, etc. */ void sqlrc_cacheOn(sqlrc sqlrcref); /* 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. */ void sqlrc_setCacheTtl(sqlrc sqlrcref, int 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. */ char *sqlrc_getCacheFileName(sqlrc sqlrcref); /* Returns the name of the file containing the most recently cached result set. */ void sqlrc_cacheOff(sqlrc sqlrcref); /* Sets query caching off. */ /* 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. */ int sqlrc_sendQuery(sqlrc sqlrcref, char *query); /* Sends "query" and gets a result set. */ int sqlrc_sendFileQuery(sqlrc sqlrcref, char *path, char *filename); /* Sends the query in file "path"/"filename" 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. */ void sqlrc_prepareQuery(sqlrc sqlrcref, char *query); /* Prepare to execute "query". */ void sqlrc_prepareFileQuery(sqlrc sqlrcref, char *path, char *filename); /* Prepare to execute the contents of "path"/"filename". */ void sqlrc_subString(sqlrc sqlrcref, char *variable, char *value); void sqlrc_subLong(sqlrc sqlrcref, char *variable, long value); void sqlrc_subDouble(sqlrc sqlrcref, char *variable, double value, short precision, short scale); /* Define a substitution variable. */ void sqlrc_inputBindString(sqlrc sqlrcref, char *variable, char *value); void sqlrc_inputBindLong(sqlrc sqlrcref, char *variable, long value); void sqlrc_inputBindDouble(sqlrc sqlrcref, char *variable, double value, short precision, short scale); /* Define an input bind variable. */ void sqlrc_defineOutputBind(sqlrc sqlrcref, char *variable, int length); /* Define an output bind variable. */ void sqlrc_subStrings(sqlrc sqlrcref, char **variables, char **values); void sqlrc_subLongs(sqlrc sqlrcref, char **variables, long *values); void sqlrc_subDoubles(sqlrc sqlrcref, char **variables, double *values, short *precisions, short *scales); /* Define an array of substitution variables. */ void sqlrc_inputBindStrings(sqlrc sqlrcref, char **variables, char **values); void sqlrc_inputBindLongs(sqlrc sqlrcref, char **variables, long *values); void sqlrc_inputBindDoubles(sqlrc sqlrcref, char **variables, double *values, short *precisions, short *scales); /* Define an array of input bind variables. */ void sqlrc_validateBinds(sqlrc sqlrcref); /* 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. */ int sqlrc_executeQuery(sqlrc sqlrcref); /* Execute the query that was previously prepared and bound. */ char *sqlrc_getOutputBind(sqlrc sqlrcref, char *variable); /* Get the value stored in a previously defined output bind variable. */ int sqlrc_openCachedResultSet(sqlrc sqlrcref, char *filename); /* Opens a cached result set as if a query that would have generated it had been executed. Returns 1 on success and 0 on failure. */ int sqlrc_openCachedResultSetWithOffset(sqlrc sqlrcref, char *filename, int offset, int 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. */ int sqlrc_endSession(sqlrc sqlrcref); /* Ends the session. */ int sqlrc_suspendSession(sqlrc sqlrcref); /* Disconnects this client from the current session but leaves the session open so that another client can connect to it using sqlrc_resumeSession(). */ int sqlrc_getConnectionPort(sqlrc sqlrcref); /* Returns the inet port that the client is communicating over. This parameter may be passed to another client for use in the sqlrc_resumeSession() command. */ char *sqlrc_getConnectionSocket(sqlrc sqlrcref); /* Returns the unix socket that the client is communicating over. This parameter may be passed to another client for use in the sqlrc_resumeSession() command. */ int sqlrc_getCacheFileOffset(sqlrc sqlrcref); /* 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. */ int sqlrc_resumeSession(sqlrc sqlrcref, int port, char *socket); /* Resumes a session previously left open using sqlrc_suspendSession(). Returns 1 on success and 0 on failure. */ int sqlrc_resumeCachedSession(sqlrc sqlrcref, int port, char *socket, char *cachefilename); /* Resumes a session previously left open using sqlrc_suspendSession() and continues caching the result set to "cachefilename". Returns 1 on success and 0 on failure. */ int sqlrc_colCount(sqlrc sqlrcref); /* Returns the number of columns in the current result set. */ int sqlrc_rowCount(sqlrc sqlrcref); /* Returns the number of rows in the current result set. */ int sqlrc_totalRows(sqlrc sqlrcref); /* 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. */ int sqlrc_affectedRows(sqlrc sqlrcref); /* 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. */ int sqlrc_firstRowIndex(sqlrc sqlrcref); /* Returns the index of the first buffered row. This is useful when buffering only part of the result set at a time. */ char *sqlrc_errorMessage(sqlrc sqlrcref); /* If a query failed and generated an error, the error message is available here. If the query succeeded then this method returns a NULL. */ void sqlrc_getNullsAsEmptyStrings(sqlrc sqlrcref); /* Tells the client to return NULL fields and output bind variables as empty strings. This is the default. */ void sqlrc_getNullsAsNulls(sqlrc sqlrcref); /* Tells the client to return NULL fields and output bind variables as NULL's. */ char *sqlrc_getFieldByIndex(sqlrc sqlrcref, int row, int col); char *sqlrc_getFieldByName(sqlrc sqlrcref, int row, char *col); /* Returns a pointer to the value of the specified row and column. */ char **sqlrc_getRow(sqlrc sqlrcref, int row); /* Returns a null terminated array of the values of the specified row. */ char **sqlrc_getColumnNames(sqlrc sqlrcref); /* Returns a null terminated array of the column names of the current result set. */ char *sqlrc_getColumnName(sqlrc sqlrcref, int col); /* Returns the name of the specified column. */ char *sqlrc_getColumnTypeByIndex(sqlrc sqlrcref, int col); char *sqlrc_getColumnTypeByName(sqlrc sqlrcref, char *col); /* Returns the type of the specified column. */ int sqlrc_getColumnLengthByIndex(sqlrc sqlrcref, int col); int sqlrc_getColumnLengthByName(sqlrc sqlrcref, char *col); /* Returns the length of the specified column. */ int sqlrc_getLongestByIndex(sqlrc sqlrcref, int col); int sqlrc_getLongestByName(sqlrc sqlrcref, char *col); /* Returns the length of the longest field in the specified column. */ void sqlrc_debugPrintFunction(sqlrc sqlrcref, int (*printfunction)(const char *,...)); /* Allows you to replace the function used to print debug messages with your own function. The function needs to work like printf. */ void sqlrc_debugOn(sqlrc sqlrcref); /* Causes verbose debugging information to be sent to standard output. Another way to do this is to start a query with "-- debug\n". */ void sqlrc_debugOff(sqlrc sqlrcref); /* Turns debugging off. */ int sqlrc_getDebug(sqlrc sqlrcref); /* Returns 0 if debugging is off and 1 if debugging is on. */ void sqlrc_copyReferences(sqlrc sqlrcref); /* don't use this function for anything, it's here to help bridge between C and scripting languages */ /* 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 subStrings(), subLongs(), subDoubles(), inputBindStrings(), inputBindLongs() and inputBindDoubles() 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 subString(), subLong(), subDouble(), inputBindString(), inputBindLong(), inputBindDouble() 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. */ #endif