View contents of preparedstatement

Hi all,

I’m trying to find out if there is a way to view the final contents of a preparedstatement after the bindings are completed, so I can see exactly what is being sent to the database (SQLite).

I have tried using Str(ps) but to no avail.

Is there a known way of doing this ?

Thanks.

Can’t even drill down into it in the debugger–I was banging my head against this a lot over the last few days :frowning:

[quote=391882:@Stephen Thomas]Hi all,

I’m trying to find out if there is a way to view the final contents of a preparedstatement after the bindings are completed, so I can see exactly what is being sent to the database (SQLite).

I have tried using Str(ps) but to no avail.

Is there a known way of doing this ?

Thanks.[/quote]

There isn’t because it doesn’t get turned into a string by replacing the markers with the values that are bound. The values are sent to the server (in the case of db servers like PostgeSQL, mysql, etc) via the API’s their clients provide - and this is usually in binary form. The server would have to have an API to return the statement - most do not have such an API.

It looks like they have added this to SQLite:

https://sqlite.org/c3ref/expanded_sql.html

Is there a reason that the debugger can’t show the unbound statement, and then, for each parameter, the bindtype and value in a table before those three things are sent to the server? That should be doable and enough to far more easily troubleshoot.

It’s sometimes practical to generate the bindtypes and plug in the values dynamically (using a loop with IF or SELECT statements to specify the bindtypes and values) instead of line-by-line.

When each binding is done line-by-line or with a parameter array (for servers that support it), it’s usually not too hard to debug–but being able to view raw (unbound) statement, bindtypes and values in the debugger would be really valuable for dynamically generated stuff.

Its an interesting conundrum.

I have SQL query code here that runs perfectly in SQLite Manager.

Trying to build said code in a preparedstatement is proving to be an absolute pig.

SELECT * FROM Transactions WHERE TransactionDate BETWEEN '2017-11-27' AND date('2017-11-27', '+27 Days')

[quote=391889:@Stephen Thomas]Its an interesting conundrum.

I have SQL query code here that runs perfectly in SQLite Manager.

Trying to build said code in a preparedstatement is proving to be an absolute pig.

SELECT * FROM Transactions WHERE TransactionDate BETWEEN '2017-11-27' AND date('2017-11-27', '+27 Days')

how are you trying to do this… I can’t think of a reason off the top of my head that it can’t be made to work

My actual code is this :

Dim DisplayDataPS As SQLitePreparedStatement = FinanceDB.Prepare("SELECT Transactions.PKID, AccountDescription, TransactionDate, TransactionAmount, TransactionReference, CompanyDescription FROM Account, Company, Transactions WHERE TransactionDate BETWEEN ? AND ? AND TransactionAccount=Account.PKID AND TransactionCompany=Company.PKID ORDER BY AccountDisplayOrder") DisplayDataPS.BindType(0, 3) DisplayDataPS.BindType(1, 3) Dim StartString As Text = "2017-11-27" Dim EndString As Text = "date(`2017-11-27`,`+27 Days`)" DisplayDataPS.Bind(0, StartString) DisplayDataPS.Bind(1, EndString) Dim DisplayDataRS As RecordSet = DisplayDataPS.SQLSelect

It returns nothing.

If I change StartString and EndString to normal SQL dates it works fine. I’m convinced its the EndString binding that is causing my issue but without being able to view the finished preparedstatement its difficult to track down what is wrong.

Does it work if bind three separate variables and change your prepared statement to something like:

... WHERE TransactionDate BETWEEN ? AND DATE(?,?) AND ...

two things wrong that I can see…

  • you have the wrong type of single quotes ` [0x60] instead of ’ [0x27]
  • I believe attempting to split a function isn’t the way PS parser works… its NOT a direct string replacement

perhaps

Dim StartString As Text = "2017-11-27"
Dim EndString As Text = "27"

.. WHERE TransactionDate BETWEEN ? AND DATE(?,+? days)

and pass StartString twice

I think I have it, subject to more testing. I used 3 bindings and its the content of the 3rd one that makes it work. It should not contain only the number of days, but the full interval i.e ‘+27 days’.

StartString = “2017-11-27”
EndString = “2017-11-27”
DateInterval = “+27 days”

DisplayDataPS.Bind(0, StartString)
DisplayDataPS.Bind(1, EndString)
DisplayDataPS.Bind(2, DateInterval)

It means I can change it to supply the single date and bind it twice which is cleaner and less error prone, as suggested by Dave.

StartDateString = “2017-11-27”
DateInterval = “+27 days”

DisplayDataPS.Bind(0, StartDateString)
DisplayDataPS.Bind(1, StartDateString)
DisplayDataPS.Bind(2, DateInterval)

The preparedstatement code at the insertion points :

WHERE TransactionDate BETWEEN ? AND DATE(?,?)

I think Jared had the right idea with using 3 bindings.

I was attempting to enclose in single quotes (Also tried backticks) to match the SQLite Manager code that works :

SELECT * FROM Transactions WHERE TransactionDate BETWEEN '2017-11-27' AND date('2017-11-27', '+27 Days')

Enclosing in these quotes and backticks, and putting the ‘+’ and ‘days’ in the statement rather than the binding values is the cause of the failure.

This corrected approach has 2 advantages :

I get more flexibility in respect of the DateInterval. You can use the usual range of days, weeks, months, years etc, with the usual mathematical operators. I would guess its the same when using SQLDateTime format too.

My code is much cleaner and simpler because I don’t have to declare the dates and perform the dateinterval calculation in code prior to passing the values for binding. I like the fact I can now hand off the calculation part to the database itself.

I’ve certainly learned something new today.

Thanks all for your input :slight_smile: