Re: Weird method 4 ProC error ... please help...
Date: 1996/11/27
Message-ID: <329cb2ba.29257800_at_dcsun4>
You use sqlald to allocate a descriptor.
describe bind variables for ... into ... does HALF of the work of setting up the bind area. It tells you how many and what the names are of the bind variables.
You have to do the other half and
- set up pointers to indicator variables - point each of the bind_dp->V[i] to where the bind value is - set up the length of the bind variable (where bind_dp->V[i] is pointing to) - set up the type of the bind variable that bind_dp->V[i] points to. - set the value of the indicator variable.
I am enclosing a piece of code I wrote. It takes ANY statement in (pl/sql block, select/insert/update, etc) and figures out what the bind variables are. It assumes the type will be 1 and sets it so (strings). It uses a 'magic' routine that takes the bind variable name and gets a value for it (replace with your code). It then opens (executes) the statement and if it was a query , fetches the data and prints it.
Hope this helps...
On Mon, 25 Nov 1996 22:19:21 GMT, modrall_at_world.std.com (Mark W Modrall) wrote:
>hi...
>
> i'm writing a proc app that's taking a number of user input
>variables, building a query to select around them, and then processing
>the result set... the user can enter or not enter any of several
>result criterion as he pleases...
>
> the querystring starts with
>SELECT * FROM table WHERE
>
> and conditional clauses get added depending on what the user
>inputs... if the user supplies a value for column a, for example,
>column_a = :a
>
> is appended to the string...
>
> when all is said and done, i have an EXEC SQL PREPARE statement
>process the query string, i declare a cursor, i open it, then i fetch
>from it... the first time i tried to run it, it gave me an error that
>there were unbound variables (so i went back and read the manual about
>method 4)...
>
> i inserted
>
>EXEC SQL INCLUDE sqlda;
>
>SQLDA *bind_dp;
>bind_dp = sqlald (#of items, 20, 20);
>
>EXEC SQL DESCRIBE BIND VARIABLES FOR query INTO bind_dp;
>
>and USING DESCRIPTOR bind_dp; at the end of the cursor open statement...
>
>it all compiled fine, but when i tried to run it, i got the error
>ORA-3115 unsupported network datatype or representation.
>
>A user bind or define, or an Oracle function, is not supported by
>this heterogeneous SQL*Net connection.
>
>now, this error message doesn't make sense to me... the :a i'm binding
>to is a pointer to a character string. i'm logging in locally (though
>i do have SQL*Net active)... i'm using Oracle Server 7.2.2.3.0 and i
>have the compatibility variable in the SID.ora file set to 7.2... the
>action in the error message manual says "upgrade oracle", but something
>smells wrong here...
>
>can anyone shed light on my darkness? thanks
>
>-mark
>
#include <stdio.h>
#include <string.h>
#define MAX_VNAME_LEN 30 #define MAX_INAME_LEN 30
#define SQLCA_STORAGE_CLASS extern
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
extern SQLDA *sqlald();
extern void sqlclu();
EXEC SQL WHENEVER SQLERROR CONTINUE;
static int sqlerror_soft( char * stmt, int lineno )
{
char msg[255];
int x = sqlca.sqlcode;
EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK; printf( "%d<br><pre>%s</pre><br>", lineno, stmt ); printf( "\n<b><i>Oracle Error Detected:\n% .70s \n</i></b>",
sqlca.sqlerrm.sqlerrmc);
return sqlca.sqlcode = x;
}
int do_block( char * plsql_block, int display_type ) {
VARCHAR data[27][256];
int ndata = 27;
int i;
EXEC SQL PREPARE S FROM :plsql_block;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block, __LINE__);
EXEC SQL DECLARE C CURSOR FOR S;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
set_bind_variables(plsql_block);
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
if ( !process_select_list(display_type,plsql_block) ) {
for( ndata = 27; !sqlca.sqlcode; ndata = 27 )
{
for( i = 0; i < ndata; data[i].len = 256, i++ );
EXEC SQL EXECUTE
BEGIN
OWA.GET_PAGE( :data, :ndata );
END;
END-EXEC;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
if ( !ndata ) break;
for( i = 0; i < ndata; i++ )
printf( "%.*s", data[i].len, data[i].arr );
}
}
EXEC SQL CLOSE C;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
EXEC SQL COMMIT WORK;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
}
set_bind_variables(char * plsql_block)
{
int size = 10; int i;
char bvname[MAX_VNAME_LEN+5];
SQLDA * bind_dp;
if ((bind_dp = sqlald(size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
die( "Cannot allocate memory for bind descriptor" );
bind_dp->N = size;
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
if (bind_dp->F < 0)
{
size = -bind_dp->F;
sqlclu(bind_dp);
if ((bind_dp = sqlald(size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
die( "Cannot allocate memory for bind descriptor" );
bind_dp->N = size; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
}
bind_dp->N = bind_dp->F;
for (i = 0; i < bind_dp->N; i++)
bind_dp->I[i] = (short *) malloc(sizeof (short));
for (i = 0; i < bind_dp->F; i++)
{
strncpy( bvname, bind_dp->S[i], bind_dp->C[i] );
bvname[bind_dp->C[i]] = 0;
bind_dp->V[i] = (char *)get_arglist( bvname );
bind_dp->L[i] = strlen(bind_dp->V[i]);
*bind_dp->I[i] = (*bind_dp->V[i]) ? 0 : -1;
if ( *bind_dp->I[i] )
{
bind_dp->V[i] = (char *)malloc(5);
bind_dp->L[i] = 4;
}
bind_dp->T[i] = 1;
}
EXEC SQL OPEN C USING DESCRIPTOR bind_dp; if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
for( i = 0; i < bind_dp->N; i++ ) free( bind_dp->I[i] );
sqlclu( bind_dp );
}
static int lengths[] =
{ -1, 0, 45, 0, 0, 0, 0, 0, 2000, 0, 0,
18, 25, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 512, 2000 };
int process_select_list(int display_type, char * plsql_block)
{
SQLDA * select_dp;
int i;
int null_ok;
int precision;
int scale;
int size = 10;
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
die( "Cannot allocate memory for select descriptor." );
select_dp->N = size;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp; if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__); if ( !select_dp->F ) return 0;
if (select_dp->F < 0)
{
size = -select_dp->F;
sqlclu( select_dp );
if ((select_dp = sqlald (size, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL )
die( "Cannot allocate memory for select descriptor." );
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
}
select_dp->N = select_dp->F;
for (i = 0; i < select_dp->N; i++)
select_dp->I[i] = (short *) malloc(sizeof(short));
switch(display_type)
{
case 'T': printf( "<table border=1>" ); break;
case 'L': printf( "<ul>" ); break;
}
for (i = 0; i < select_dp->F; i++)
{
sqlnul (&(select_dp->T[i]), &(select_dp->T[i]), &null_ok);
if ( select_dp->T[i] < sizeof(lengths)/sizeof(lengths[0]) )
{
if ( lengths[select_dp->T[i]] )
select_dp->L[i] = lengths[select_dp->T[i]];
else select_dp->L[i] += 5;
}
else select_dp->L[i] += 5;
select_dp->T[i] = 5;
select_dp->V[i] = (char *)malloc( select_dp->L[i] );
if (display_type == 'T' )
printf ("<th>%.*s</th>", MAX_VNAME_LEN, select_dp->S[i]);
}
for (;;)
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
if ( sqlca.sqlcode ) return sqlerror_soft(plsql_block,__LINE__);
if ( display_type == 'T' ) printf( "<tr>" );
for (i = 0; i < select_dp->F; i++)
printf( "%s%s%s%s",
(display_type=='T')?"<td>":"",
(display_type=='L')?(i?", ":"<li>"):"",
*(select_dp->I[i])?"(null)":select_dp->V[i],
(display_type=='T')?"</td>":"" );
if ( display_type == 'T' ) printf( "</tr>\n" );
}
switch(display_type)
{
case 'T': printf( "</table border=1>" ); break;
case 'L': printf( "</ul>" ); break;
}
for( i = 0; i < select_dp->F; i++ )
{
free( select_dp->I[i] );
free( select_dp->V[i] );
}
sqlclu(select_dp);
return 1;
}
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Nov 27 1996 - 00:00:00 CET