The sqlrcur_getFieldByIndex(), sqlrcur_getFieldByName() and sqlrcur_getRow() functions return NULL fields as empty strings. If you would like them to come back as NULL''''s instead, you can call the sqlrcur_getNullsAsNulls() method. To revert to the default behavior, you can call sqlrcur_getNullsAsEmptyStrings().

If 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 sqlrcur_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 sqlrcur_getColumnInfo() again later to turn off this feature.

SQL 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.

Use sqlrcur_setResultSetBufferSize() to set the number of rows to buffer at a time. Calls to sqlrcur_getRow(), sqlrcur_getFieldByIndex() and sqlrcur_getFieldByName() cause the chunk containing the requested field to be fetched. Rows in that chunk are accessible but rows before it are not.

For 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, getFieldByIndex(0,0) will return NULL and getFieldByIndex(11,0) will cause rows 10-14 to be fetched and return the requested value.

When buffering the result set in chunks, don''''t end the session until after you''''re done with the result set.

If you call sqlrcur_setResultSetBufferSize() and forget what you set it to, you can always call sqlrcur_getResultSetBufferSize().

When buffering a result set in chunks, the sqlrcur_rowCount() method returns the number of rows returned so far. The sqlrcur_firstRowIndex() method returns the index of the first row of the currently buffered chunk.

#include <sqlrelay/sqlrclientwrapper.h>
#include <stdio.h>

main() {

        int     done=0;
        int     row=0;
        int     col;
        char    *field;

        sqlrcon      con=sqlrcon_alloc("host",9000,"","user","password",0,1);
        sqlrcur      cur=sqlrcur_alloc(con);

        sqlrcur_setResultSetBufferSize(cur,5);

        sqlrcur_sendQuery(cur,"select * from my_table");

        while (!done) {
                for (col=0; col<sqlrcur_colCount(cur); col++) {
                        if (field=sqlrcur_getFieldByIndex(cur,row,col)) {
                                printf("%s,",field);
                        } else {
                                done=1;
                        }
                }
                printf("\n");
                row++;
        }

        sqlrcur_sendQuery(cur,"select * from my_other_table");

        ... process this query''''s result set in chunks also ...

        sqlrcur_setResultSetBufferSize(cur,0);

        sqlrcur_sendQuery(cur,"select * from my_third_table");

        ... process this query''''s result set all at once ...

        sqlrcon_endSession(con);

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

Cursors 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.

For example:

#include <sqlrelay/sqlrclientwrapper.h>

main() {

        sqlrcon      con;
        sqlrcur      cursor1;
        sqlrcur      cursor2;
        int          index;

        con=new sqlrcon_alloc("host",9000,"","user","password",0,1);
        cursor1=new sqlrcur_alloc(con);
        cursor2=new sqlrcur_alloc(con);

        sqlrcur_setResultSetBufferSize(cursor1,10);
        sqlrcur_sendQuery(cursor1,"select * from my_huge_table");

        index=0;
        while (!sqlrcur_endOfResultSet(cursor1)) {
                sqlrcur_prepareQuery(cursor2,"insert into my_other_table values (:1,:2,:3)");
                sqlrcur_inputBindString(cursor2,"1",sqlrcur_getFieldByIndex(cursor1,index,1));
                sqlrcur_inputBindString(cursor2,"2",sqlrcur_getFieldByIndex(cursor1,index,2));
                sqlrcur_inputBindString(cursor2,"3",sqlrcur_getFieldByIndex(cursor1,index,3));
                sqlrcur_executeQuery(cursor2);
        }

        sqlrcur_free(cursor2);
        sqlrcur_free(cursor1);
        sqlrcon_free(con);
}

Prior 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.

If 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()

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10]  ...  下一页 >>