Re: ANSI dynamic SQL with ProC error...

Thank You,
I figured out the problem, the host variable deptno_type should be 4 or 5, as the TYPE_CODE is set to ANSI.
Cheers
Sanjay.

"Frank" <fvanbortel_at_netscape.net> schrieb im Newsbeitrag news:bi5smi$a7p$1_at_news1.tilbu1.nb.home.nl...
> RD2-Team_at_fujitsu-siemens.com wrote:
>
> > Hi,
> > I am a student undergoing my practical traineeship in Fujitsu -Seimens,
> > basically I'm trying to get a hand on Ansi Dynamic SQL, I am
encountering a
> > problem with the code below, which basically fetches me the all the
empno in
> > deptno 10. I get an ORA-01727: numeric precision specifier is out of
range(1
> > to 38) error message. I don't understand where the precision goes into.
> > Any help will be appreciated.
> >
> >
> > #include <stdio.h>
> > #include <string.h>
> > #include <stdlib.h>
> > #include <sqlcpr.h>
> > EXEC SQL BEGIN DECLARE SECTION;
> > char* dyn_statement = "SELECT empno FROM employee WHERE deptno =
> > :deptno_data";
> > int deptno_type = 3, deptno_len = 4, deptno_data = 10;
> > EXEC SQL END DECLARE SECTION;
> > void sql_error(char *msg){
> > char err_msg[128];
> > int buf_len, msg_len;
> > EXEC SQL WHENEVER SQLERROR CONTINUE;
> > printf("\n%s\n", msg);
> > buf_len = sizeof (err_msg);
> > sqlglm(err_msg, &buf_len, &msg_len);
> > printf("%.*s\n", msg_len, err_msg);
> > EXEC SQL ROLLBACK RELEASE;
> > exit(1);
> > }
> > int main() {
> > EXEC SQL BEGIN DECLARE SECTION;
> > char *username = "spielplatz/spielplatz_at_ecadtst";
> > EXEC SQL END DECLARE SECTION;
> > EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
> > EXEC SQL CONNECT :username;
> > EXEC SQL CREATE table employee (empno NUMBER(4),deptno NUMBER(2));
> > EXEC SQL INSERT into employee(empno,deptno) values(1,10);
> > EXEC SQL INSERT into employee(empno,deptno) values(12,10);
> > EXEC SQL ALLOCATE DESCRIPTOR 'in';
> > EXEC SQL PREPARE s FROM :dyn_statement;
> > EXEC SQL DESCRIBE INPUT s USING DESCRIPTOR 'in';
> > EXEC SQL SET DESCRIPTOR 'in' VALUE 1 TYPE = :deptno_type,
> > LENGTH = :deptno_len, DATA = :deptno_data ;
> > EXEC SQL DECLARE c CURSOR FOR s;
> > EXEC SQL OPEN c USING DESCRIPTOR 'in';
> > /*rest is not interesting as the error has now happened...*/
> > return 1;
> > }
> >
> > #Setup for make
> > .PHONY: test
> > #Variables containing options for compilation
> > PROC_INC_PREFIX=/opt/os/linux/packages/oracle/client
> > INCLUDE= . \
> > $(PROC_INC_PREFIX)/precomp/public \
> > $(PROC_INC_PREFIX)/rdbms/public \
> > $(PROC_INC_PREFIX)/rdbms/demo \
> > $(PROC_INC_PREFIX)/plsql/public \
> > $(PROC_INC_PREFIX)/network/public
> > PROCFLAGS= LTYPE=NONE LINES=YES mode=ansi $(addprefix
include=,$(INCLUDE))
> > CFLAGS= -g -trigraphs -fPIC \
> > -DPRECOMP -DLINUX -D_GNU_SOURCE -D_LARGEFILE64_SOURCE=1 \
> > -D_LARGEFILE_SOURCE=1 -DSLTS_ENABLE -DSLMXMX_ENABLE \
> > -D_REENTRANT -DNS_THREADS \
> > $(addprefix -I,$(INCLUDE))
> > LDFLAGS= -L$(PROC_INC_PREFIX)/lib -lclntsh \
> > `cat $(PROC_INC_PREFIX)/lib/ldflags` \
> > `cat $(PROC_INC_PREFIX)/lib/sysliblist`\
> > -ldl -lm
> > #Suffix based rules
> > %.c:%.pc
> > proc iname=$(basename $<) oname=$_at_ $(PROCFLAGS)
> > %.o:%.c
> > gcc $(CFLAGS) -c $(basename $<).c -o $_at_
> > %:%.o
> > gcc $(LDFLAGS) -o $_at_ $<
> > test: testcase
> > ./$<
> >
> >
> Wouldn't deptno be 5 (sign, 4 digits) or even 6
> (sign, 4 digits, \0) in length?
>
> --
> Regards, Frank van Bortel
>
Received on Wed Aug 27 2003 - 09:39:14 CEST