[fpc-pascal] How to clear the value of a field in a table using parameters?

silvioprog silvioprog at gmail.com
Thu Aug 29 19:52:27 CEST 2013


2013/8/29 silvioprog <silvioprog at gmail.com>

> 2013/8/29 Michael Van Canneyt <michael at freepascal.org>
>
>>  On Thu, 29 Aug 2013, silvioprog wrote:
>>
>>  Hello,
>>> I'm trying to do something with a dataset, but I think that isn't
>>> possible.
>>>
>>> Assuming I have the following table:
>>>
>>> create table users (
>>> id serial not null primary key,
>>> email varchar(100),
>>> nickname varchar(20) not null
>>> );
>>>
>>> And the following records:
>>>
>>> id | email | nickname
>>> 1 | user1 at domain.com | user1
>>> 2 | user2 at domain.com | user2
>>> 3 | user3 at domain.com | user3
>>>
>>> I want to clear the email of the second user, but using parameters:
>>>
>>> id | email | nickname
>>> 1 | user1 at domain.com | user1
>>> 2 | <null> | user2
>>> 3 | user3 at domain.com | user3
>>>
>>> But when I try this:
>>>
>>>   SQLQuery1.SQL.Add('update users set email = :email');
>>>   SQLQuery1.SQL.Add('where id = :id');
>>>   SQLQuery1.ParamByName('id').**AsInteger := 2;
>>>   SQLQuery1.ParamByName('email')**.Clear;
>>>   SQLQuery1.ExecSQL;
>>>   SQLTransaction1.Commit;
>>>
>>> I got:
>>>
>>> "Project project1 raised exception class "EDatabaseError" with message:
>>> PQConnection1: Unknown fieldtype for parameter "email"."
>>>
>>> I know that I could solve easily via literal string, but I need to use
>>> parameters anyway.
>>>
>>> How I solve this?
>>>
>>> Here in Brazil, a friend suggested this:
>>>
>>>   VParam := SQLQuery1.ParamByName('email')**;
>>>   VParam.DataType := ftString;
>>>   VParam.Clear
>>>
>>> Is this the only way? '-'
>>>
>>
>> Yes and no.
>>
>> In 2.6.2 this is the only way.
>>
>> There is a fix in SVN that allows you to simply do clear, and it will use
>> then the "any"
>> field type of postgres (or something similar).
>>
>> Michael.
>
>
> I'll try the trunk version. Thank you very much Michael! (y)
>

Other interesting feature is the property EmptyAsNull:

http://docwiki.embarcadero.com/VCL/XE/en/IBCustomDataSet.TIBStringField.EmptyAsNull

It tests if field (ftString) is empty (f = ''), if yes, it set the field
value to null (f.Clear).

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20130829/b1875808/attachment.html>


More information about the fpc-pascal mailing list