sql relay的c++接口

Programming with SQL Relay using the C++ API
Compiling an SQL Relay Client Program
Establishing a Sessions
Executing Queries
Commits and Rollbacks
Temporary Tables
Catching Errors
Substitution and Bind Variables
Re-Binding and Re-Executing
Accessing Fields in the Result Set
Dealing With Large Result Sets
Cursors
Getting Column Information
Stored Procedures
Caching The Result Set
Suspending and Resuming Sessions
Compiling an SQL Relay Client Program
When writing an SQL Relay client program using the C++ API, you need to include the sqlrclient.h file.
> > #include
   1<sqlrelay sqlrclient.h="">
   2&gt;     
   3
   4You'll also need to link against the sqlrclient and rudiments libraries. The include file is usually found in /usr/local/firstworks/include and the libraries are usually found in /usr/local/firstworks/lib. 
   5
   6The command to compile your .C file to object code will look something like this (assuming you're using GNU C++): 
   7
   8&gt; g++ -I/usr/local/firstworks/include -c myprogram.C 
   9
  10The command to compile your .o file to an executable will look something like this (assuming you're using GNU C++): 
  11
  12&gt; g++ -o myprogram myprogram.o -L/usr/local/firstworks/lib -lsqlrclient -lrudiments 
  13
  14Establishing a Session    
  15  
  16
  17
  18To use SQL Relay, you have to identify the connection that you intend to use. 
  19
  20&gt; 
  21&gt;     #include <sqlrelay sqlrclient.h="">
  22&gt;     
  23&gt;     main() {
  24&gt;     
  25&gt;            sqlrconnection      con= **new** sqlrconnection("host",9000,"","user","password",0,1);
  26&gt;     
  27&gt;            ... execute some queries ...
  28&gt;     
  29&gt;            **delete** con;
  30&gt;     }
  31&gt;     
  32
  33After calling the constructor, a session is established when the first query, ping() or identify() is run. 
  34
  35For the duration of the session, the client stays connected to a database connection daemon. While one client is connected, no other client can connect. Care should be taken to minimize the length of a session. 
  36
  37If you're using a transactional database, ending a session has a catch. Database connection daemons can be configured to send either a commit or rollback at the end of a session if DML queries were executed during the session with no commit or rollback. Program accordingly. 
  38
  39Executing Queries    
  40  
  41
  42
  43Allocate a cursor, then call sendQuery() or sendFileQuery() to run a query. The same cursor may be used over and over. 
  44
  45&gt; 
  46&gt;     #include <sqlrelay sqlrclient.h="">
  47&gt;     
  48&gt;     main() {
  49&gt;     
  50&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
  51&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
  52&gt;     
  53&gt;             cur-&gt;sendQuery("select * from my_table");
  54&gt;     
  55&gt;             ... do some stuff that takes a short time ...
  56&gt;     
  57&gt;             cur-&gt;sendFileQuery("/usr/local/myprogram/sql","myquery.sql");
  58&gt;             con-&gt;endSession();
  59&gt;     
  60&gt;             ... do some stuff that takes a long time ...
  61&gt;     
  62&gt;             cur-&gt;sendQuery("select * from my_other_table");
  63&gt;             con-&gt;endSession();
  64&gt;     
  65&gt;             ... process the result set ...
  66&gt;     
  67&gt;             **delete** cur;
  68&gt;             **delete** con;
  69&gt;     }
  70&gt;     
  71
  72Note the call to endSession() after the call to sendFileQuery(). Since the program does some stuff that takes a long time between that query and the next, ending the session there allows another client an opportunity to use that database connection while your client is busy. The next call to sendQuery() establishes another session. Since the program does some stuff that takes a short time between the first two queries, it's OK to leave the session open between them. 
  73
  74Commits and Rollbacks    
  75  
  76
  77
  78If you need to execute a commit or rollback, you should use the commit() and rollback() methods of the sqlrconnection class rather than sending a "commit" or "rollback" query. There are two reasons for this. First, it's much more efficient to call the methods. Second, if you're writing code that can run on transactional or non-transactional databases, some non-transactional databases will throw errors if they receive a "commit" or "rollback" query, but by calling the commit() and rollback() methods you instruct the database connection daemon to call the commit and rollback API methods for that database rather than issuing them as queries. If the API's have no commit or rollback methods, the calls do nothing and the database throws no error. This is especially important when using SQL Relay with ODBC. 
  79
  80You can also turn Autocommit on or off with the autoCommitOn() and autoCommitOff() methods of the sqlrconnection class. When Autocommit is on, the database performs a commit after each successful DML or DDL query. When Autocommit is off, the database commits when the client instructs it to, or (by default) when a client disconnects. For databases that don't support Autocommit, autoCommitOn() and autoCommitOff() have no effect. 
  81
  82Temporary Tables    
  83  
  84
  85
  86Some databases support temporary tables. That is, tables which are automatically dropped or truncated when an application closes it's connection to the database or when a transaction is committed or rolled back. 
  87
  88For databases which drop or truncate tables when a transaction is committed or rolled back, temporary tables work naturally. 
  89
  90However, for databases which drop or truncate tables when an application closes it's connection to the database, there is an issue. Since SQL Relay maintains persistent database connections, when an application disconnects from SQL Relay, the connection between SQL Relay and the database remains, so the database does not know to drop or truncate the table. To remedy this situation, SQL Relay parses each query to see if it created a temporary table, keeps a list of temporary tables and drops (or truncates them) when the application disconnects from SQL Relay. Since each database has slightly different syntax for creating a temporary table, SQL Relay parses each query according to the rules for that database. 
  91
  92In effect, temporary tables should work when an application connects to SQL Relay in the same manner that they would work if the application connected directly to the database. 
  93
  94Catching Errors    
  95  
  96
  97
  98If your call to sendQuery() or sendFileQuery() returns a 0, the query failed. You can find out why by calling errorMessage(). 
  99
 100&gt; 
 101&gt;     #include <sqlrelay sqlrclient.h="">
 102&gt;     #include <iostream.h>
 103&gt;     
 104&gt;     main() {
 105&gt;     
 106&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 107&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 108&gt;     
 109&gt;             **if** (!cur-&gt;sendQuery("select * from my_nonexistant_table")) {
 110&gt;                     cout &lt;&lt; cur-&gt;errorMessage() &lt;&lt; endl;
 111&gt;             }
 112&gt;     
 113&gt;             **delete** cur;
 114&gt;             **delete** con;
 115&gt;     }
 116&gt;     
 117&gt;     
 118
 119Substitution and Bind Variables    
 120  
 121
 122
 123Programs rarely execute fixed queries. More often than not, some part of the query is dynamically generated. It's convenient to store queries in files so they can be changed by a non-C++ programmer. The SQL Relay API provides methods for making substitutions and binds in those queries. 
 124
 125For a detailed discussion of substitutions and binds, see  this document  . 
 126
 127Rather than just calling sendFileQuery() you call prepareFileQuery(), substitution(), inputBind() and executeQuery(). 
 128
 129&gt; 
 130&gt;     /usr/local/myprogram/sql/myquery.sql:
 131&gt;     
 132&gt;     	select * from mytable $(whereclause)
 133&gt;     
 134&gt; 
 135&gt; Program code: 
 136&gt;     
 137&gt;     
 138&gt;     #include <sqlrelay sqlrclient.h="">
 139&gt;     
 140&gt;     main() {
 141&gt;     
 142&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 143&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 144&gt;     
 145&gt;             cur-&gt;prepareFileQuery("/usr/local/myprogram/sql","myquery.sql");
 146&gt;             cur-&gt;substitution("whereclause","where col1=:value1");
 147&gt;             cur-&gt;inputBind("value1","true");
 148&gt;             cur-&gt;executeQuery();
 149&gt;     
 150&gt;             ... process the result set ...
 151&gt;     
 152&gt;             **delete** cur;
 153&gt;             **delete** con;
 154&gt;     }
 155&gt;     
 156
 157If you're using a database with an embedded procedural language, you may want to retrieve data from a call to one of it's functions. To facilitate this, SQL Relay provides the defineOutputBind() and getOutputBind() methods. 
 158
 159&gt; PL/SQL Procedure:   
 160&gt; 
 161&gt;     
 162&gt;     
 163&gt;     FUNCTION sp_mytable RETURN types.cursorType
 164&gt;     l_cursor types.cursorType;
 165&gt;     BEGIN
 166&gt;             OPEN l_cursor FOR **SELECT** * FROM mytable;
 167&gt;             RETURN l_cursor;
 168&gt;     END;
 169&gt;     
 170&gt; 
 171&gt;   
 172&gt;  Program code: 
 173&gt;     
 174&gt;     
 175&gt;     #include <sqlrelay sqlrclient.h="">
 176&gt;     
 177&gt;     main() {
 178&gt;     
 179&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 180&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 181&gt;     
 182&gt;             cur-&gt;prepareQuery("begin  :result:=addTwoNumbers(:num1,:num2);  end;");
 183&gt;             cur-&gt;inputBind("num1",10);
 184&gt;             cur-&gt;inputBind("num2",20);
 185&gt;             cur-&gt;defineOutputBind("result",100);
 186&gt;             cur-&gt;executeQuery();
 187&gt;             **int**     result=atoi(cur-&gt;getOutputBind("result"));
 188&gt;             con-&gt;endSession();
 189&gt;     
 190&gt;             ... do something with the result ...
 191&gt;     
 192&gt;             **delete** cur;
 193&gt;             **delete** con;
 194&gt;     }
 195&gt;     
 196
 197The getOutputBind() method returns a NULL value as an empty string. If you would it to come back as a NULL instead, you can call the getNullsAsNulls() method. To revert to the default behavior, you can call getNullsAsEmptyStrings(). 
 198
 199The getOutputBind() method returns a string, if you would like to get the value as a long or double, you can use getOutputBindAsLong() or getOutputBindAsDouble(). 
 200
 201If you are using Oracle 8i or higher, you can insert data into BLOB and CLOB columns using the inputBindBlob(), inputBindClob() methods. 
 202
 203If you are curious how many bind variables have been declared in a query, you can call countBindVariables() after preparing the query. 
 204
 205&gt; 
 206&gt;     #include <sqlrelay sqlrclient.h="">
 207&gt;     
 208&gt;     main() {
 209&gt;     
 210&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 211&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 212&gt;     
 213&gt;             cur-&gt;executeQuery("create table images (image blob, description clob)");
 214&gt;     
 215&gt;             **unsigned** **char**   imagedata[40000];
 216&gt;             **unsigned** **long**   imagelength;
 217&gt;     
 218&gt;             ... read an image from a file into imagedata and the length of the
 219&gt;                     file into imagelength ...
 220&gt;     
 221&gt;             **unsigned** **char**   description[40000];
 222&gt;             **unsigned** **long**   desclength;
 223&gt;     
 224&gt;             ... read a description from a file into description and the length of
 225&gt;                     the file into desclength ...
 226&gt;     
 227&gt;             cur-&gt;prepareQuery("insert into images values (:image,:desc)");
 228&gt;             cur-&gt;inputBindBlob("image",imagedata,imagelength);
 229&gt;             cur-&gt;inputBindClob("desc",description,desclength);
 230&gt;             cur-&gt;executeQuery();
 231&gt;     
 232&gt;             **delete** cur;
 233&gt;             **delete** con;
 234&gt;     }
 235&gt;     
 236
 237Likewise, with Oracle 8i, you can retreive BLOB or CLOB data using defineOutputBindBlob(), defineOutputBindClob(), getOutputBind() and getOutputBindLength(). 
 238
 239&gt; 
 240&gt;     #include <sqlrelay sqlrclient.h="">
 241&gt;     
 242&gt;     main() {
 243&gt;     
 244&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 245&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 246&gt;     
 247&gt;             cur-&gt;prepareQuery("begin  select image into :image from images;  select description into :desc from images;  end;");
 248&gt;             cur-&gt;defineOutputBindBlob("image");
 249&gt;             cur-&gt;defineOutputBindClob("desc");
 250&gt;             cur-&gt;executeQuery();
 251&gt;     
 252&gt;             **char**    *image=cur-&gt;getOutputBind("image");
 253&gt;             **long**    imagelength=cur-&gt;getOutputBindLength("image");
 254&gt;     
 255&gt;             **char**    *desc=cur-&gt;getOutputBind("desc");
 256&gt;             **char**    *desclength=cur-&gt;getOutputBindLength("desc");
 257&gt;     
 258&gt;             con-&gt;endSession();
 259&gt;     
 260&gt;             ... do something with image and desc ...
 261&gt;     
 262&gt;             **delete** cur;
 263&gt;             **delete** con;
 264&gt;     }
 265&gt;     
 266
 267Sometimes it's convenient to bind a bunch of variables that may or may not actually be in the query. For example, if you are building a web based application, it may be easy to just bind all the form variables/values from the previous page, even though some of them don't appear in the query. Databases usually generate errors in this case. Calling validateBinds() just prior to calling executeQuery() causes the API to check the query for each bind variable before actually binding it, preventing those kinds of errors. There is a performance cost associated with calling validateBinds(). 
 268
 269Re-Binding and Re-Execution    
 270  
 271
 272
 273Another feature of the prepare/bind/execute paradigm is the ability to prepare, bind and execute a query once, then re-bind and re-execute the query over and over without re-preparing it. If your backend database natively supports this paradigm, you can reap a substantial performance improvement. 
 274
 275&gt; 
 276&gt;     #include <sqlrelay sqlrclient.h="">
 277&gt;     
 278&gt;     main() {
 279&gt;     
 280&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 281&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 282&gt;     
 283&gt;             cur-&gt;prepareQuery("select * from mytable where mycolumn&gt;:value");
 284&gt;             cur-&gt;inputBind("value",1);
 285&gt;             cur-&gt;executeQuery();
 286&gt;     
 287&gt;             ... process the result set ...
 288&gt;     
 289&gt;             cur-&gt;clearBinds();
 290&gt;             cur-&gt;inputBind("value",5);
 291&gt;             cur-&gt;executeQuery();
 292&gt;     
 293&gt;             ... process the result set ...
 294&gt;     
 295&gt;             cur-&gt;clearBinds();
 296&gt;             cur-&gt;inputBind("value",10);
 297&gt;             cur-&gt;executeQuery();
 298&gt;     
 299&gt;             ... process the result set ...
 300&gt;     
 301&gt;             **delete** cur;
 302&gt;             **delete** con;
 303&gt;     }
 304&gt;     
 305
 306Accessing Fields in the Result Set    
 307  
 308
 309
 310The rowCount(), colCount() and getField() methods are useful for processing result sets. 
 311
 312&gt; 
 313&gt;     #include <sqlrelay sqlrclient.h="">
 314&gt;     #include <iostream.h>
 315&gt;     
 316&gt;     main() {
 317&gt;     
 318&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 319&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 320&gt;     
 321&gt;             cur-&gt;sendQuery("select * from my_table");
 322&gt;             con-&gt;endSession();
 323&gt;     
 324&gt;             **for** ( **int** row=0; row<cur->rowCount(); row++) {
 325&gt;                     **for** ( **int** col=0; col<cur->colCount(); col++) {
 326&gt;                             cout &lt;&lt; cur-&gt;getField(row,col) &lt;&lt; ",";
 327&gt;                     }
 328&gt;                     cout &lt;&lt; endl;
 329&gt;             }
 330&gt;     
 331&gt;             **delete** cur;
 332&gt;             **delete** con;
 333&gt;     }
 334&gt;     
 335
 336The getField() method returns a string. If you would like to get a field as a long or double, you can use getFieldAsLong() and getFieldAsDouble(). 
 337
 338You can also use getRow() which returns a NULL-terminated array of the fields in the row. 
 339
 340&gt; 
 341&gt;     #include <sqlrelay sqlrclient.h="">
 342&gt;     #include <iostream.h>
 343&gt;     
 344&gt;     main() {
 345&gt;     
 346&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 347&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 348&gt;     
 349&gt;             cur-&gt;sendQuery("select * from my_table");
 350&gt;             con-&gt;endSession();
 351&gt;     
 352&gt;             **for** ( **int** row=0; row<cur->rowCount(); row++) {
 353&gt;                     **char**    **rowarray=cur-&gt;getRow(row);
 354&gt;                     **for** ( **int** col=0; col<cur->colCount(); col++) {
 355&gt;                             cout &lt;&lt; rowarray[col] &lt;&lt; ",";
 356&gt;                     }
 357&gt;                     cout &lt;&lt; endl;
 358&gt;             }
 359&gt;     
 360&gt;             **delete** cur;
 361&gt;             **delete** con;
 362&gt;     }
 363&gt;     
 364
 365The getField() and getRow() methods return NULL fields as empty strings. If you would like them to come back as NULL's instead, you can call the getNullsAsNulls() method. To revert to the default behavior, you can call getNullsAsEmptyStrings(). 
 366
 367If you want to access the result set, but don't care about the column information (column names, types or sizes) and don't mind getting fields by their numeric index instead of by name, you can call the dontGetColumnInfo() method prior to executing your query. This can result in a performance improvement, especially when many queries with small result sets are executed in rapid succession. You can call getColumnInfo() again later to turn off this feature. 
 368
 369Dealing With Large Result Sets    
 370  
 371
 372
 373SQL Relay normally buffers the entire result set. This can speed things up at the cost of memory. With large enough result sets, it makes sense to buffer the result set in chunks instead of all at once. 
 374
 375Use setResultSetBufferSize() to set the number of rows to buffer at a time. Calls to getRow() and getField() cause the chunk containing the requested field to be fetched. Rows in that chunk are accessible but rows before it are not. 
 376
 377For example, if you setResultSetBufferSize(5) and execute a query that returns 20 rows, rows 0-4 are available at once, then rows 5-9, then 10-14, then 15-19. When rows 5-9 are available, getField(0,0) will return NULL and getField(11,0) will cause rows 10-14 to be fetched and return the requested value. 
 378
 379When buffering the result set in chunks, don't end the session until after you're done with the result set. 
 380
 381If you call setResultSetBufferSize() and forget what you set it to, you can always call getResultSetBufferSize(). 
 382
 383When buffering a result set in chunks, the rowCount() method returns the number of rows returned so far. The firstRowIndex() method returns the index of the first row of the currently buffered chunk. 
 384
 385&gt; 
 386&gt;     #include <sqlrelay sqlrclient.h="">
 387&gt;     #include <iostream.h>
 388&gt;     
 389&gt;     main() {
 390&gt;     
 391&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 392&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 393&gt;     
 394&gt;             cur-&gt;setResultSetBufferSize(5);
 395&gt;     
 396&gt;             cur-&gt;sendQuery("select * from my_table");
 397&gt;     
 398&gt;             **int**     done=0;
 399&gt;             **int**     row=0;
 400&gt;             **char**    *field;
 401&gt;             **while** (!done) {
 402&gt;                     **for** ( **int** col=0; col<cur->colCount(); col++) {
 403&gt;                             **if** (field=cur-&gt;getField(row,col)) {
 404&gt;                                     cout &lt;&lt; field &lt;&lt; ",";
 405&gt;                             } **else** {
 406&gt;                                     done=1;
 407&gt;                             }
 408&gt;                     }
 409&gt;                     cout &lt;&lt; endl;
 410&gt;                     row++;
 411&gt;             }
 412&gt;     
 413&gt;             cur-&gt;sendQuery("select * from my_other_table");
 414&gt;     
 415&gt;             ... process this query's result set in chunks also ...
 416&gt;     
 417&gt;             cur-&gt;setResultSetBufferSize(0);
 418&gt;     
 419&gt;             cur-&gt;sendQuery("select * from my_third_table");
 420&gt;     
 421&gt;             ... process this query's result set all at once ...
 422&gt;     
 423&gt;             con-&gt;endSession();
 424&gt;     
 425&gt;             **delete** cur;
 426&gt;             **delete** con;
 427&gt;     }
 428&gt;     
 429
 430Cursors    
 431  
 432
 433
 434Cursors make it possible to execute queries while processing the result set of another query. You can select rows from a table in one query, then iterate through it's result set, inserting rows into another table, using only 1 database connection for both operations. 
 435
 436For example: 
 437
 438&gt; 
 439&gt;     #include <sqlrelay sqlrclient.h="">
 440&gt;     
 441&gt;     main() {
 442&gt;     
 443&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 444&gt;             sqlrcursor          *cursor1= **new** sqlrcursor(con);
 445&gt;             sqlrcursor          *cursor2= **new** sqlrcursor(con);
 446&gt;     
 447&gt;             cursor1-&gt;setResultSetBufferSize(10);
 448&gt;             cursor1-&gt;sendQuery("select * from my_huge_table");
 449&gt;     
 450&gt;             **int**     index=0;
 451&gt;             **while** (!cursor1-&gt;endOfResultSet()) {
 452&gt;                     cursor2-&gt;prepareQuery("insert into my_other_table values (:1,:2,:3)");
 453&gt;                     cursor2-&gt;inputBind("1",cursor1-&gt;getField(index,1));
 454&gt;                     cursor2-&gt;inputBind("2",cursor1-&gt;getField(index,2));
 455&gt;                     cursor2-&gt;inputBind("3",cursor1-&gt;getField(index,3));
 456&gt;                     cursor2-&gt;executeQuery();
 457&gt;             }
 458&gt;     
 459&gt;             **delete** cursor2;
 460&gt;             **delete** cursor1;
 461&gt;             **delete** con;
 462&gt;     }
 463&gt;     
 464
 465Prior to SQL Relay version 0.25, you would have had to buffer the first result set or use 2 database connections instead of just 1. 
 466
 467If you are using stored procedures with Oracle 8i or higher, a stored procedure can execute a query and return a cursor. A cursor bind variable can then retrieve that cursor. Your program can retrieve the result set from the cursor. All of this can be accomplished using defineOutputBindCursor(), getOutputBindCursor() and fetchFromOutputBindCursor(). 
 468
 469&gt; PL/SQL Procedure:   
 470&gt; 
 471&gt;     
 472&gt;     
 473&gt;     FUNCTION sp_mytable RETURN types.cursorType
 474&gt;     l_cursor types.cursorType;
 475&gt;     BEGIN
 476&gt;             OPEN l_cursor FOR **SELECT** * FROM mytable;
 477&gt;             RETURN l_cursor;
 478&gt;     END;
 479&gt;     
 480&gt; 
 481&gt;   
 482&gt;  Program code: 
 483&gt;     
 484&gt;     
 485&gt;     #include <sqlrelay sqlrclient.h="">
 486&gt;     #include <iostream.h>
 487&gt;     
 488&gt;     main() {
 489&gt;     
 490&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 491&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 492&gt;     
 493&gt;             cur-&gt;prepareQuery("begin  :curs:=sp_mytable; end;");
 494&gt;             cur-&gt;defineOutputBindCursor("curs");
 495&gt;             cur-&gt;executeQuery();
 496&gt;     
 497&gt;             sqlrcursor      *bindcur=cur-&gt;getOutputBindCursor("curs");
 498&gt;             bindcur-&gt;fetchFromBindCursor();
 499&gt;     
 500&gt;             // print fields from table
 501&gt;             **for** ( **int** i=0; i<bindcur->rowCount(); i++) {
 502&gt;                     **for** ( **int** j=0; j<bindcur->colCount(); j++) {
 503&gt;                             cout &lt;&lt; bindcur-&gt;getField(i,j) &lt;&lt; ", ";
 504&gt;                     }
 505&gt;                     cout &lt;&lt; endl;
 506&gt;             }
 507&gt;     
 508&gt;             **delete** bindcur;
 509&gt;     
 510&gt;             **delete** cur;
 511&gt;             **delete** con;
 512&gt;     }
 513&gt;     
 514
 515The number of cursors simultaneously available per-connection is set at compile time and defaults to 5. 
 516
 517Getting Column Information    
 518  
 519
 520
 521For each column, the API supports getting the name, type and length of each field. All databases support these attributes. The API also supports getting the precision, scale, length of the longest field, and whether the column is nullable, the primary key, unique, part of a key, unsigned, zero-filled, binary, or an auto-incrementing field. However, not all databases support these attributes. If a database doesn't support an attribute, it is always returned as false. 
 522
 523&gt; 
 524&gt;     #include <sqlrelay sqlrclient.h="">
 525&gt;     #include <iostream.h>
 526&gt;     
 527&gt;     main() {
 528&gt;     
 529&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 530&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 531&gt;     
 532&gt;             cur-&gt;sendQuery("select * from my_table");
 533&gt;             con-&gt;endSession();
 534&gt;     
 535&gt;             **for** ( **int** i=0; i<cur->colCount(); i++) {
 536&gt;                     cout &lt;&lt; "Name:          " &lt;&lt; cur-&gt;getColumnName(i) &lt;&lt; endl;
 537&gt;                     cout &lt;&lt; "Type:          " &lt;&lt; cur-&gt;getColumnType(i) &lt;&lt; endl;
 538&gt;                     cout &lt;&lt; "Length:        " &lt;&lt; cur-&gt;getColumnLength(i) &lt;&lt; endl;
 539&gt;                     cout &lt;&lt; "Precision:     " &lt;&lt; cur-&gt;getColumnPrecision(i) &lt;&lt; endl;
 540&gt;                     cout &lt;&lt; "Scale:         " &lt;&lt; cur-&gt;getColumnScale(i) &lt;&lt; endl;
 541&gt;                     cout &lt;&lt; "Longest Field: " &lt;&lt; cur-&gt;getLongest(i) &lt;&lt; endl;
 542&gt;                     cout &lt;&lt; "Nullable:      " &lt;&lt; cur-&gt;getColumnIsNullable(i) &lt;&lt; endl;
 543&gt;                     cout &lt;&lt; "Primary Key:   " &lt;&lt; cur-&gt;getColumnIsPrimaryKey(i) &lt;&lt; endl;
 544&gt;                     cout &lt;&lt; "Unique:        " &lt;&lt; cur-&gt;getColumnIsUnique(i) &lt;&lt; endl;
 545&gt;                     cout &lt;&lt; "Part of Key:   " &lt;&lt; cur-&gt;getColumnIsPartOfKey(i) &lt;&lt; endl;
 546&gt;                     cout &lt;&lt; "Unsigned:      " &lt;&lt; cur-&gt;getColumnIsUnsigned(i) &lt;&lt; endl;
 547&gt;                     cout &lt;&lt; "Zero Filled:   " &lt;&lt; cur-&gt;getColumnIsZeroFilled(i) &lt;&lt; endl;
 548&gt;                     cout &lt;&lt; "Binary:        " &lt;&lt; cur-&gt;getColumnIsBinary(i) &lt;&lt; endl;
 549&gt;                     cout &lt;&lt; "Auto Increment:" &lt;&lt; cur-&gt;getColumnIsAutoIncrement(i) &lt;&lt; endl;
 550&gt;                     cout &lt;&lt; endl;
 551&gt;             }
 552&gt;     
 553&gt;             **delete** cur;
 554&gt;             **delete** con;
 555&gt;     }
 556&gt;     
 557
 558Some databases force column names to upper case, others force column names to lower case, and others still support mixed-case column names. Sometimes, when migrating between databases, you can run into trouble. You can use upperCaseColumnNames() and lowerCaseColumnNames() to cause column names to be converted to upper or lower case, or you can use mixedCaseColumnNames() to cause column names to be returned in the same case as they are defined in the database. 
 559
 560&gt; 
 561&gt;     #include <sqlrelay sqlrclient.h="">
 562&gt;     #include <iostream.h>
 563&gt;     
 564&gt;     main() {
 565&gt;     
 566&gt;             sqlrconnection      *con= **new** sqlrconnection("host",9000,"","user","password",0,1);
 567&gt;             sqlrcursor          *cur= **new** sqlrcursor(con);
 568&gt;     
 569&gt;             // column names will be forced to upper case
 570&gt;             cur-&gt;upperCaseColumnNames();
 571&gt;             cur-&gt;sendQuery("select * from my_table");
 572&gt;             con-&gt;endSession();
 573&gt;     
 574&gt;             **for** ( **int** i=0; i<cur->colCount(); i++) {
 575&gt;                     cout &lt;&lt; "Name:          " &lt;&lt; cur-&gt;getColumnName(i) &lt;&lt; endl;
 576&gt;                     cout &lt;&lt; endl;
 577&gt;             }
 578&gt;     
 579&gt;             // column names will be forced to lower case
 580&gt;             cur-&gt;lowerCaseColumnNames();
 581&gt;             cur-&gt;sendQuery("select * from my_table");
 582&gt;             con-&gt;endSession();
 583&gt;     
 584&gt;             **for** ( **int** i=0; i<cur->colCount(); i++) {
 585&gt;                     cout &lt;&lt; "Name:          " &lt;&lt; cur-&gt;getColumnName(i) &lt;&lt; endl;
 586&gt;                     cout &lt;&lt; endl;
 587&gt;             }
 588&gt;     
 589&gt;             // column names will be the same as they are in the database
 590&gt;             cur-&gt;mixedCaseColumnNames();
 591&gt;             cur-&gt;sendQuery("select * from my_table");
 592&gt;             con-&gt;endSession();
 593&gt;     
 594&gt;             **for** ( **int** i=0; i<cur->colCount(); i++) {
 595&gt;                     cout &lt;&lt; "Name:          " &lt;&lt; cur-&gt;getColumnName(i) &lt;&lt; endl;
 596&gt;                     cout &lt;&lt; endl;
 597&gt;             }
 598&gt;     
 599&gt;             **delete** cur;
 600&gt;             **delete** con;
 601&gt;     }
 602&gt;     
 603
 604Stored Procedures    
 605  
 606
 607
 608Many databases support stored procedures. Stored procedures are sets of queries and procedural code that are executed inside of the database itself. For example, a stored procedure may select rows from one table, iterate through the result set and, based on the values in each row, insert, update or delete rows in other tables. A client program could do this as well, but a stored procedure is generally more efficient because queries and result sets don't have to be sent back and forth between the client and database. Also, stored procedures are generally stored in the database in a compiled state, while queries may have to be re-parsed and re-compiled each time they are sent. 
 609
 610While many databases support stored procedures. The syntax for creating and executing stored procedures varies greatly between databases. 
 611
 612SQL Relay supports stored procedures for most databases, but there are some caveats. Stored procedures are not currently supported when using FreeTDS against Sybase or Microsoft SQL Server. Blob/Clob bind variables are only supported in Oracle 8i or higher. Sybase stored procedures must use varchar output parameters. 
 613
 614Stored procedures typically take input paramters from client programs through input bind variables and return values back to client programs either through bind variables or result sets. Stored procedures can be broken down into several categories, based on the values that they return. Some stored procedures don't return any values, some return a single value, some return multiple values and some return entire result sets. 
 615
 616No Values    
 617
 618
 619Some stored procedures don't return any values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. 
 620
 621Oracle    
 622
 623
 624To create the stored procedure, run a query like the following. 
 625
 626&gt; 
 627&gt;     **create** procedure testproc(in1 **in** **number** , in2 **in** **number** , in3 **in** **varchar2** ) is
 628&gt;     begin
 629&gt;             **insert** into mytable values (in1,in2,in3);
 630&gt;     end;
 631&gt;     
 632
 633To execute the stored procedure from an SQL Relay program, use code like the following. 
 634
 635&gt; 
 636&gt;     cur-&gt;prepareQuery("begin testproc(:in1,:in2,:in3); end;");
 637&gt;     cur-&gt;inputBind("in1",1);
 638&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 639&gt;     cur-&gt;inputBind("in3","hello");
 640&gt;     cur-&gt;executeQuery();
 641&gt;     
 642
 643To drop the stored procedure, run a query like the following. 
 644
 645&gt; 
 646&gt;     **drop** procedure testproc
 647&gt;     
 648
 649Sybase and Microsoft SQL Server    
 650
 651
 652To create the stored procedure, run a query like the following. 
 653
 654&gt; 
 655&gt;     **create** procedure testproc @in1 int, @in2 **float** , @in3 **varchar** (20) as
 656&gt;             **insert** into mytable values (@in1,@in2,@in3)
 657&gt;     
 658
 659To execute the stored procedure from an SQL Relay program, use code like the following. 
 660
 661&gt; 
 662&gt;     cur-&gt;prepareQuery("exec testproc");
 663&gt;     cur-&gt;inputBind("in1",1);
 664&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 665&gt;     cur-&gt;inputBind("in3","hello");
 666&gt;     cur-&gt;executeQuery();
 667&gt;     
 668
 669To drop the stored procedure, run a query like the following. 
 670
 671&gt; 
 672&gt;     **drop** procedure testproc
 673&gt;     
 674
 675Interbase and Firebird    
 676
 677
 678To create the stored procedure, run a query like the following. 
 679
 680&gt; 
 681&gt;     **create** procedure testproc(in1 **integer** , in2 **float** , in3 **varchar** (20)) as
 682&gt;     begin
 683&gt;             **insert** into mytable values (in1,in2,in3);
 684&gt;             suspend;
 685&gt;     end;
 686&gt;     
 687
 688To execute the stored procedure from an SQL Relay program, use code like the following. 
 689
 690&gt; 
 691&gt;     cur-&gt;prepareQuery("execute procedure testproc ?, ?, ?");
 692&gt;     cur-&gt;inputBind("1",1);
 693&gt;     cur-&gt;inputBind("2",1.1,2,1);
 694&gt;     cur-&gt;inputBind("3","hello");
 695&gt;     cur-&gt;executeQuery();
 696&gt;     
 697
 698To drop the stored procedure, run a query like the following. 
 699
 700&gt; 
 701&gt;     **drop** procedure testproc
 702&gt;     
 703
 704DB2    
 705
 706
 707To create the stored procedure, run a query like the following. 
 708
 709&gt; 
 710&gt;     **create** procedure testproc( **in** in1 int, **in** in2 double, **in** in3 **varchar** (20)) language sql
 711&gt;     begin
 712&gt;             **insert** into mytable values (in1,in2,in3);
 713&gt;     end;
 714&gt;     
 715
 716To execute the stored procedure from an SQL Relay program, use code like the following. 
 717
 718&gt; 
 719&gt;     cur-&gt;prepareQuery("call testproc(?,?,?)");
 720&gt;     cur-&gt;inputBind("1",1);
 721&gt;     cur-&gt;inputBind("2",1.1,2,1);
 722&gt;     cur-&gt;inputBind("3","hello");
 723&gt;     cur-&gt;executeQuery();
 724&gt;     
 725
 726To drop the stored procedure, run a query like the following. 
 727
 728&gt; 
 729&gt;     **drop** procedure testproc
 730&gt;     
 731
 732Postgresql    
 733
 734
 735To create the stored procedure, run a query like the following. 
 736
 737&gt; 
 738&gt;     **create** function testproc(int, **float** , **varchar** (20)) returns void as '
 739&gt;     begin
 740&gt;             **insert** into mytable values ($1,$2,$3);
 741&gt;             return;
 742&gt;     end;' language plpgsql
 743&gt;     
 744
 745To execute the stored procedure from an SQL Relay program, use code like the following. 
 746
 747&gt; 
 748&gt;     cur-&gt;prepareQuery("select testproc(:in1,:in2,:in3)");
 749&gt;     cur-&gt;inputBind("in1",1);
 750&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 751&gt;     cur-&gt;inputBind("in3","hello");
 752&gt;     cur-&gt;executeQuery();
 753&gt;     
 754
 755To drop the stored procedure, run a query like the following. 
 756
 757&gt; 
 758&gt;     **drop** procedure testproc
 759&gt;     
 760
 761  
 762Single Values    
 763
 764
 765Some stored procedures return single values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. 
 766
 767Oracle    
 768
 769
 770In Oracle, stored procedures can return values through output parameters or as return values of the procedure itself. 
 771
 772Here is an example where the procedure itself returns a value. Note that Oracle calls these functions. 
 773
 774To create the stored procedure, run a query like the following. 
 775
 776&gt; 
 777&gt;     **create** function testproc(in1 **in** **number** , in2 **in** **number** , in3 **in** **varchar2** ) returns **number** is
 778&gt;     begin
 779&gt;             return in1;
 780&gt;     end;
 781&gt;     
 782
 783To execute the stored procedure from an SQL Relay program, use code like the following. 
 784
 785&gt; 
 786&gt;     cur-&gt;prepareQuery("select testproc(:in1,:in2,:in3) from dual");
 787&gt;     cur-&gt;inputBind("in1",1);
 788&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 789&gt;     cur-&gt;inputBind("in3","hello");
 790&gt;     cur-&gt;executeQuery();
 791&gt;     **char**    *result=cur-&gt;getField(0,0);
 792&gt;     
 793
 794To drop the stored procedure, run a query like the following. 
 795
 796&gt; 
 797&gt;     **drop** function testproc
 798&gt;     
 799
 800Here is an example where the value is returned through an output parameter. 
 801
 802To create the stored procedure, run a query like the following. 
 803
 804&gt; 
 805&gt;     **create** procedure testproc(in1 **in** **number** , in2 **in** **number** , in3 **in** **varchar2** , out1 **out** **number** ) as
 806&gt;     begin
 807&gt;             out1:=in1;
 808&gt;     end;
 809&gt;     
 810
 811To execute the stored procedure from an SQL Relay program, use code like the following. 
 812
 813&gt; 
 814&gt;     cur-&gt;prepareQuery("begin testproc(:in1,:in2,:in3,:out1); end;");
 815&gt;     cur-&gt;inputBind("in1",1);
 816&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 817&gt;     cur-&gt;inputBind("in3","hello");
 818&gt;     cur-&gt;defineOutputBind("out1",20);
 819&gt;     cur-&gt;executeQuery();
 820&gt;     **char**    *result=cur-&gt;getOutputBind("out1");
 821&gt;     
 822
 823To drop the stored procedure, run a query like the following. 
 824
 825&gt; 
 826&gt;     **drop** procedure testproc
 827&gt;     
 828
 829Sybase and Microsoft SQL Server    
 830
 831
 832In Sybase and Microsoft SQL Server, stored procedures return values through output parameters rather than as return values of the procedure itself. 
 833
 834To create the stored procedure, run a query like the following. 
 835
 836&gt; 
 837&gt;     **create** procedure testproc @in1 int, @in2 **float** , @in3 **varchar** (20), @out1 int output as
 838&gt;             **select** @out1=convert( **varchar** (20),@in1)
 839&gt;     
 840
 841To execute the stored procedure from an SQL Relay program, use code like the following. 
 842
 843&gt; 
 844&gt;     cur-&gt;prepareQuery("exec testproc");
 845&gt;     cur-&gt;inputBind("in1",1);
 846&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 847&gt;     cur-&gt;inputBind("in3","hello");
 848&gt;     cur-&gt;defineOutputBind("out1",20);
 849&gt;     cur-&gt;executeQuery();
 850&gt;     **char**    *result=cur-&gt;getOutputBind("out1");
 851&gt;     
 852
 853To drop the stored procedure, run a query like the following. 
 854
 855&gt; 
 856&gt;     **drop** procedure testproc
 857&gt;     
 858
 859Interbase and Firebird    
 860
 861
 862To create the stored procedure, run a query like the following. 
 863
 864&gt; 
 865&gt;     **create** procedure testproc(in1 **integer** , in2 **float** , in3 **varchar** (20)) returns (out1 **integer** ) as
 866&gt;     begin
 867&gt;             out1=in1;
 868&gt;             suspend;
 869&gt;     end;
 870&gt;     
 871
 872To execute the stored procedure from an SQL Relay program, use code like the following. 
 873
 874&gt; 
 875&gt;     cur-&gt;prepareQuery("select * from testproc(?,?,?)");
 876&gt;     cur-&gt;inputBind("1",1);
 877&gt;     cur-&gt;inputBind("2",1.1,2,1);
 878&gt;     cur-&gt;inputBind("3","hello");
 879&gt;     cur-&gt;executeQuery();
 880&gt;     **char**    *result=cur-&gt;getField(0,0);
 881&gt;     
 882
 883Alternatively, you can run a query like the following and receive the result using an output bind variable. Note that in Interbase/Firebird, input and output bind variable indices are distict from one another. The index of the output bind variable is 1 rather than 4, even though there were 3 input bind variables. 
 884
 885&gt; 
 886&gt;     cur-&gt;prepareQuery("execute procedure testproc ?, ?, ?");
 887&gt;     cur-&gt;inputBind("1",1);
 888&gt;     cur-&gt;inputBind("2",1.1,2,1);
 889&gt;     cur-&gt;inputBind("3","hello");
 890&gt;     cur-&gt;defineOutputBind("1",20);
 891&gt;     cur-&gt;executeQuery();
 892&gt;     **char**    *result=cur-&gt;getOutputBind("1");
 893&gt;     
 894
 895To drop the stored procedure, run a query like the following. 
 896
 897&gt; 
 898&gt;     **drop** procedure testproc
 899&gt;     
 900
 901DB2    
 902
 903
 904In DB2, stored procedures return values through output parameters rather than as return values of the procedure itself. 
 905
 906To create the stored procedure, run a query like the following. 
 907
 908&gt; 
 909&gt;     **create** procedure testproc( **in** in1 int, **in** in2 double, **in** in3 **varchar** (20), **out** out1 int) language sql
 910&gt;     begin
 911&gt;             **set** out1 = in1;
 912&gt;     end
 913&gt;     
 914
 915To execute the stored procedure from an SQL Relay program, use code like the following. 
 916
 917&gt; 
 918&gt;     cur-&gt;prepareQuery("call testproc(?,?,?,?)");
 919&gt;     cur-&gt;inputBind("1",1);
 920&gt;     cur-&gt;inputBind("2",1.1,2,1);
 921&gt;     cur-&gt;inputBind("3","hello");
 922&gt;     cur-&gt;defineOutputBind("4",25);
 923&gt;     cur-&gt;executeQuery();
 924&gt;     **char**    *result=cur-&gt;getOutputBind("4");
 925&gt;     
 926
 927To drop the stored procedure, run a query like the following. 
 928
 929&gt; 
 930&gt;     **drop** procedure testproc
 931&gt;     
 932
 933Postgresql    
 934
 935
 936To create the stored procedure, run a query like the following. 
 937
 938&gt; 
 939&gt;     **create** function testfunc(int, **float** , **char** (20)) returns int as '
 940&gt;     declare
 941&gt;             in1 int;
 942&gt;             in2 **float** ;
 943&gt;             in3 **char** (20);
 944&gt;     begin
 945&gt;             in1:=$1;
 946&gt;             return;
 947&gt;     end;
 948&gt;     ' language plpgsql
 949&gt;     
 950
 951To execute the stored procedure from an SQL Relay program, use code like the following. 
 952
 953&gt; 
 954&gt;     cur-&gt;prepareQuery("select * from testfunc(:in1,:in2,:in3)");
 955&gt;     cur-&gt;inputBind("in1",1);
 956&gt;     cur-&gt;inputBind("in2",1.1,4,2);
 957&gt;     cur-&gt;inputBind("in3","hello");
 958&gt;     cur-&gt;executeQuery();
 959&gt;     **char**    *result=cur-&gt;getField(0,0);
 960&gt;     
 961
 962To drop the stored procedure, run a query like the following. 
 963
 964&gt; 
 965&gt;     **drop** function testfunc(int, **float** , **char** (20))
 966&gt;     
 967
 968  
 969Multiple Values    
 970
 971
 972Some stored procedures return multiple values. Below are examples, illustrating how to create, execute and drop this kind of stored procedure for each database that SQL Relay supports. 
 973
 974Oracle    
 975
 976
 977In Oracle, stored procedures can return values through output parameters or as return values of the procedure itself. If a procedure needs to return multiple values, it can return one of them as the return value of the procedure itself, but the rest must be returned through output parameters. 
 978
 979To create the stored procedure, run a query like the following. 
 980
 981&gt; 
 982&gt;     **create** procedure testproc(in1 **in** **number** , in2 **in** **number** , in3 **in** **varchar2** , out1 **out** **number** , out2 **out** **number** , out3 **out** **varchar2** ) is
 983&gt;     begin
 984&gt;             out1:=in1;
 985&gt;             out2:=in2;
 986&gt;             out3:=in3;
 987&gt;     end;
 988&gt;     
 989
 990To execute the stored procedure from an SQL Relay program, use code like the following. 
 991
 992&gt; 
 993&gt;     cur-&gt;prepareQuery("begin testproc(:in1,:in2,:in3,:out1,:out2,:out3); end;");
 994&gt;     cur-&gt;inputBind("in1",1);
 995&gt;     cur-&gt;inputBind("in2",1.1,2,1);
 996&gt;     cur-&gt;inputBind("in3","hello");
 997&gt;     cur-&gt;defineOutputBind("out1",20);
 998&gt;     cur-&gt;defineOutputBind("out2",20);
 999&gt;     cur-&gt;defineOutputBind("out3",20);
1000&gt;     cur-&gt;executeQuery();
1001&gt;     **char**    *out1=cur-&gt;getOutputBind("out1");
1002&gt;     **char**    *out2=cur-&gt;getOutputBind("out2");
1003&gt;     **char**    *out3=cu</cur-></cur-></cur-></iostream.h></sqlrelay></cur-></iostream.h></sqlrelay></bindcur-></bindcur-></iostream.h></sqlrelay></sqlrelay></cur-></iostream.h></sqlrelay></cur-></cur-></iostream.h></sqlrelay></cur-></cur-></iostream.h></sqlrelay></sqlrelay></sqlrelay></sqlrelay></sqlrelay></sqlrelay></iostream.h></sqlrelay></sqlrelay></sqlrelay></sqlrelay>