// Copyright (c) 1999-2000 David Muse // See the file COPYING for more information. #ifndef SQLRCLIENT_H #define SQLRCLIENT_H #include <fstream.h> #include <client.h> #include <private/row.h> #include <private/column.h> #include <private/field.h> #include <private/bindvar.h> #define MAXQUERYSIZE 32768 #define MAXVAR 256 #ifndef MAXPATHLEN #define MAXPATHLEN 256 #endif class sqlrclient : public client { public: sqlrclient(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 neither // 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. ~sqlrclient(); // Disconnects and ends the session if // it hasn't been ended already. void setResultSetBufferSize(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 getResultSetBufferSize(); // Returns the number of result set rows that // will be buffered at a time or 0 for the // entire result set. int ping(); // Returns 1 if the database is up and 0 // if it's down. char *identify(); // Returns the type of database: // oracle7, oracle8, postgresql, mysql, etc. void cacheOn(); // 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 setCacheTtl(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 *getCacheFileName(); // Returns the name of the file containing the // most recently cached result set. void cacheOff(); // Sets query caching off. // If you don't need to use substitution or bind variables // in your queries, use these two methods. int sendQuery(char *query); // Sends "query" and gets a result set. int sendFileQuery(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 prepareQuery(char *query); // Prepare to execute "query". void prepareFileQuery(char *path, char *filename); // Prepare to execute the contents // of "path"/"filename". void substitution(char *variable, char *value); void substitution(char *variable, long value); void substitution(char *variable, double value, short precision, short scale); // Define a substitution variable. void inputBind(char *variable, char *value); void inputBind(char *variable, long value); void inputBind(char *variable, double value, short precision, short scale); // Define an input bind variable. void defineOutputBind(char *variable, int bufferlength); // Define an output bind variable. void substitutions(char **variables, char **values); void substitutions(char **variables, long *values); void substitutions(char **variables, double *values, short *precisions, short *scales); // Define an array of substitution variables. void inputBinds(char **variables, char **values); void inputBinds(char **variables, long *values); void inputBinds(char **variables, double *values, short *precisions, short *scales); // Define an array of input bind variables. void validateBinds(); // 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 executeQuery(); // Execute the query that was previously // prepared and bound. char *getOutputBind(char *variable); // Get the value stored in a previously // defined output bind variable. int openCachedResultSet(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 openCachedResultSet(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 endSession(); // Ends the session. int suspendSession(); // Disconnects this client from the current // session but leaves the session open so // that another client can connect to it // using resumeSession(). int getConnectionPort(); // Returns the inet port that the client is // communicating over. This parameter may be // passed to another client for use in // the resumeSession() method. char *getConnectionSocket(); // Returns the unix socket that the client is // communicating over. This parameter may be // passed to another client for use in // the resumeSession() method. int getCacheFileOffset(); // 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 resumeSession(int port, char *socket); // Resumes a session previously left open // using suspendSession(). // Returns 1 on success and 0 on failure. int resumeCachedSession(int port, char *socket, char *cachefile); // Resumes a session previously left open // using suspendSession() and continues caching // the result set to "cachefile". // Returns 1 on success and 0 on failure. int colCount(); // Returns the number of columns in the current // result set. int rowCount(); // Returns the number of rows in the current // result set (if the result set is being // stepped through, this returns the number // of rows processed so far). int totalRows(); // 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 affectedRows(); // 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 firstRowIndex(); // Returns the index of the first buffered row. // This is useful when buffering only part of // the result set at a time. char *errorMessage(); // 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 getNullsAsEmptyStrings(); // Tells the client to return NULL fields // and output bind variables as empty strings. // This is the default. void getNullsAsNulls(); // Tells the client to return NULL fields // and output bind variables as NULL's. char *getField(int row, int col); char *getField(int row, char *col); // Returns a pointer to the value of the // specified row and column. char *getFieldLength(int row, int col); char *getFieldLength(int row, char *col); // Returns the length of the value of the // specified row and column. char **getRow(int row); // Returns a null terminated array of the // values of the specified row. char **getColumnNames(); // Returns a null terminated array of the // column names of the current result set. char *getColumnName(int col); // Returns the name of the specified column. char *getColumnType(int col); char *getColumnType(char *col); // Returns the type of the specified column. int getColumnLength(int col); int getColumnLength(char *col); // Returns the length of the specified column. int getLongest(int col); int getLongest(char *col); // Returns the length of the longest field // in the specified column. void debugPrintFunction(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 debugOn(); // Causes verbose debugging information to be // sent to standard output. Another way to do // this is to start a query with "-- debug\n". void debugOff(); // Turns debugging off. int getDebug(); // Returns 0 if debugging is off and 1 if // debugging is on. private: #include <private/sqlrclient.h> }; // 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. #endif