However, 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.

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

If your call to sqlrcur_sendQuery() or sqlrcur_sendFileQuery() returns a 0, the query failed. You can find out why by calling sqlrcur_errorMessage().

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

main() {

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

        if (!sqlrcur_sendQuery(cur,"select * from my_nonexistant_table")) {
                printf("%s\n",sqlrcur_errorMessage(cur));
        }

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

Programs 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 functions for making substitutions and binds in those queries.

For a detailed discussion of substitutions and binds, see this document.

Rather than just calling sqlrcur_sendFileQuery() you call sqlrcur_prepareFileQuery(), one or more of sqlrcur_subString(), sqlrcur_subLong() or sqlrcur_subDouble, one or more of sqlrcur_inputBindString(), sqlrcur_inputBindLong() or sqlr_inputBindDouble() and sqlrcur_executeQuery().

/usr/local/myprogram/sql/myquery.sql:

	select * from mytable $(whereclause)

Program code:

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

main() {

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

        sqlrcur_prepareFileQuery(cur,"/usr/local/myprogram/sql","myquery.sql");
        sqlrcur_subString(cur,"whereclause","where col1=:value1");
        sqlrcur_inputBindString(cur,"value1","true");
        sqlrcur_executeQuery(cur);

        ... process the result set ...

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

If 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 sqlrcur_defineOutputBind() and sqlrcur_getOutputBind() methods.

PL/SQL Procedure:

FUNCTION sp_mytable RETURN types.cursorType
l_cursor types.cursorType;
BEGIN
        OPEN l_cursor FOR SELECT * FROM mytable;
        RETURN l_cursor;
END;

Program code:

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

main() {

        int        result;

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

        sqlrcur_prepareQuery(cur,"begin  :result:=addTwoNumbers(:num1,:num2);  end;");
        sqlrcur_inputBindLong(cur,"num1",10);
        sqlrcur_inputBindLong(cur,"num2",20);
        sqlrcur_defineOutputBind(cur,"result",100);
        sqlrcur_executeQuery(cur);
        result=atoi(sqlrcur_getOutputBind(cur,"result"));
        sqlrcon_endSession(con);

        ... do something with the result ...

        sqlrcur_free(cur);
        sqlrcon_free(con);
}

The sqlrcur_getOutputBind() function returns a NULL value as an empty string. If you would it to come back as a NULL instead, you can call the sqlrcur_getNullsAsNulls() method. To revert to the default behavior, you can call sqlrcur_getNullsAsEmptyStrings().

The sqlrcur_getOutputBind() function returns a string, if you would like to get the value as a long or double, you can use sqlrcur_getOutputBindAsLong() or sqlrcur_getOutputBindAsDouble().

If you are using Oracle 8i or higher, you can insert data into BLOB and CLOB columns using the inputBindBlob(), inputBindClob() methods.

If you are curious how many bind variables have been declared in a query, you can call sqlrcur_countBindVariables() after preparing the query.

#include <sqlrelay/sqlrclientwrapper.h>

main() {

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

        sqlrcur_executeQuery(cur,"create table images (image blob, description clob)");

        unsigned char   imagedata[40000];
        unsigned long   imagelength;

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