[Gambas-user] Problem with SQLite3

[Benoit Minisini]

On Saturday 27 January 2007 16:21, Leonardo Miliani wrote:
> I don't know if it's me what I'm wrong, or Gambas or SQLite3 but I'm
> having an annoying problem with a SQLite3 query.
> I have an SQLite3 DB with a table named "pagamenti" (payments). Inside
> it I have a list of payments with some fields. Some of them are
> "saldato" (payed), "data" (date), "cliente" (customer).
> The field "data" is a Date field.
>
> For some reason I have the necessity to extract all the datas that are
> still unpayed (I use the field "saldato" as a boolean check to tell me
> if the payment has been done or not) and order them by fields "cliente"
> and "data".
> So I use this piece of code:
>
> Private Function Extract_data(Customer as string, PayDate as date) as
> boolean
> Dim hResult as Result
> Dim hConnection as new Connection
>
>   hConnection.Name = Application.Path & "/archive"
>   hConnection.Type = "sqlite3"
>   hConnection.Open
>
>   hResult = hConnection.Exec("SELECT * FROM pagamenti WHERE cliente = '"
> & Customer & "' AND data = #" & PayDate & "# AND NOT saldato ORDER BY
> cliente,data")
>
>   IF hResult.Available = FALSE THEN
>     RETURN FALSE
>   END IF
> ......
> ......
>   RETURN TRUE
> END
>
>
> When I call the function I always get FALSE as return value because the
> SQLite query doesn't get any match. It seems that the use of the date
> value is a bit difficult.
> I know (or mayber I thinked to know...) that in SQL the dates must be
> put inside 2 chars '#' but this notation seems not to work in
> Connection.Exec...
> In fact, if I use this SQL query:
>
> "SELECT * FROM pagamenti WHERE cliente = '" & Customer & "' AND NOT
> saldato ORDER BY cliente,data"
>
> (just taking away the check on the date) I get results.

You must use the quoting features of the Exec() function:

hResult = hConnection.Exec("SELECT * FROM pagamenti WHERE cliente = &1"
  & " AND data = &2" & "AND NOT saldato ORDER BY
  cliente,data", Customer, PayDate)

The database driver will transform the string and the date into something that 
the SQL interpreter will understand.

Regards,

-- 
Benoit Minisini