PHP: INSERT using prepared statement works?

Jaka_Jančar

I'm trying to insert using a prepared statement in PHP.

Using pdo_odbc, I run into the following issue:
   - https://bugs.php.net/bug.php?id=65952&edit=3
   - https://community.vertica.com/vertica/topics/sqlstate_hy000_general_error_50240_vertica_support_5024...
   - https://community.vertica.com/vertica/topics/error_50240_invalid_buffer_position

Using odbc, I run into the following issue:

https://community.vertica.com/vertica/topics/php_10020_odbc_function_s_executed_out_of_allowable_seq...

Does anyone have a working example of an INSERT using prepared statements in PHP?

Jaka_Jančar

Thank you.

Richard_Uren

Bump ... I've got the same problem. Was there any resolution on this ?

PHP Warning:  odbc_execute(): SQL error: [unixODBC][Vertica][ODBC] (10020) ODBC Function(s) executed out of allowable sequence., SQL state S1010 in SQLDescribeParameter in /home/dbadmin/v4.php on line 22

Minimal test case ..

<?php
        putenv("ODBCINI=/etc/odbc.ini");
        putenv("VERTICAINI=/etc/odbc.ini");

        # Turn on error reporting
        error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

        # Connect to the Database
        $dsn = "VerticaDSN";
        $conn = odbc_connect($dsn,'dbadmin','dbadmin') or die ("CONNECTION ERROR");
        echo "Connected with DSN: $dsn" . "\n";

        $count = 0;
        $time = time();

        # Insert data into the table with a standard SQL statement
        //odbc_autocommit($conn, FALSE);

        $values = array(2,2.28,'abcdefg1234567890','1901-01-01','23:12:34','1901-01-01 09:00:09','t');
        $stmt = odbc_prepare($conn,"INSERT into TEST values(?,?,?,?,?,?,?)");
        odbc_execute($stmt, $values);
        odbc_execute($stmt, $values);

Richard_Uren

Logfiles at

http://hd-files.s3.amazonaws.com/vertica_odbc_conn_1.log
http://hd-files.s3.amazonaws.com/vertica_driver.log

Richard_Uren

Is this a Vertica ODBC bug ? Can anyone official comment ?

Thanks.

Cheers
Richard

Richard_Uren

Its been a week. Anyone at HP ? Is this a known problem ?

[Deleted User]

Hi Richard,

I don't know of any known issue here, and I know that there are many people (including people who have posted elsewhere on this forum) using PHP and Vertica in ways similar to this successfully.

I'm not personally familiar with PHP.  Hopefully someone else can comment as to your particular example.

There's a simple PHP example in our documentation:

https://my.vertica.com/docs/6.1.x/HTML/index.htm#16519.htm

Does that work?

If not, most likely something's wrong with your setup; make sure your setup meets Vertica's requirements in terms of tested versions of stuff, etc; also that you've configured everything per the documentation.

If so, see if you can figure out what's different between your example and the official example that's causing you to receive this error.  If you post an example that works and an almost-but-not-quite-identical example that doesn't, that would likely get you more responses here as people could look at it and understand much more precisely what's going on.

Adam

Richard_Uren

Hi Adam,

Thanks for your reply.

The example from the URL above does work, however if you add a second execute to the prepared statement it does not.

In my stripped down minimal example (based on that URL and displayed fully above) they are these lines :

$values = array(2,2.28,'abcdefg1234567890','1901-01-01','23:12:34','1901-01-01 09:00:09','t');
$stmt = odbc_prepare($conn,"INSERT into TEST values(?,?,?,?,?,?,?)");
odbc_execute($stmt, $values);
odbc_execute($stmt, $values);

The first odbc_execute works fine, inserts a record, the second gets the error.

My ODBC setup is fine, it works for all other ODBC commands. Its just the prepare. I see a few other threads that have this same issue.  You can see my minimal test case above in the thread along with the log information.

The example that works is just to call odbc_execute once :-)  I can prepare, execute, prepare, execute .. that works (of course that defeats the purpose .. but it works) .. Its just when I use the same resource ($stmt) twice in a row that odbc_execute fails with the "ODBC 10020" error.

Cheers Adam,
Richard

Dev_1

Any solution for this? I have the exact same issue.

Richard_Uren

Hi Dev,

Unfortunately no solution yet.
Hoping HP will take a look at it
Its a bug somewhere and its easy to reproduce.

Cheers
Richard

[Deleted User]

Hi all,

So, I can't speak to this specifically, as I don't personally work on our client drivers.  But this behavior definitely works via ODBC in a C application.  PHP's ODBC functionality is just based on top of that.

I hope we'll have a response for you on the Vertica side.  (I can't comment beyond that on unreleased functionality; though I will say that the relevant people are aware of the issue.)

But in the meantime, I suspect that PHP is trying to do something a little funny (and unnecessary) that Vertica doesn't support.  If anyone here has experience developing PHP extensions, you could probably open up pdo_odbc's source code, figure out what it's doing, and tweak it to do something that works.

Not a convenient or complete answer, I know.  I wanted to at least post an update so you know that we are aware of this.

Adam

Dev_1

Hi, Thanks,
This problem exists also on regular php_odbc not only on pdo_odbc 

[Deleted User]

Ah, thanks for the update.  I'll be sure to pass that along.