Trick to reset odbc_error()

Question

I've learnt that odbc_execute() does not always trigger a proper ODBC error when it returns FALSE (not at least with the Oracle driver) and I can't fully trust odbc_error() or odbc_errormsg(). This situation is easy to detect when there wasn't a previous error because odbc_error() returns an empty string. However, when it returns something I don't know whether it belongs to the last failed operation or it's a remain from a previous error.

The simplest solution would be to reset the odbc_error() and odbc_errormsg() functions when there's an error so next calls would start from scratch, but I couldn't find a supported way to do so. Can you figure out a way to do it?

_{Background: I'm enhancing a legacy app with a class that encapsulates database calls. That's why I need to make everything as generic as possible.}

score 3 · Answer 1 · answered Dec 17 '14 at 13:30

3

it's not necesary to reset the function I solved in this way:

function foo($sql){
   $res = odbc_exec($this->dbconn, $sql);
   if (odbc_error () and $res===false ) {
       return $this->catchException(odbc_errormsg($this->dbconn));

   }
    return $res;
}

answered Dec 17 '14 at 13:30

vteran93

41
2

If you decide to catch the exception and ignore the error (e.g., because it's a duplicate key that your code logic can handle) its error message might end up associated to next errors. That's what I want to guard against. – Álvaro González Dec 17 '14 at 15:39
What does `$this->catchException()` do ? – Lorenz Meyer Dec 17 '14 at 16:54
public function catchException($odbc_errormsg){ //echo "Funciona en catch
"; $patron_error="/SQL\d+/"; if(preg_match($patron_error, $odbc_errormsg, $coincidencias)){ throw new Exception($coincidencias[0].";".$odbc_errormsg); } } For now, it's only useful with error messages of DB2. I hope find a way to manage errors of another databases with the php odbc driver – vteran93 Dec 17 '14 at 21:41

score 1 · Answer 2 · answered Dec 11 '14 at 09:14

odbc_error sometimes becomes confusing. executed sql string and error message may be different. In order to prevent this, we can hold all the executed sqls in an array, and after all execution finishes, we can check what the error messages are.

First let us define a executedSQL class which will hold the executed sqls info:

class executedSQL 
{
    public sql;
    public result;
    public error;
    public message;
}

This class will hold all sql info and their result and returned messages.

If we use a class to connect the odbc db:

class myODBC
{
    //holds the connection
    public $connection;

    //all executed sql string are added to this array as executedSQL object.
    public $executedSQLs = array();


    public function connect()
    {
        $this->connection = dbc_connect(" ", " ","") or die(odbc_errormsg());
    }

    public function execute($sql)
    {
        $execution = odbc_exec($this->connection, $sql); //execute it

        //put all the results to executedSQL object
        $executed = new executedSQL();
        $executed->sql = $sql;
        $executed->result = $execution;
        $executed->error = odbc_error();
        $executed->message = odbc_errormsg();

        //push to executedSQLs var.
        array_push($this->executedSQLs, $executed);

        return $execution;
    }
}

If we execute our sqls:

$db = new myODBC();

$db->connect();

$db->execute("select * from table1");
$db->execute("this is gonna be failed sql");
$db->execute("select * from table2");

print_r($db->executedSQLs);

This is going to print all sqls and their results. At this point we can see the executed sql and its related error message. So literally we are not resetting odbc_error but we make it more clear . If an error message is repeated twice, it is more propable that it belongs to previous executed sql. This way debugging becomes easier.

Thanks for your answer. Yet your answer is only well for the case that one needs the error messages for debug purposes only. In a real world application, one should be able to rely on error messages to take decisions (different behavior according to the error, or translate the message in a way that it makes sense for the end user). This is the reason why I needed a thorough answer and offered a bounty. — Lorenz Meyer, Dec 12 '14 at 19:57
The root platform bug this question is about is that `odbc_errormsg()` can happily return the message from a previous statement. The problem is not to detect whether your query succeeds (that's easy), the problem is to be sure that the error message does not belong to a previous query. Unless I'm missing something, your code does nothing to solve that. — Álvaro González, Dec 17 '14 at 15:42
In this approach, we can detect whether an arror message from previous statement or not. For example, say, we are executing three queries. First one is successful, second one fails and returns "fail message 1". And third one also fails return "fail message 1". By this approach, we can learn that "fail message 1" belongs to query 2. — isa, Dec 18 '14 at 11:38
@isa - Sadly, that's the only info we can get. We can't be sure at all about third query. — Álvaro González, Dec 23 '14 at 08:44

score 0 · Answer 3 · answered Feb 23 '15 at 20:16

odbc_errormsg errors cannot be reset during script. So, actually an easy way to separate odbc_errormsg errors is to assign each obdc_connect an unique identifier. Examples show $db =@odbc_connect($somefile,......) but using either random or unique names => $db900 =@odbc_connect($somefile,......) or $myuniquename =@odbc_connect($somefile,......) will seperate error messages. Then using odbc_errormsg($myuniquename) will only return the error for that id.

Trick to reset odbc_error()

3 Answers3