Tracking down weird errors with database PreparedStatements

Databases
1

I am working with PostgresPreparedStatements at the moment and I am running into some issues. Odd issues (and YES it is the programmer and not the framework). I do things like below

// strUUID = string
// personsName = string
// dateNow = date (object)
pgSQLStatement = pgDatabaseConnection.Prepare( modSQL.kSQL_createEntry )
pgSQLStatement.Bind(0, strUUID     )
pgSQLStatement.Bind(1, personsName )
pgSQLStatement.Bind(2, dateNow )

and it throws an error 22. Which is that maps back to Postgress Error Class 22, it is a data mismatch error. (there error codes is 4-5 digits in length)

Now my question has nothing to do with the bad code above. Is there a way to see what the SQL statement is in a prepared statement once all the binding have happened? tracking down what I have done wrong is a PITA. And if could just see the SQL at runtime that would be ten kinds of awesomeness.

-sb

2

Bind of a date is probably the error all by itself

3

I agree, it would make debugging so much easier. I haven’t found a way of seeing the Prepared statement with bindings (a SQL injection version), so I write a manual SQL injection binding which would show, as Norman states, the issue. That is a lot of work to find a small issue though.

4

I dont disagree with you.
I dont doubt you.

I am just trying to figure out how to inject the proper date format in the SQL bindings so I an insert the data into the database. And cuz little issues like this, I am pulling out what little hair I had left.

5

What does “seeing the Prepared statement with bindings” mean ???
Do either / both of you mean “show me the statement with the substituted values in their respective places” ?
Unless the database API has such a facility, which most API’s dont, there’s nothing to see
The API literally sends the sql with the markers in it then transfers the data in binary form usually to the “server” (or db engine in the case of SQLite and it handles everything)

But this particular error is not an error in VALUES bound - its bind TYPES
There is no bind type for “Xojo date” for any db

FWIW most prepared statement cannot take a Xojo DATE as a parameter
Passing a string that is in the format the DB can accept as a date should work

6

[quote=348821:@Norman Palardy]What does “seeing the Prepared statement with bindings” mean ???
Do either / both of you mean "show me the statement with the substituted values in their respective places” ?[/quote]
yes that is what I mean…

[quote=348821:@Norman Palardy]Unless the database API has such a facility, which most API’s dont, there’s nothing to see
The API literally sends the sql with the markers in it then transfers the data in binary form usually to the “server” (or db engine in the case of SQLite and it handles everything)[/quote]

never said it was a Xojo issue. Just said I wished I knew how to see it.

[quote=348821:@Norman Palardy]But this particular error is not an error in VALUES bound - its bind TYPES
There is no bind type for “Xojo date” for any db

FWIW most prepared statement cannot take a Xojo DATE as a parameter
Passing a string that is in the format the DB can accept as a date should work[/quote]

very true. I didn’t know if the DB driver translated the date into the proper formatting or not. Good to know that it doesn’t take a date object.

7

There’s almost no way that I can think of to see it IN Xojo
Postgresql logging might permit you to see whats going on
https://www.postgresql.org/docs/9.0/static/runtime-config-logging.html
see
log_min_error_statement (enum)
log_statement (enum)

8

You might try SQLdeLite http://www.github.com/1701software/SQLdeLite

It handles prepared statements for you with all the appropriate Xojo data types.

9

Scott, is there an error message to go along with the code? Can we see the SQL in the constant?

10

I didn’t think it would be possible, but it would make debugging a heap easier.

11

Different DB engine may provide different ways to view this IF they provide them at all
Few have any way for a client program like Xojo (or the app you write) to view the SQL that the execution would be equivalent to

And this isn’t the only language where this has been asked for
Variations of this have been asked elsewhere

12 
INSERT INTO tokentable( uuid,personname,lastaccessed ) VALUES ( '$1', '$2', '$3')
13

me too. but I can always hope and dream…

14

[quote=348846:@Norman Palardy]Different DB engine may provide different ways to view this IF they provide them at all
Few have any way for a client program like Xojo (or the app you write) to view the SQL that the execution would be equivalent to

And this isn’t the only language where this has been asked for
Variations of this have been asked elsewhere
https://stackoverflow.com/questions/2382532/how-can-i-get-the-sql-of-a-preparedstatement[/quote]

I feel a little better that other languages don’t have it either. Just a little.

per your earlier suggestion, I am playing around with the logging on the postgresql server and seeing what it says… lots of data, until it gets to that insert, then it says…. with user/database is redacted.

2017-09-03 22:13:36 UTC [1781-9] ***@********** ERROR:  invalid input syntax for type timestamp with time zone: "$3" at character 88

That is when I changed the schema to use “timestamp with timezone”.

this is previous with timestamp without timezone.

2017-09-03 20:43:01 UTC [1259-1] ***@********** ERROR:  invalid input syntax for type timestamp: "$3" at character 88
15

I dont think you’re going to be able to directly bind a Xojo date

Bind it as s string and pass an appropriately formatted string
like “2004-10-19 10:23:54+02”

16

from my experiences, you can. If you format the date string by hand, please take care of using the proper Date Style and Time Zone settings for the session.

that looks like the server is trying to parse a literal “$3”. Maybe you did accidentally quote the placeholder in the SQL string?
If you enable full logging with the following options, you should get the full SQL statement and the content of placeholders in the server log to explore.

db.SQLExecute("SET log_statement TO 'all';") db.SQLExecute("SET log_min_messages To 'info';") db.SQLExecute("SET log_min_error_statement To 'info';")

17

I know this isn’t really the real question, but for dates i would do:

pgSQLStatement.Bind(2, "now()")

or

pgSQLStatement.Bind(2, dateNow.SQLDateTime )
18

Do you need the quotes around $1, $2, and $3? I think the prepared statement handles the quoting, but I might be wrong…

19

No, you’re right. No quotes.

20

that was my first thought as well:

sorry I didn’t notice the SQL in question was already posted in the thread. I’m pretty sure that’s the reason for the error. Column uuid seems not to be defined with type uuid otherwise “$1” would not be accepted as input for this column as well.