Re: [lazarus] How to debug when accessing database with sqldb
On 10/6/06, Adrian Maier [EMAIL PROTECTED] wrote: On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). What you could do: compile the sqldb-unit with debuginfo and place a breakpoint on line 1181. And then take a look at qry.sql.text. Or simply place a 'writeln(qry.sql.text);' on that line. Recompile fpc and try again... I was hoping that recompiling sqldb wasn't neccessary . But if there's no other way ... I have added some writelns inside pqconnection.pp just before the PQexec calls. The query that causes the error is : ' prepare prepst2 (int,int,numeric,text,int,text,int) as update PROD set mag=$1,cod=$2,pu=$3,den=$4,p_vinz=$5,um=$6,stoc_ini=$7 where ' Which is incomplete... It looks liek it wasn't able to detect the columns that form the primary key. This table has a PK made up of 3 columns. Can sqldb handle multi-column primary keys ? -- Adrian Maier _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
On 10/6/06, Adrian Maier [EMAIL PROTECTED] wrote: On 10/6/06, Adrian Maier [EMAIL PROTECTED] wrote: On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). What you could do: compile the sqldb-unit with debuginfo and place a breakpoint on line 1181. And then take a look at qry.sql.text. Or simply place a 'writeln(qry.sql.text);' on that line. Recompile fpc and try again... I was hoping that recompiling sqldb wasn't neccessary . But if there's no other way ... I have added some writelns inside pqconnection.pp just before the PQexec calls. The query that causes the error is : ' prepare prepst2 (int,int,numeric,text,int,text,int) as update PROD set mag=$1,cod=$2,pu=$3,den=$4,p_vinz=$5,um=$6,stoc_ini=$7 where ' Which is incomplete... It looks liek it wasn't able to detect the columns that form the primary key. This table has a PK made up of 3 columns. Can sqldb handle multi-column primary keys ? Apparently it doesn't :-( or it doesn't work for postgres. Replacing the 3-columns primary key with a single-column primary key caused the problem to disappear. -- Adrian Maier _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
On Fri, 2006-10-06 at 14:33 +0300, Adrian Maier wrote: On 10/6/06, Adrian Maier [EMAIL PROTECTED] wrote: On 10/6/06, Adrian Maier [EMAIL PROTECTED] wrote: On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). What you could do: compile the sqldb-unit with debuginfo and place a breakpoint on line 1181. And then take a look at qry.sql.text. Or simply place a 'writeln(qry.sql.text);' on that line. Recompile fpc and try again... I was hoping that recompiling sqldb wasn't neccessary . But if there's no other way ... I have added some writelns inside pqconnection.pp just before the PQexec calls. The query that causes the error is : ' prepare prepst2 (int,int,numeric,text,int,text,int) as update PROD set mag=$1,cod=$2,pu=$3,den=$4,p_vinz=$5,um=$6,stoc_ini=$7 where ' Which is incomplete... It looks liek it wasn't able to detect the columns that form the primary key. This table has a PK made up of 3 columns. Can sqldb handle multi-column primary keys ? Apparently it doesn't :-( or it doesn't work for postgres. Indeed. Somewhere in the code there's a comment like: 'ToDo: Multiple fields-indexes should be parsed here...' Replacing the 3-columns primary key with a single-column primary key caused the problem to disappear. Could you write a bug-report for it? Also mention that it should generate an error, if something like this happens. Instead of sending incomplete requests to the db-server. As a temporary solution you can do: sqlquery1.updatesql := 'update PROD set mag=$1,cod=$2,pu=$3,den= $4,p_vinz=$5,um=$6,stoc_ini=$7 where ...'; But then complete, offcourse. That way it doesn't try to generate the update-query automatically, but it uses the query that you provide. -- Met vriendelijke groeten, Joost van der Sluis CNOC Informatiesystemen en Netwerken http://www.cnoc.nl _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
The query that causes the error is : ' prepare prepst2 (int,int,numeric,text,int,text,int) as update PROD set mag=$1,cod=$2,pu=$3,den=$4,p_vinz=$5,um=$6,stoc_ini=$7 where ' Which is incomplete... It looks liek it wasn't able to detect the columns that form the primary key. This table has a PK made up of 3 columns. Can sqldb handle multi-column primary keys ? Apparently it doesn't :-( or it doesn't work for postgres. Indeed. Somewhere in the code there's a comment like: 'ToDo: Multiple fields-indexes should be parsed here...' Replacing the 3-columns primary key with a single-column primary key caused the problem to disappear. Could you write a bug-report for it? Also mention that it should generate an error, if something like this happens. Instead of sending incomplete requests to the db-server. Ok, I will . As a temporary solution you can do: sqlquery1.updatesql := 'update PROD set mag=$1,cod=$2,pu=$3,den= $4,p_vinz=$5,um=$6,stoc_ini=$7 where ...'; But then complete, offcourse. That way it doesn't try to generate the update-query automatically, but it uses the query that you provide. I'll try this approach. Many thanks for your help, Joost ! Cheers, Adrian Maier _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
[lazarus] How to debug when accessing database with sqldb
Hello, I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). Enabling the statement logging in postgres doesn't help in this case, because the sql command is not recognised at all, and therefore it doesn't show up in the log. Cheers, Adrian Maier _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote:
I'm using Sqldb to access a PostgreSQL database. Is it possible to find out
easily what sql commands is sqldb generating and trying to execute ?
I have a DBgrid which is associated to a table.
After updating a row and executing query.ApplyUpdates , the program generates
an exception :
An error occurred while applying the updates in a query: preparation of query
failed. (PostgreSQL: ERROR: syntax error at end of input at character 132).
Enabling the statement logging in postgres doesn't help in this case,
because the
sql command is not recognised at all, and therefore it doesn't show
up in the log.
It's more then 132 characters long, so it should be in the log?
The size is not important. I meant that i've configured postgres to
write in the log
all the queries that are executed, for debugging. Yet, the query
which generates
the error is not recognized as a query at all, and only the error
appears in the
log.
Since it's with applyupdates. The SQL is probably parsed incorrectly.
You can look what the updatequery.sql, modifyquery.sql etc. are. Maybe
that that already reviels the problem.
I'm afraid that i still don't understand how can I see those queries.
What/where
are the update.sql, modifyquery.sql that you are referring to?
In the program i have the following variables:
- Fconnection : tSQLConnection;
- Ftransaction : tSQLTransaction;
- query1: TSQLQuery;
- ds1: TDatasource;
- DBGrid1: TDBGrid;
- DBNavigator1: TDBNavigator;
The initialisation:
CreateFConnection;
CreateFTransaction;
ds1.Enabled:=false;
query1.Active:=false;
Fconnection.Transaction:=Ftransaction;
query1.DataBase:=Fconnection;
query1.Transaction:=Ftransaction;
query1.SQL.Clear;
query1.SQL.Add('select
DEN,CNP,C_FISCAL,LOC,REG_COM,JU,CONT,BANCA,ADR,COD_SOC from SOC_M');
query1.Active:=true;
DBGrid1.DataSource:=ds1;
ds1.DataSet:=query1;
ds1.Enabled:=true;
DBGrid1.Enabled:=true;
The user updates some values in the grid, and then presses a button that does :
query1.ApplyUpdates; --- the exception is generated here
Ftransaction.CommitRetaining;
I use identical code for other two tables - and updating works ! I
have no idea
what is different about this table.
Cheers,
Adrian Maier
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
On Thu, 2006-10-05 at 17:19 -0400, Adrian Maier wrote: On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). Enabling the statement logging in postgres doesn't help in this case, because the sql command is not recognised at all, and therefore it doesn't show up in the log. It's more then 132 characters long, so it should be in the log? The size is not important. I meant that i've configured postgres to write in the log all the queries that are executed, for debugging. Yet, the query which generates the error is not recognized as a query at all, and only the error appears in the log. Can't you specify to log all the commands which are sent? Since it's with applyupdates. The SQL is probably parsed incorrectly. You can look what the updatequery.sql, modifyquery.sql etc. are. Maybe that that already reviels the problem. I'm afraid that i still don't understand how can I see those queries. What/where are the update.sql, modifyquery.sql that you are referring to? Seems that they aren't public. What you could do: compile the sqldb-unit with debuginfo and place a breakpoint on line 1181. And then take a look at qry.sql.text. Or simply place a 'writeln(qry.sql.text);' on that line. Recompile fpc and try again... Joost _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] How to debug when accessing database with sqldb
On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: On Thu, 2006-10-05 at 17:19 -0400, Adrian Maier wrote: On 10/5/06, Joost van der Sluis [EMAIL PROTECTED] wrote: I'm using Sqldb to access a PostgreSQL database. Is it possible to find out easily what sql commands is sqldb generating and trying to execute ? I have a DBgrid which is associated to a table. After updating a row and executing query.ApplyUpdates , the program generates an exception : An error occurred while applying the updates in a query: preparation of query failed. (PostgreSQL: ERROR: syntax error at end of input at character 132). Enabling the statement logging in postgres doesn't help in this case, because the sql command is not recognised at all, and therefore it doesn't show up in the log. It's more then 132 characters long, so it should be in the log? The size is not important. I meant that i've configured postgres to write in the log all the queries that are executed, for debugging. Yet, the query which generates the error is not recognized as a query at all, and only the error appears in the log. Can't you specify to log all the commands which are sent? This is precisely what I've tried. All the other statements are written in postgres's log, but the query we are talking about is not logged. Apparently it is very broken and parsing it fails at a very early stage , therefore PostgreSQL doesn't consider it to be a statement . Since it's with applyupdates. The SQL is probably parsed incorrectly. You can look what the updatequery.sql, modifyquery.sql etc. are. Maybe that that already reviels the problem. I'm afraid that i still don't understand how can I see those queries. What/where are the update.sql, modifyquery.sql that you are referring to? Seems that they aren't public. What you could do: compile the sqldb-unit with debuginfo and place a breakpoint on line 1181. And then take a look at qry.sql.text. Or simply place a 'writeln(qry.sql.text);' on that line. Recompile fpc and try again... I was hoping that recompiling sqldb wasn't neccessary . But if there's no other way ... Thanks Joost ! Adrian Maier _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
