The same error is detected in stored **procedure**, but not in stored **function**

This question is related to my previous one: RaiseError (PERL, DBI) equivalent for unixODBC C API?

As I isolated the problem later, I'll post new question, that is more specific, isolated and without unnecessary information.

Version: unixODBC 2.3.0

lib: unixODBC - C API

Suppose I have a stored FUNCTION:

CREATE FUNCTION "test".func() RETURNING LVARCHAR(1000);
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
trace off;
开发者_开发问答return 'result is set here';
END FUNCTION;

And the same body, but in stored PROCEDURE:

CREATE PROCEDURE "test".proc(pDummy SMALLINT)
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
LET pDummy = 2;
trace off;
END PROCEDURE;

As you can see, they are absolutely the same. The path to debug file is wrong, so error is expected. When I execute call func() from Aqua Data Studio the error is detected:

Cannot open DEBUG file for SPL routine trace

It's the same for call proc(1).

BUT when I execute these 2 calls through unixODBC (using SQLExecute),

execute procedure proc(1);

returns SQL_ERROR (which is expected and fine), while

execute function func();

returns SQL_SUCCESS.. BUT 'result is set here' is not returned, empty string ('') is returned, instead..

Executing call func() gives the same results, as execute function func();

Calling SQLMoreResults returns SQL_NO_DATA, SQLFetch returns SQL_ERROR.

Any ideas?

I don't use Informix but the simple examples I've tried with Perl DBD::ODBC and also from isql (written in C) all return an error:

use strict;
use warnings;
use DBI;

my $h = DBI->connect();
eval {
    $h->do(q/drop function fmje/);
};

$h->do(<<'EOS');
create function fmje (@p1 as int)
returns int
as
begin
    declare @a int;

    set @a = 'fred';
    return @p1;
end;
EOS

my $s = $h->prepare(q/{? = call fmje(?)/);
$s->bind_param_inout(1, \my $x, 10);
$s->bind_param(2, 1);
$s->execute;
print "return is ", ($x ? $x : "undef"), "\n";



isql -v baugi sa easysoft
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> {call fmje(1)}
[22005][unixODBC][Easysoft][SQL Server Driver 11.0][SQL Server]Conversion failed when converting the varchar value 'fred' to data type int.
[ISQL]ERROR: Could not SQLExecute
SQL>

Informix must work differently for functions or perhaps you are not using generic ODBC via Aqua Data Studio.

If you see the error from Perl as you say in your other post then do as I recommended there and add:

[ODBC]
Trace=yes
TraceFile=/tmp/unixodbc.log

to the top of your odbcinst.ini file and run the Perl. Then show us the lines from the log from the error. Then repeat with isql so we can compare the ODBC calls.

This may be something to do with the server version you are using (unlikely, but possible), or to do with the API you are using. When I test with IDS 11.70.FC2 on MacOS X 10.7 using (my) sqlcmd program build with ESQL/C (CSDK) 3.70.FC2, I get:

$ sqlcmd -c -d stores -e begin -xf x1.sql -e 'execute procedure proc(2)' \
         -e 'execute function func()' 
+ CREATE FUNCTION "test".func() RETURNING LVARCHAR(1000);
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
trace off;
return 'result is set here';
END FUNCTION;
+ CREATE PROCEDURE "test".proc(pDummy SMALLINT)
set debug file to '/home/directory_does_not_exists/unknown.log';
trace off;
trace on;
LET pDummy = 2;
trace off;
END PROCEDURE;
+ execute procedure proc(2)
SQL -648: Cannot open DEBUG file for SPL routine trace.
SQLSTATE: IX000 at /dev/stdin:0
+ execute function func()
SQL -648: Cannot open DEBUG file for SPL routine trace.
at /dev/stdin:0
$

As you can see, both func() and proc() correctly report an error in ESQL/C. So, the problem is almost certainly in the client-side code - in the ODBC driver and the way it is handling the errors, or in the code calling the ODBC driver.

How to isolate the problem more?

Run your test with SQLIDEBUG=2:xyz in the environment. Then, find the file with a names starting xyz_ (I got xyz_35424_0_819800, for example) and run sqliprint on it. That will show you whether the server is generating the error message twice.

I got two packets similar to this in the one trace:

S->C (12)               Time: 2011-07-28 00:28:02.41736
    SQ_ERR
        SQL error..........: -648
        ISAM/RSAM error....: 0
        Offset in statement: 0
        Error message......: "" [0]
    SQ_EOT

If you see the two packets with the -648 error, then you know the problem is in the way the client is handling the error. If you don't see the two errors, then I'm very curious to see what is going on.

First of all - thanks a lot to @Jonathan Leffler(for the hint with SQLIDEBUG=2:xyz + sqliprint and testing on his machine) and @bohica (for the hint with strace ) for the support! That really helped me to find the real problem and solve it! +1 from me for both.
Unfortunately, the answer was not in their posts, that's why I'll answer it my own.

Summary:

SQLPrepare and SQLExecute fail sometimes on some errors, but not all. When stored procedure is used, these functions catch more errors. Unfortunately, the situation is different with stored functions.

How I catch the errors now? If SQLExecute is successfull, I call SQLNumResultCols - that's normal. After that, I call SQLFetch which is also expected. BUT, as SQLFetch may fail for many reasons (for example, it always fails on stored procedures), it's error is ignored. And there's a while like

if ( SQLNumResultCols( stmt, &nAllCols ) != SQL_SUCCESS )
// ...

int nSucceededFetches = 0; // added now, see below why
while ( SQL_SUCCEEDED( SQLFetch( stmt ) ) )
{
    ++nSucceededFetches; // added now, see below why
    /* bla bla */ 
}

And here's the key - add additional check:

if( 0 == nSucceededFetches && nColumns > 0 )

which says - if there are returned columns and fetch fails on the FIRST call, then something's wrong. Then I have

while ( SQL_SUCCESS == SQLError( 0, 0, stmt, szSqlState, &nNativeError, szError, 500, &nErrorMsg ) )
{ /* bla bla */ }

And everything's fine. I still don't understand why SQLExecute returns SQL_SUCCESS (NOT even SQL_SUCCESS_WITH_INFO ..), but it doesn't matter.