Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

PHP: INSERT using prepared statement works?

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?

Comments

  • 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);


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

    Thanks.

    Cheers
    Richard
  • Its been a week. Anyone at HP ? Is this a known problem ?
  • 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

  • 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







  • Any solution for this? I have the exact same issue.
  • 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

  • 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
  • Hi, Thanks,
    This problem exists also on regular php_odbc not only on pdo_odbc 
  • Ah, thanks for the update.  I'll be sure to pass that along.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.