Cannot create triggers and functions through oracle using Connection_execute

[Former user]

We have tried running this function code through libzdb using execute

Example : create or replace function datediff( p_what in varchar2,p_d1 in date, p_d2 in date ) return number as l_result number;begin select (p_d2-p_d1) * decode(upper(p_what),'SS', 246060, 'MI', 24*60, 'HH', 24, NULL ) into l_result from dual;return l_result;end;

In this case it is removing the last semi-colon (;) i.e after end from the string and corresponding function/trigger is not formed properly .The main thing is that we are not getting any exception of any sort.

libzdb code in which we made changes to sort this out

int OracleConnection_execute(T C, const char *sql, va_list ap)

T StringBuffer_trim(T S) { assert(S); // Right trim and remove trailing semicolon while (S->used && ((S->buffer[S->used - 1] == ';') || isspace(S->buffer[S->used - 1]))) S->buffer[--S->used] = 0; // Left trim if (isspace(*S->buffer)) { int i; for (i = 0; isspace(S->buffer[i]); i++) ; memmove(S->buffer, S->buffer + i, S->used - i); S->used -= i; S->buffer[S->used] = 0; } return S; }

Inside this connection_execute function , there is a trim function named StringBuffer_trim as shown below used to remove trailing semicolon , so it is removing semi-colon after end of the string . We have even compiled the libzdb again by removing this semi-colon check ( S->buffer[S->used - 1] == ';' ) from code to see if something happens but the issue remained same , we diin't find any changes.

Please analyze this and provide us the feedback or a simple solution to it .

[Volodymyr Tarasenko]

The problem is not in semi-colon. As I understand this is oracle specific question - all stuff like function creation, etc should be done by different methods. Anyway you could call already created function, but currently you will not be able to create any function.

If you will provide the code snippet of libzdb usage, I will try to figure out how it could be solved.

[Dhananjay Team]

Here's a short example of the issue , hope it could be of some help

This example tries to create a function and we are not getting any exception.

int main(int argc, char** argv) { string msg="",conn_string; conn_string= "oracle://192.168.1.112/xe?user=ngucc&password=12345678&connect-timeout=60"; URL_T url= URL_new(conn_string.c_str()); ConnectionPool_T pool;

TRY
{
    pool = ConnectionPool_new(url);

    if(pool!=NULL)
    {
        ConnectionPool_setInitialConnections(pool,1);
        ConnectionPool_setMaxConnections(pool,2);
        ConnectionPool_setConnectionTimeout(pool,30);
        ConnectionPool_setReaper(pool,300);
        ConnectionPool_start(pool);

        if(ConnectionPool_size(pool))
        {
            msg="";
        }            
        else
        {
            msg="Please do the config setting correctly...";
            cout<< msg;
        }                
    }        
    else
    {
        msg="Database Connectivity Error";
        cout<<msg<<"\n\n";
    }        
}    
ELSE
{
    msg=Exception_frame.message;

    cout<< msg;
}

END_TRY;

Connection_T con=ConnectionPool_getConnection(pool);

if(con==NULL)
{ 
    msg="Database connection is not created.Please resolved the Issue and restart your service again...."; 
    cout << msg << endl;
}

string query="create or replace function datediff( p_what in varchar2,p_d1   in date, p_d2   in date ) return number as l_result    number;begin select (p_d2-p_d1) * decode(upper(p_what),'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) into l_result from dual;return l_result;end;";

TRY {
Connection_beginTransaction(con); Connection_execute(con,query.c_str(),"%s"); Connection_commit(con); cout<<"Execution successful "; } ELSE { Connection_rollback(con); msg=Exception_frame.exception->name; cout << "query = " << query<<endl; cout<<Exception_frame.message<<"\n\n"; msg += "\n";

    cout<<msg<<endl;
}
END_TRY;

Connection_close(con);
return 0;

}

[Tildeslash]

Unless this is a "spelling error" your parameters are in the wrong order in this call:

 Connection_execute(con,query.c_str(),"%s");

[Tildeslash]

Removing version: 3.0 (automated comment)