[Gambas-user] Problem with SQLite3

[Leonardo Miliani]

Benoit Minisini ha scritto:
> 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,
> 

YES. It works :-)
Thanks a lot :-))))

-- 
Ciao.
Leo.

Web: www.leonardomiliani.com
E-mail: leonardo at ...1237...
Scegli software opensource - Choose opensource software