Menu

#18 Problem when binding output stored procedures

v1.0_(example)
open
nobody
None
5
2013-07-29
2013-07-29
No

Hi

I found you library very good, and i´m planning to use on an new project.

this small procedure
create or replace
procedure test3
(
param1 out varchar2
) as
begin
param1:='Hi world';
end test3;

never show hi world on screen

this is the code i tested

include "ocilib.h"

int main(void)
{
OCI_Connection cn;
OCI_Statement
st;
OCI_Resultset *rs ;

int code = 22;
int code2 = 2;

if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
    return EXIT_FAILURE;

cn = OCI_ConnectionCreate("culik3", "proprio", "proprio", OCI_SESSION_DEFAULT);

st = OCI_StatementCreate(cn);

printf( "%i\n",OCI_Prepare(st, "BEGIN test3(:22); END; "));
printf( "%i\n",    OCI_SetBindMode( st,OCI_BIND_BY_POS )) ;
printf( "%i\n",    OCI_RegisterString(st, ":1",30));

printf( "%i\n",    OCI_Execute(st));

rs = OCI_GetResultset(st);
/* print resultset content */
while (OCI_FetchNext(rs))

{

    printf("> String : %s", OCI_GetString(rs, 1));

}

OCI_Commit(cn);

OCI_Cleanup();

return EXIT_SUCCESS;

}

Debugging ocilib sources i found the problem here
boolean OCI_DefineIsDataNotNull
(
OCI_Define *def
)
{
boolean res = FALSE;

if ((def != NULL) && (def->rs->row_cur > 0))  <------ ***Here the problem, def->rs->row_cur is 0 for executing this procedure***
{
    OCIInd ind = OCI_IND_NULL;

    if (def->col.type == OCI_CDT_OBJECT)
    {
       ind =  * (OCIInd *) def->buf.obj_inds[def->rs->row_cur-1];
    }
    else
    {
        ind = ((OCIInd *) (def->buf.inds))[def->rs->row_cur-1];

    }

    res = (ind != OCI_IND_NULL);
}

return res;

}

Regards
Luiz Rafael Culik

Discussion

  • Luiz Rafael Culik

    HI

    just for complement previus message.
    Also the OCI_Resultset row_cur field is keeped as 0 , so the call to OCI_FetchNext will close the fetch since rs->row_abs and rs->row_count is both 0, making OCI_GetXXXX Fail

    Regards
    Luiz

     
  • Vincent Rogier

    Vincent Rogier - 2013-07-29

    Hello,

    It does not work because you're not using the intended method for achieving what you want to do.
    Before deeping in source code, take a bit of time reading on the documentation.

    OCi_RegisterXXX() calls must be used only for supporting Oracle SQL "Returning into" clause.
    When performing regular binding, you must use OCI_BindXXX() calls and must not use resultsets...

    Another thing, you declared your precedure "test" taking an out string parameter.
    But in your C code, you passing a bind string ":22" and tehn declare a bind ":1"... It cannot work.

    Thus here is the right (and much simplier) way to do what you want :

    int main(void)
    {
        OCI_Connection *cn;
        OCI_Statement *st;
    
        char res[250] = "";
    
        OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
    
        cn = OCI_ConnectionCreate("culik3", "proprio", "proprio", OCI_SESSION_DEFAULT);
        st = OCI_StatementCreate(cn);
    
        OCI_Prepare(st, "BEGIN test3(:1); END; ");
        OCI_BindString(st, ":1", res, 250);
        OCI_Execute(st);
    
        printf("> test3 outout variable content is  : %s\n", res);
    
        OCI_Cleanup();
    
        return EXIT_SUCCESS;
    }
    
     
  • Luiz Rafael Culik

    Hi Vincent

    Thanks for answer, but as I know for positional binds the name of parametere dont matter.

    So for an stored procedure with output cursor, i can use this example bellow safely, correct

    OCI_Statement* st2 = OCI_StatementCreate(cn);

    print_text("\n>>>>> TEST REF CURSOR \n\n");
    
    OCI_Prepare(st, MT("begin open :c for select * from test_fetch; end;"));
    OCI_BindStatement(st, MT(":c"), st2);
    OCI_Execute(st);
    
    rs = OCI_GetResultset(st2);
    
    /* print resultset content */
    while (OCI_FetchNext(rs))
    {
        print_frmt("> code : %i", OCI_GetInt(rs, 1));
        print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
        print_frmt(", price : %g", OCI_GetDouble(rs,3));
        print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
        print_text("\n");
    }
    

    Regards
    Luiz

     

Anonymous
Anonymous

Add attachments
Cancel





Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.