Editor with Postgres, sql query ends with 'WHERE "ID" = $1', causes error

hzvend16

Using the Postgres library in editor, I keep getting an error and I can't figure out what's causing it or how to fix it.

Here is my javascript:

var editor = new $.fn.dataTable.Editor( {
                    ajax: "/ajax/editor.php",
                    table: "#db_table",
                    fields: [ 
                        {
                            label: "ID:",
                            name: "ID"
                        },
                        {
                            label: "Message:",
                            name: "Message"
                        },
                       ]
                } );

var table = $('#db_table').dataTable({
....
                                                                       "columns": [
                                        { "data": "ID" },
                                        { "data": "Message" }
                                    ],
....

Here is the Editor call in the editor.php file (which is just a modified version of the standard example server-side script):

Editor::inst( $db, '"Test"' )->pkey('"ID"')
    ->fields(
        Field::inst( '"Message"' )
    )
    ->process( $_POST )
    ->json();

When I submit the edit form, the following error is returned:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "undefined"
STATEMENT:  SELECT  "ID" as "ID", "Message" as "Message" FROM  "Test" WHERE "ID" = $1

I'm assuming the '$1' is supposed to be a variable of my submitted ID field but I don't know why it's not being populated.

Please help! Thanks!

allan

Could you try using:

Editor::inst( $db, "Test" )->pkey("ID")
    ->fields(
        Field::inst( "Message" )
    )
    ->process( $_POST )
    ->json();

It looks like your strings above where being double quoted for some reason.

Allan

hzvend16

Yes, this was done on purpose. The table/column names in our Postgres db have capital letters in them, requiring them to be encased in double quotes in the SQL statement. If I use your method, the capital letters in the table/column names get changed to lower case, causing an error. In order to keep the capitals and include the double quotes in the SQL, I encased them with single quotes in the Editor statements.

The ideal solution would be to remove the capitals from the table/column names but this is a production database with several hundred tables and a ton of scripting behind it, making that change a complete nightmare. I'm hoping to be able to find an alternative solution with the Editor (although my trial period just expired, lol).

So, to boil it down, I need the SQL statement to look like this:

SELECT  "ID" as "ID", "Message" as "Message" FROM  "Test" WHERE "ID" = 2;

allan

That's interesting - Editor's database classes shouldn't be performing any kind of case conversion on the database field names. I've just done a quick check locally and if I use an upper case field name, the SQL that Editor builds reflects that.

To check you can add the following to the end of the _prepare() function in php/Database/Drivers/Postgres/Query.php:

file_put_contents( '/tmp/sql', $sql."\n", FILE_APPEND );

That will output the SQL that Editor uses to the file specified (update the output file as you need).

There does appear to be a CASE_* attribute in PHP's PDO, might that be getting set in your configuration somewhere?

Regards,
Allan

hzvend16

I'm not hitting the CASE_* attribute anywhere. Postgres may be doing this because I'm not encasing the table/column names in double quotes. Either way, they need to be encased in double quotes to work properly so the output I'm currently generating is correct in that respect. Just not sure why I'm getting the 'WHERE "ID" = $1' clause instead of it including the actual posted id number. BTW, thanks for your help on this.

allan

The $1 is a bound parameter - the row id (or at least it should be).

Could you try simply ->pkey("ID") (keep your other fields as they are). I'm fairly certain that the issue is related to the double quoting - in the above case Editor will be looking for a primary key called "ID" (which is obviously isn't - its just ID).

Allan

hzvend16

Sorry for the delayed responses, this is a project for my part-time gig.

If I use ->pkey("ID"):

ERROR:  column "id" does not exist at character 9
STATEMENT:  SELECT  ID as ID, "Notes" as "Notes" FROM  "AdsLocations" WHERE ID =
 $1

If I use ->pkey('"ID"'):

ERROR:  column "id" does not exist at character 9
STATEMENT:  SELECT  id as id, "ID" as "ID", "Notes" as "Notes" FROM  "AdsLocatio
ns"