Home » Developer & Programmer » Precompilers, OCI & OCCI » Dynamic SQL Method 4 with VARCHAR2 and DECIMALS (Oracle 10g in HP-UX B.11.11)
Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #323482] Wed, 28 May 2008 10:14 Go to next message
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!
Re: Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #323667 is a reply to message #323482] Thu, 29 May 2008 03:21 Go to previous messageGo to next message
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!


Re: Dynamic SQL Method 4 with VARCHAR2 and DECIMALS [message #328507 is a reply to message #323482] Fri, 20 June 2008 06:30 Go to previous message
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!
Previous Topic: GLOBAL TEMPORARY TABLES from Pro*C
Next Topic: SQLDA structures vs MODE=ANSI
Goto Forum:
  


Current Time: Sat Dec 10 13:01:42 CST 2016

Total time taken to generate the page: 0.09626 seconds