How to avoid a database hit when instantiating a RWDBStoredProc

Article ID: 988
Last updated: 02 Feb, 2008
Article ID: 988
Last updated: 02 Feb, 2008
Revision: 1
Views: 17606
Posted: 23 Nov, 1998
by Dean J.
Updated: 02 Feb, 2008
by Dean J.
Problem


I want to avoid any unnecessary database hits so I create a RWDBStoredProc with a RWDBSchema. When I try to use this RWDBStoredProc I get an error message from the server like the one below or similar.
[VENDORLIB] Vendor Library Error: Unknown fixed-length datatype encountered.


Cause


The RWDBSchema supplied to the constructor of the RWDBStoredProc must contain a RWDBColumn for each parameter and the nativeType and storageLength of the parameter must be specified.


Action


Always supply the nativeType and storageLength parameters to the RWDBSchema::appendColumn() method when constucting a schema to pass as an argument to RWDBDatabase::storedProc.The following code demonstrates this for two different stored procedures with Sybase.
#include  #ifdef RW_WRAP_C extern "C" { #endif #include  #include  #ifdef RW_WRAP_C } #endif #include  #include  #include   void myError(const RWDBStatus& st) {   cout << st.message() << endl;   cout << st.vendorError1() << endl;   cout << st.vendorMessage1() << endl;   cout << st.vendorError2() << endl;   cout << st.vendorMessage2() << endl; }   void main() {   RWDBManager::setErrorHandler(myError);  // FILL IN PARAMETERS!!   RWDBDatabase db = RWDBManager::database();

if (db.isValid() )
cout << "db is valid" << endl;
else
return;

RWDBTracer& tr = db.tracer();
tr.setOn(RWDBTracer::SQL);
tr.stream(cout);
RWDBConnection conn = db.connection();
if(conn.isValid())
cout << "connection is OK" << endl;
else
return;

RWDBSchema schema;

// Either form of appendColumn will work fine, it isn't necessary to specify the column type
// schema.appendColumn("@NAME", RWDBValue::String, 32,47,0,0,FALSE,RWDBColumn::inParameter );
schema.appendColumn("@NAME", RWDBValue::String, 32,SYBCHAR,0,0);

// schema.appendColumn("@EXT", RWDBValue::Long, 4, 56, 0, 0, FALSE,RWDBColumn::outParameter );
schema.appendColumn("@EXT", RWDBValue::Long, 4, SYBINT4, 0, 0);

RWDBStoredProc proc = db.storedProc("GET_EXT", conn, schema);
RWCString str("janet");
int ext;
proc["@NAME"] << str;
proc["@EXT"] << &ext;
proc.execute(conn);
proc.fetchReturnParams();
cout << "ext is:" << ext << endl;
RWDBSchema schemaA;
schemaA.appendColumn("@MYINT", RWDBValue::Long, 4, 56, 0, 0);
RWDBStoredProc rspA = db.storedProc("TEST_SELECT_INT", conn, schemaA);
int iA = -1;
rspA << &iA;
rspA.execute(conn);
rspA.fetchReturnParams();
cout << "int is:" << iA << endl;

/*
this section shows an example of what I can do to
determine the values for the columns needed above. As
long as the stored procedure exists in the database
this approach will work. However, I would not include
this code in my release code as it requires a database
hit and that's what we want to avoid in our release code. */

RWDBStoredProc info = db.storedProc("GET_EXT");
RWDBSchema infoschema = info.params();

for (size_t i = 0; i < infoschema.entries(); ++i) {
RWDBColumn col = infoschema.column(i);
cout << "name:" << col.name() << endl
<< "storageLength:" << col.storageLength() << endl
<< "nativeType:" << col.nativeType() << endl
<< "precision:" << col.precision() << endl
<< "scale:" << col.scale() << endl
<< "nullAllowed:" << (col.nullAllowed() ? "TRUE" : "FALSE") << endl
<< "RWDBColumn::ParamType:";
switch(col.paramType()) {
case RWDBColumn::notAParameter:
cout << "RWDBColumn::notAParameter";
case RWDBColumn::inParameter:
cout << "RWDBColumn::inParameter";
case RWDBColumn::outParameter:
cout << "RWDBColumn::outParameter";
case RWDBColumn::inOutParameter:
cout << "RWDBColumn::inOutParameter";
default:
cout << "unable to determine parameter type";
}
cout << endl;
}
}

This article was:   Helpful | Not helpful
Report an issue
Article ID: 988
Last updated: 02 Feb, 2008
Revision: 1
Views: 17606
Posted: 23 Nov, 1998 by Dean J.
Updated: 02 Feb, 2008 by Dean J.

Others in this category