OraFAQ Forum: Precompilers, OCI & OCCI » Dynamic SQL Method 4 with VARCHAR2 and DECIMALS

Home » Developer & Programmer » Precompilers, OCI & OCCI » Dynamic SQL Method 4 with VARCHAR2 and DECIMALS (Oracle 10g in HP-UX B.11.11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #323482]

Wed, 28 May 2008 10:14

donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain

Member

Hello again!

I'm working with the famous sample10.pc of Oracle (Dynamic SQL Method 4).

It works fine, but not ever. It have 'problems' on the 'DESCRIBE BIND VARIABLES' part.

The 'important' source code for this case is:

    for (i = 0; i < bind_dp->F; i++)
    {
        printf ("\nEnter value for bind variable %.*s:  ",
               (int)bind_dp->C[i], bind_dp->S[i]);
        fgets(bind_var, sizeof bind_var, stdin);

        /* Get length and remove the new line character. */
        n = strlen(bind_var) - 1;

        /* Set it in the descriptor. */
        bind_dp->L[i] = n;

        /* (re-)allocate the buffer for the value.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer. */

         bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
                         (bind_dp->L[i] + 1));            

        /* And copy it in. */
        strncpy(bind_dp->V[i], bind_var, n);

        /* Set the indicator variable's value. */
        if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
                (strncmp(bind_dp->V[i], "null", 4) == 0))
            *bind_dp->I[i] = -1;
        else
            *bind_dp->I[i] = 0;
    
        /* Set the bind datatype to 1 for CHAR. */
        bind_dp->T[i] = 1;
    }

A simple example that works fine is:

SQL> select * from scott.emp where ename = :v1;

Enter value for bind variable V1:  SMITH

EMPN ENAME      JOB       MGR  HIREDATE  SAL     COMM    DEPT

7369 SMITH      CLERK     7902 17/12/80  800.00           20

1 row processed.

But, if i change 'EMP' table, changing the column 'ENAME' to 'CHAR(10)' -originally is VARCHAR2(10)-, sample10 will not works. The solution is changing the value of SQLDA->T[]:

        /* Set the bind datatype to 1 for CHAR. */
        bind_dp->T[i] = 96;  /*(previously '1')*/

But, and here i have so much troubles, if i add to 'EMP' a new decimal column -PRICE NUMBER(14, 2)-, and then i try to do:

SQL> select * from EMP where PRICE= :v1;

Enter value for bind variable V1:  12.34

ORA-01722: invalid number

I think i have to make a similar change to the case of 'CHAR' column, i mean, changing SQLDA->T[] to another Oracle External Datatype constant. I try with all (4, 7 -float-, SQLT_BDOUBLE, ...), but i never have success.

Have i to use the 'sqlprc()' function in the BIND VARIABLES? (In the Oracle documentations refers this funtion only to select-list SQLDA)

Somebody can help me, please?

Thanks so much!

Report message to a moderator

Re: Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #323667 is a reply to message #323482]

Thu, 29 May 2008 03:21

donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain

Member

Hello!

I'm still testing to solve it.

If i throw a SELECT clause, it returns the decimals values correctly. Sample10 don't works when the decimal field is one of BIND VARIABLES.

Now, i'm trying to make an INSERT INTO clause.

I'm testing coercing Oracle Datatype to 4, calling function 'sqlprc()', etc.. but i don't have success. The option that are nearest to success is:

    for (i = 0; i < bind_dp->F; i++)
    {
        printf ("\nEnter value for bind variable %.*s:  ",
               (int)bind_dp->C[i], bind_dp->S[i]);
        fgets(bind_var, sizeof bind_var, stdin);

        /* Get length and remove the new line character. */
        n = strlen(bind_var) - 1;

        /* Set it in the descriptor. */
        bind_dp->L[i] = n;

        /* (re-)allocate the buffer for the value.
           SQLSQLDAAlloc() reserves a pointer location for
           V[i] but does not allocate the full space for
           the pointer. */

         bind_dp->V[i] = (char *) realloc(bind_dp->V[i],  
                                          bind_dp->L[i]);            

        /* And copy it in. */
        strncpy(bind_dp->V[i], bind_var, n);

        /* Set the indicator variable's value. */
        if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
                (strncmp(bind_dp->V[i], "null", 4) == 0))
            *bind_dp->I[i] = -1;
        else
            *bind_dp->I[i] = 0;
    
        /* Set the bind datatype to 4 for FLOAT. */
        bind_dp->T[i] = 4;
    }

But in this case, the value inserted in 'PRICE' (the decimal column) allways will be 0.

I'm very disconcerted!

Thanks in advance!

Report message to a moderator

Re: Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #328507 is a reply to message #323482]

Fri, 20 June 2008 06:30

donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain

Member

Hello again!

This solution to that trouble is simplier than i expected Wink

We can't assign a string value to a decimal field:

(...)
/* And copy it in. */
strncpy(bind_dp->V[i], bind_var, n);
(...)

must be changed for something that:

(...)
double bind_var_d;
(...)
keyr_inp->L[i] = sizeof(double);	
keyr_inp->V[i] = (char *)&bind_var_d;
keyr_inp->T[i] = 96;
(...)

Thanks!

Report message to a moderator

Previous Topic:	GLOBAL TEMPORARY TABLES from Pro*C
Next Topic:	SQLDA structures vs MODE=ANSI

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 05:23:13 CDT 2024