Lazarus

Programming => Databases => Topic started by: zoimutante on April 05, 2016, 07:42:53 am

Title: [solved] How to pass the value of a variable to a column ? SQLITE
Post by: zoimutante on April 05, 2016, 07:42:53 am

SQLite3Connection1.ExecuteDirect('insert into DATA (Name,Phone) values ("nome","telefone");');

That is the code, I have 2 variables called nome and telefone, I try to pass the content of the variables to the columns, nome to column Name, and telephone o the column Phone, but it is not happening. The columns are full of "nome" and "telefone", the literal names of the variables, not the contents of it.

Title: Re: How to pass the value of a variable to a column ? SQLITE
Post by: Thaddy on April 05, 2016, 07:54:06 am

That wrong SQL for what you want. Just use the variable names , no quotes.
If the nome and telephone are edit controls refer to the text property.
Something like one of these (not tested, but should be close)

Code: MySQL  [Select][+][-]

1. --- as variables
2. insert into DATA (name,Phone) values( nome, telephone)
3. --- or
4. insert into DATA(name,Phone) values (nome.text, telephone.text)
5.  

Title: Re: How to pass the value of a variable to a column ? SQLITE
Post by: balazsszekely on April 05, 2016, 08:02:41 am

To avoid problems, you should always use parameterized queries. Since you want to insert the data directly, try something like this:

Code: Pascal  [Select][+][-]

1. var
2.   Nome, Telefone: String;
3. begin
4.   Nome := 'John';
5.   Telefone := '0785-343434344l';
6.   SQLite3Connection1.ExecuteDirect('insert into DATA (Name,Phone) values (' + QuotedStr(Nome) + ',' + QuotedStr(Telefone) + ');');
7. end;

                                                                                                                                 

If both fields are varchar it should work.

Title: Re: How to pass the value of a variable to a column ? SQLITE
Post by: zoimutante on April 05, 2016, 08:14:39 am

Quote from: Thaddy on April 05, 2016, 07:54:06 am

That wrong SQL for what you want. Just use the variable names , no quotes.
If the nome and telephone are edit controls refer to the text property.
Something like one of these (not tested, but should be close)

Code: MySQL  [Select][+][-]

1. --- as variables
2. insert into DATA (name,Phone) values( nome, telephone)
3. --- or
4. insert into DATA(name,Phone) values (nome.text, telephone.text)
5.  

It don't work in my case, at run time i get a error at execution... complain about a there is no column named name... but thank for the fast answer :)

Title: Re: How to pass the value of a variable to a column ? SQLITE
Post by: zoimutante on April 05, 2016, 08:20:39 am

Quote from: GetMem on April 05, 2016, 08:02:41 am

To avoid problems, you should always use parameterized queries. Since you want to insert the data directly, try something like this:

Code: Pascal  [Select][+][-]

1. var
2.   Nome, Telefone: String;
3. begin
4.   Nome := 'John';
5.   Telefone := '0785-343434344l';
6.   SQLite3Connection1.ExecuteDirect('insert into DATA (Name,Phone) values (' + QuotedStr(Nome) + ',' + QuotedStr(Telefone) + ');');
7. end;

                                                                                                                                 

If both fields are varchar it should work.

It work but phone column is a integer, and the variable also a integer...  I need to change the type of the variable to work. There is a QuotedInteger, or something like that to pass a integer to the column ? What is parameterized query ? some commands used by tsqlquery ?

Title: Re: How to pass the value of a variable to a column ? SQLITE
Post by: balazsszekely on April 05, 2016, 08:28:29 am

Quote

It work but phone column is a integer, and the variable also a integer...  I need to change the type of the variable to work.

See my comment about parameterized queries.

Quote

There is a QuotedInteger, or something like that to pass a integer to the column ?

There is no such thing as QuotedInteger. Try this(cross your fingers :D) :

Code: Pascal  [Select][+][-]

1. var
2.    Nome: String;
3.    Telefone: Integer;
4. begin
5.    Nome := 'John';
6.    Telefone := 123455656;
7.    SQLite3Connection1.ExecuteDirect('insert into DATA (Name,Phone) values (' + QuotedStr(Nome) + ',' + IntToStr(Telefone) + ');');
8. end;
9.  

Quote

What is parameterized query ? some commands used by tsqlquery ?

Yes! Please read this: http://wiki.freepascal.org/Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters

Title: Re: [solved] How to pass the value of a variable to a column ? SQLITE
Post by: Thaddy on April 05, 2016, 08:56:42 am

As I wrote I didn't test it, but indeed it should be (in the case of variables):

Code: MySQL  [Select][+][-]

1. insert into DATA (name,Phone) values( :nome, :telephone)
2.  

I wonder if that works with executedirect, though. I think you should use an SqlQuery or the like for that to work.