Runtime Error: Column Name does not exist
lazarus / 16-Databases

----------------------------------------------------------------------
jrmit · 2012-11-05 19:36

I have encountered a runtime error trying to display Fields for a
selected Table using Postgresql/OS X 10.8. 

Error: PQConnection1: Preparation of query failed. (PostgreSQL: ERROR:
column "item" does not exist.  Line 1: ...name FROM
information_schema.columns WHERE table_name = item.

The query works if I replace the ComboBox.Text with hardcoded table
name 'item' (see below):

SQLQuery1.SQL.Text := 'SELECT column_name FROM
information_schema.columns WHERE table_name = ''item''';

Any idea what I am doing wrong?

Code: [Select]procedure TForm2.TBLComboBox1Change(Sender: TObject);
begin
  try
    ListBox1.Clear;
    Edit1.Text := TBLComboBox1.Text;
    SQLQuery1.SQL.Text := 'SELECT column_name FROM
information_schema.columns' +
    ' WHERE table_name = ' + TBLComboBox1.Text;
    ShowString('Retrieving Fields for Table ' + TBLComboBox1.Text);
    SQLQuery1.Open;
    while not SQLQuery1.EOF do begin
      ListBox1.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;
    SQLQuery1.Close;
    ShowString('List of Fields Received');
  except
    on E: Exception do
      MessageDlg (E.Message, mtError, [mbOK], 0);
  end;
end;

----------------------------------------------------------------------
eny · 2012-11-05 19:41

I'm guessing the answer is already in the message you posted: put
double quotes around the name.

Code: [Select] SQLQuery1.SQL.Text := 'SELECT column_name FROM
information_schema.columns' +
    ' WHERE table_name = "' + TBLComboBox1.Text + '"';

----------------------------------------------------------------------
jrmit · 2012-11-05 20:40

Thanks, Eny.  What a relief.

I don't know if this is how you meant me to handle the syntax, but I
found I had to use two single quotes for double quotes:

' WHERE table_name = ''' + TBLComboBox1.Text + '''';

  John

----------------------------------------------------------------------
Stag76 · 2012-11-08 09:09

You can also use this format:-

WHERE table_name = '+ QuotedSTR(TBLComboBox1.Text);

----------------------------------------------------------------------
BigChimp · 2012-11-08 09:47

Or better yet, parameterized queries: better performance (in almost
all cases), more secure.

See the wiki:
http://wiki.freepascal.org/SqlDBHowto#How_to_use_parameters_in_a_query
.3F
and
http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-
give-me-death.html


With parameterized queries you'll get something like (untested):
Code: [Select]procedure TForm2.TBLComboBox1Change(Sender: TObject);
begin
  try
    ListBox1.Clear;
    Edit1.Text := TBLComboBox1.Text;
    SQLQuery1.SQL.Text := 'SELECT column_name FROM
information_schema.columns' +
    ' WHERE table_name = :OurTable';
   
SQLQuery1.Params.ParamByName('OurTable').AsString:=TBLComboBox1.Text;
    ShowString('Retrieving Fields for Table ' + TBLComboBox1.Text);
    SQLQuery1.Open;
    while not SQLQuery1.EOF do begin
      ListBox1.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;
    SQLQuery1.Close;
    ShowString('List of Fields Received');
  except
    on E: Exception do
      MessageDlg (E.Message, mtError, [mbOK], 0);
  end;
end;

By the way, looking at your query, you might want to investigate
something like TPQConnection.GetSchemaInfoSQL with
SchemaType:=stColumns and SchemaObjectName:=TBLComboBox1.Text
... this would work for other sqldb-supported databases as well (I
know Firebird is supported, others may be too - if they're not, we
should improve it - I've still got MS SQL Server on my to do list )

----------------------------------------------------------------------
Leledumbo · 2012-11-08 09:49

For such error-free quoting, I suggest using Greyhound.

----------------------------------------------------------------------
jrmit · 2012-11-15 05:12

Thanks for all the suggestions.  The QuoteSTR function worked well. 
Like it alot better than all of the quotation marks.  Will look into
the parameter suggestion as well.

John

----------------------------------------------------------------------
goodname · 2012-11-15 14:13

If your going to continue to use quotedStr with PostgreSQL then be
sure that standard_conforming_strings is on. When off a string that
contains \' will not be properly escaped by quotedStr.

http://www.postgresql.org/docs/9.2/static/runtime-config-
compatible.html#GUC-STANDARD-CONFORMING-STRINGS