Re: ANSI dynamic SQL with ProC error...

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