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

[Michael Van Canneyt]

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.