Re: Newbie odbc/oracle-procedure-questions

Harp wrote:
> I have the following questions:
> My program should process and access sql-statements being sent by a
> user dynamically. There are different dbmss - I have to use odbc.
> If I have to call a procedure dynamically. How can the name of the
> procedure here test
> lstrcpy((char *) pSqlStmt, "{CALL test(?)}");//SQLCHAR SqlStmt[50];
>
> be represented with a variable like:
> string name = "test";
> lstrcpy((char *) pSqlStmt,"{CALL name(?)}");//wrong.
> It will not work, but how can I improve it?
>
> Secondly, how I can pass parameters to SQLBindParameter(...)and
> SQLBindCol()(this will not be a problem if I know that of
> SQLBindParameter)dynamically. I don't know the value of parameters at
> compilation time. The count of parameters for a special call is
unknown
> too.
> The use of these do not give the right result.
> rc = SQLProcedureColumns(hstmt,NULL,0,(unsigned char
> *)colSchemaNamePattern,len,(unsigned char *)"test",len,(char
> *)colNamePattern,len);
> SQLSMALLINT numCols,colNameLen;
> SQLCHAR colName[50];
> rc = SQLNumResultCols(hstmt,&numCols);
> //numCols gives for e.g. 1(which is wrong) and as soon as I proceed
to
> if(rc == ...), it turns to 19. I do not know why, although the
> procedure has 4 columns.
>
> if(rc == SQL_SUCCESS){
> for(int i = 1; i < numCols; i++){
> rc = SQLColAttribute(hstmt, i, SQL_DESC_NAME, colName, sizeof
> (colName), &colNameLen,NULL);
> }
> }
> I tried these to be able to know the no. of parameters for calling
the
> procedure, binding the parameters and the columns.
> Any ideas?
> Thanks,
> Harp

If you don't know the procedure name or parameters at design time, then you have to do what the compiler does at compile time at runtime. i.e. Parse the statement, check the syntax, semantics, grammer etc etc. How does the user build his/her query? Just type it in a data field and hit execute? Have you thought about doing all this server side (I am not sure if it will hang togather but) e.g. create a procedure and send the whole thing as a string. Then parse, check syntax etc via PLSQL supplied packages (dbms_sql) and data dictionary.

Maybe someone else have a better suggestion then this but to me the design seems just too dynamic for runtime.

Regards
/Rauf Received on Fri Feb 18 2005 - 08:03:18 CST