Recent

Author Topic: [solved] How to pass the value of a variable to a column ? SQLITE  (Read 2490 times)

zoimutante

  • New Member
  • *
  • Posts: 31
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.
« Last Edit: April 05, 2016, 08:36:50 am by zoimutante »

Thaddy

  • Hero Member
  • *****
  • Posts: 8679
Re: How to pass the value of a variable to a column ? SQLITE
« Reply #1 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.  
« Last Edit: April 05, 2016, 08:01:26 am by Thaddy »
Most people that want to use threading should learn to patch their jeans first: use a needle.

GetMem

  • Hero Member
  • *****
  • Posts: 3495
Re: How to pass the value of a variable to a column ? SQLITE
« Reply #2 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.

zoimutante

  • New Member
  • *
  • Posts: 31
Re: How to pass the value of a variable to a column ? SQLITE
« Reply #3 on: April 05, 2016, 08:14:39 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 :)

zoimutante

  • New Member
  • *
  • Posts: 31
Re: How to pass the value of a variable to a column ? SQLITE
« Reply #4 on: April 05, 2016, 08:20:39 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 ?

GetMem

  • Hero Member
  • *****
  • Posts: 3495
Re: How to pass the value of a variable to a column ? SQLITE
« Reply #5 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

Thaddy

  • Hero Member
  • *****
  • Posts: 8679
Re: [solved] How to pass the value of a variable to a column ? SQLITE
« Reply #6 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.
Most people that want to use threading should learn to patch their jeans first: use a needle.