Hi Folks,

Im updating an application from DBIsamV3 to V4 and found a date formatting issue I can't seem to solve.

Here is what I'm using in V3 which works:

         qryVarious.Close;
         qryVarious.SQL.Clear;
         qryVarious.SQL.Add('INSERT INTO EquipmentSchedule');
         qryVarious.SQL.Add('(Date,"Project ID","Item ID",Qty,QtyQ)');
         //qryVarious.SQL.Add('SELECT "'+ FormatDateTime('yyyy-mm-dd',(tblProjectsStartingDate.value + DayVar)) +'","Project ID", "Component ID",SUM(Qty),0');
         qryVarious.SQL.Add('From ProjectEquipmentCompLink');
         qryVarious.SQL.Add('Where "Project ID" = :ProjID');
         qryVarious.SQL.Add('Group By "Component ID"');
         qryVarious.Params.Items[0].Value := dtmProjects.tblProjectsProjectID.Value;
         showMessage(qryVarious.SQL.Text ) ;
         qryVarious.Prepare;
         qryVarious.ExecSQL;

The double quotes are now no good for V4 so I changed the line to;
         qryVarious.SQL.Add('SELECT '+ QuotedStr(FormatDateTime('yyyy-mm-dd',(tblProjectsStartingDate.value + DayVar))) +',"Project ID", "Component ID",SUM(Qty),0');

but now I get the error:
DBISAM Engine error # 11949 SQL Parsing error - Expected NULL, Date, or Timestamp expression but instead found Expression in SELECT SQL statement at line 3, column 8

What is the correct way to format the date in DBISAM V4 please?

Thanks in advance for your help.

Cheers, Mark Gibson

Mark


Your formatting looks right to me. I'm well out of practice with DBISAM but I think Date is a reserved word and should be wrapped in double quotes.

You could also try using parameters rather than building the full sql and insert the data with .ParamByName

A general hint for solving these problems is to direct the sql you've built into a file qryVarious.SQL.SaveToFile(..) and run the result in DBSys. Its much easier to mess around in there to find the problem.

Roy Lambert [Team Elevate]

Thanks Roy,

That's all good advice, thanks.

I'll fire up DBSys & see how it goes.

Cheers, Mark

Roy Lambert wrote:

Mark


Your formatting looks right to me. I'm well out of practice with DBISAM but I think Date is a reserved word and should be wrapped in double quotes.

You could also try using parameters rather than building the full sql and insert the data with .ParamByName

A general hint for solving these problems is to direct the sql you've built into a file qryVarious.SQL.SaveToFile(..) and run the result in DBSys. Its much easier to mess around in there to find the problem.

Roy Lambert [Team Elevate]

Mark,

In one of my dbisam apps i'm using something as follows (this uses timestamp):

 DELETE FROM LOG L WHERE (L.TS < CAST (%s AS TIMESTAMP));

This is  executed in query and timestamp field is formatted using  FormatDateTime('yyyy"-"mm"-"dd" "hh:mm:ss.zzz', DateTime)

You might be able to use for your sql.

Raul

Mark,

Any time that you are building an SQL statement at runtime and encounter an
error, it pays to use the debugger to inspect the actual SQL statement being
executed in the Evaluate dialog, or to use the TDBISAMQuery.Text property to
display the value using ShowMessage():

http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=d&version=7&comp=TDBISAMQuery&prop=Text

--
Tim Young
Elevate Software
www.elevatesoft.com

> FormatDateTime

Simple: Don't use that. Use Engine.QuotedSQLStr(Engine.DateTimeToAnsiStr(
instead.

That takes care of making sure that things are proper for you.

/Matthew Jones/