Home » Developer & Programmer » Precompilers, OCI & OCCI » Pro*C : Sample1.pc do not works fine (Oracle Pro*C - Oracle 10.2.0)
Pro*C : Sample1.pc do not works fine [message #285437] Tue, 04 December 2007 10:02 Go to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hello again!

I'm using Oracle Pro*C - Oracle 10.2.0, in a HP-UX B.11.11.

I'm executing the default Sample10:

I have a table 'tabla' with 2 fields:
Field1 (Number (5))
Field2 (Varchar(11))

If i try to do (in the prompt of sample10):
select * from tabla where field1 = :v1

Then, i insert the numeric value and It works fine.

If i try to do:
select * from tabla where field2 = 'char_value'

It works fine.

But if I try to launch this statement:
select * from tabla where field2 = :v1

Then, the example calls me to insert the value of the parameter. I pick a value that exists in the table, but the example gives me the message:

0 rows processed


¿Can anybody tell me something?

Thanks!
Re: Pro*C : Sample1.pc do not works fine [message #285439 is a reply to message #285437] Tue, 04 December 2007 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry but I don't see what is the relation between what you posted and your link.

What is the type of your :v1? You first said it is number and you try to compare it to a char.
What is the value you inserted?
VARCHAR is no more a SQL datatype for Oracle, use VARCHAR2.

Post "desc tabla"
Post your code and what you exactly did.

Regards
Michel
Re: Pro*C : Sample1.pc do not works fine [message #286393 is a reply to message #285437] Fri, 07 December 2007 04:39 Go to previous messageGo to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Oracle 10.2.0 - Pro*C
HP-UX B.11.11 U 9000/785 (tb)
-----------------------------

Hello again!
Now i have a 'Memory fault (coredump)'!!!!

I have this thable:
--------------------------------------------------------------
SQL> desc table;
 Name                                      ¿Null?   Type
 ----------------------------------------- -------- ---------
 COD1                                               NUMBER(5)
 COD2                                               NUMBER(5)
 DESC                                               CHAR(20)
--------------------------------------------------------------

I'm trying to do something similar to sample10.pc:
#define STD_DIM 3		
#define KEY_DIM	2

typedef struct
{
    short       cod1;
    short       cod2;
    char        desc[20];

}   TABLE_S;
	
TABLE_S table;
my function()
{
    EXEC SQL BEGIN DECLARE SECTION;
        char  *query;
    EXEC SQL END DECLARE SECTION;
    SQLDA *keyr_inp;		// Bind sqlda
    SQLDA *std_outp;		// Select sqlda
		
    int i = 0;  
    int  bind_var_len;
    char bind_var[64];
    int null_ok, precision, scale;
		
    /* PREPARE statement */
    query = (char *)malloc(250*sizeof(char));
    sprintf(query, "SELECT cod1, cod2, desc FROM table WHERe cod1 = :v1 AND cod2 = :v2");
    EXEC SQL PREPARE q_t_regr FROM :query;			
    free(query);
    if (SQLCODE) return SQLCODE;
				
    if (! std_outp)
    {
        if ((std_outp = 
            SQLSQLDAAlloc(SQL_SINGLE_RCTX, STD_DIM, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
                return -1;  /* Error allocating SQLDA */ 
        // Alloc space to I[] and V[]
        for (i = 0; i < STD_DIM; i++) 
        {
            std_outp->I[i] = (short *) malloc(sizeof (short));
            std_outp->V[i] = (char *) malloc(1);
        }
    } 
    std_outp->N = STD_DIM; // Set de max of select items
	    
    EXEC SQL DESCRIBE SELECT LIST FOR q_t_regr INTO std_outp;
	
    if (std_outp->F < 0)
    {    		
        return -1;
    }
    // Set de number of select items
     std_outp->N = std_outp->F;
	
    for (i = 0; i < std_outp->F; i++)
    {     
        sqlnul ((unsigned short *)&(std_outp->T[i]), 
            (unsigned short *)&(std_outp->T[i]), &null_ok);
        switch (std_outp->T[i])
        {
            case  1 : //  CHAR: 
                break;
            case  2 : // NUMBER: 
                sqlprc ((unsigned int *)&(std_outp->L[i]), &precision, &scale);
                if (precision == 0) precision = 40;
	                if (scale > 0)
	                    std_outp->L[i] = sizeof(float);
	                else
	                    std_outp->L[i] = sizeof(int);
                break;
	
            case  8 : 
	                std_outp->L[i] = 240;
	                break;
            case 11 : /* ROWID datatype */
            case 104 : /* Universal ROWID datatype */
	                std_outp->L[i] = 18;
	                break;
            case 12 : /* DATE datatype */
	                std_outp->L[i] = 9;
	                break;
            case 23 : /* RAW datatype */
	                break;
            case 24 : /* LONG RAW datatype */
	                std_outp->L[i] = 240;
	                break;
            case 96 :
                        break;
        }
        // Cohercing datatypes
        if (std_outp->T[i] != 24 && std_outp->T[i] != 2)
            std_outp->T[i] = 1;
	            
        if (std_outp->T[i] == 2)
            if (scale > 0)
	             std_outp->T[i] = 4;  /* float */
            else
	             std_outp->T[i] = 3;  /* int */
    }
	    		
    // Declare CURSOR
		
    EXEC SQL DECLARE c_t_regr CURSOR FOR q_t_regr;
    
    // Allocating memory for BIND sqlda
    if ((keyr_inp = 
         SQLSQLDAAlloc(SQL_SINGLE_RCTX, KEYR_DIM, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
        return -1;  
    for (i = 0; i < KEYR_DIM; i++) 
    {
        keyr_inp->I[i] = (short *) malloc(sizeof (short));
        keyr_inp->V[i] = (char *) malloc(1);
    }

    // Set de max number of bind vars
    keyr_inp->N = KEYR_DIM;
		
    EXEC SQL DESCRIBE BIND VARIABLES for q_t_regr INTO keyr_inp;

    if (keyr_inp->F < 0)  return -1;
		// Set de number of bind vars
    keyr_inp->N = keyr_inp->F;
    
    for (i = 0; i < keyr_inp->F; i++)
    {
        // I have the values into the struct 'table'
        switch (i)
        {
            case 0:
                sprintf(bind_var, "%d", t_reg.codterr);
                break;
            case 1:
                sprintf(bind_var, "%d", t_reg.codregi);
                break;
        }
        bind_var_len = strlen(bind_var);
        keyr_inp->L[i] = bind_var_len;
        printf("LONGITUD: %d, %d\n", keyr_inp->L[i],  bind_var_len);
        // Reallocating memory				
        keyr_inp->V[i] = (char *) realloc(keyr_inp->V[i], (keyr_inp->L[i] + 1));            				
        // Copy the value     
        strncpy(keyr_inp->V[i], bind_var, bind_var_len);
							
        // Setting INDICATOR
        if ((strncmp(keyr_inp->V[i], "NULL", 4) == 0) || (strncmp(keyr_inp->V[i], "null", 4) == 0))
            *keyr_inp->I[i] = -1;
        else       
            *keyr_inp->I[i] = 0;
        keyr_inp->T[i] = 1;
    }

    // Opening the cursor
    EXEC SQL OPEN c_t_regr using DESCRIPTOR keyr_inp;
    if (SQLCODE)   return SQLCODE;   
    
    // Fetching the CURSOR
    EXEC SQL FETCH c_t_regr USING DESCRIPTOR std_outp;
    if (SQLCODE == 0)
    {
        table.cod1 		= *(int *)std_outp->V[0];
        table.cod2 		= *(int *)std_outp->V[1];
        strcpy(table.desc, std_outp->V[2]);
        printf ("[%d] - %d - %d- %s\n",SQLCODE, table.cod1, table.cod2, table.desc);
    }

    // Save the SQLCODE
    i = SQLCODE;    	   

    //free memory from SQLDA
		
    for (i = 0; i < KEYR_DIM; i++)
    {    
        if (keyr_inp->V[i] != (char *) 0)  free(keyr_inp->V[i]);
        free(keyr_inp->I[i]);   
    }		
    SQLSQLDAFree(SQL_SINGLE_RCTX, std_outp);

    for (i = 0; i < STD_DIM; i++)
    {    
        if (std_outp->V[i] != (char *) 0) free(std_outp->V[i]);
        free(std_outp->I[i]);   
    }
    SQLSQLDAFree(SQL_SINGLE_RCTX, keyr_inp); 
                /****************/
                /* HERE CRASHES */
                /****************/	
	
    // Close Cursor 
    EXEC SQL close c_t_regr;
    if (SQLCODE)   return SQLCODE;   
		
    return i;
}


The FETCH works correctly, it gives me the results and put them into the struct 'table'. It's always only a row.

But, when execute the "SQLSQLDAFree(SQL_SINGLE_RCTX, keyr_inp);" the program give me the error:
Memory fault(coredump)

I'm taking the same steps than sample10.pc.

Can anybody help me, please?
Re: Pro*C : Sample1.pc do not works fine [message #286400 is a reply to message #286393] Fri, 07 December 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to use a debugger to know exactly in which part of the code the exception occurs.

Regards
Michel
Re: Pro*C : Sample1.pc do not works fine [message #286402 is a reply to message #285437] Fri, 07 December 2007 04:56 Go to previous messageGo to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hello! I got news!


This 'Memory Fault' is shown when the variable
SQLDA *keyr_inp;		// Bind sqlda

is declared as global. If i declare it local to 'my_function()', i have no errors.
¿Anybody knows why?

Thanks so much!
Re: Pro*C : Sample1.pc do not works fine [message #286404 is a reply to message #286402] Fri, 07 December 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback but next time post the code from where you have an error and not the one that works.

Regards
Michel
Re: Pro*C : Sample1.pc do not works fine [message #286410 is a reply to message #285437] Fri, 07 December 2007 05:12 Go to previous message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Thanks you and sorry!

My real code is so much complicated, it haves many printf's, and it have 3 or 4 different functions. The code i posted is a 'copy&paste', and i wrote the variables in a bad place.

Thanks!
Previous Topic: Can update generate a no_data_found [MERGED]
Next Topic: reading data from serial port to oracle databse
Goto Forum:
  


Current Time: Thu Mar 28 05:38:39 CDT 2024