Problem with Zeos components regarding ':' in sql statement.

[CHeighlund]

I'm working on a program designed for deleting some files from a postgresql database. We've already got a copy of this in perl, but my boss says the perl one sometimes deletes records it shouldn't be deleting; I thought I'd recode in delphi and see if that had the same problem, or at least see if that got rid of the same records or not.

I'm using the Zeos components TZConnection and TZQuery for the database side, but I'm running up against an error in communications somewhere along the way. The program is only supposed to look at records older than two years from the present; the perl-side prog handled this with a "dt < now() - '2 years'::interval" statement, but the delphi program I'm working with blargs at one of the : characters; the error is 'syntax error at or near ":" at character 509'. I know the code works if put straight into the db, as I spun out a memo box to catch it, then copied the contents of the memo straight into the database. It took a while (a lot of records match the criteria), but it ran without any errors as far as I can tell...that is, I got meaningful results back.

Does anyone know what I would need to do to handle this? Are there other components that can handle the ::interval effect I need, or should I be looking into ways to work around that?

 

[lespaul]

what's your actual code?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[towerbase]

What version of the Zeos components are you using? The Core Lab website shows:

2.50.19 23-Aug-06
* Fixed bug with filling DataSet with Interval type data
* Fixed bug with retrieving relations in DataSet Wizard

which might be relevant.

However, I would have thought it would be pretty easy to modify the SQL statement to avoid using the ::interval syntax.

You say that I spun out a memo box to catch. In this kind of situation, I tend to copy what I require straight into the clipboard by using code such as

uses Clipbrd;
...
if SuspectSQLcondition then begin
  Clipboard.AsText := SuspectSQL;
  ShowMessage( 'Captured Suspect SQL' );
end;

and then paste it into the database using Ctrl+V.


Andrew
Hampshire, UK

 

[AP81]

Yep, as towerbase said it is much better to output the SQL and debug it in your SQL client program.

I use MySQL Control Center (is free).

You may want to have a look at the MySQL date and time function to come up with an equivalent SQL syntax. Take a look at this:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Alternatively, you can format the date and time using delphi's datetime functions, i.e.

uses DateUtils
...

var 
 twoYearsAgo : TDateTime;
begin
  // substract 2 years from now
  twoYearsAgo := IncYear(now, -2);
 
  SQL := '...'
  SQL := SQL + 'WHERE dt < twoYearsAgo'

 

[CHeighlund]

Thank you for the suggestions. I implemented a delphi-side solution for myself, albeit built around the negative IncMonth() rather than the negative IncYear(); I'm hoping to be able to use that elsewhere should the need arise.

Again, thank you for the advice and suggestions.