Recent

Author Topic: Bug with TPQConnection and SQL Queries at runtime  (Read 4541 times)

loopy

  • Newbie
  • Posts: 1
Bug with TPQConnection and SQL Queries at runtime
« on: October 07, 2006, 07:51:55 pm »
I'm running Lazarus 0.9.18 with FPC 2.0.4 on an Ubuntu Dapper machine.

I want to run SQL queries through a Postgres database.

The following query works perfectly when executed through the command line:

Code: [Select]

select actions.action as act, count from
        (select count(*) as count,action as actid
              from playhistory JOIN gameplayerinfo on  
              gameplayerinfo.id = playhistory.gameplayerid
              where stage=1 AND playerid=5        
              group by action  order by action) Q1
JOIN actions on Q1.actid=actions.id


with this sort of result:

 action | count
--------+-------
      1 |     6
      2 |    45
      3 |    31
      4 |     2
      6 |     9
      7 |    11

In Lazarus, I have a form with a TPQConnection, TSQLTransaction and TSQLQuery component (as well as a TDataset and TDBGrid to see the results).

If I paste the SQL query into the SQL field of TSQLQuery and make everything active AT DESIGN TIME, everything works as expected.
So the query is fine as far as I'm concerned.

However, my problem is at RUNTIME. If I execute the following code:

Code: [Select]

  SQLQuery1.SQL.Clear;
  SQLQuery1.SQL.LoadFromFile('sql/actionAggregates.sql');
  SQLQuery1.Open;

Note: actionAggregates.sql contains the SQL query above.

I get the following error:
TApplication.HandleException PQConnection1 : Preparation of query failed. (PostgreSQL: ERROR:  syntax error at or near "Q1" at character 188)

I've tried rewording the query in many different ways; tried assigning it directly to the SQL field, but nothing helps.

Since I'm struggling to find much documentation for TPQConnection, I thought I'd post this here for help before submitting a bug report.

Thanks in advance