D4pro BDE supported SQL statements

[P E Schoen]

I have been experimenting with SQL statements in the database portion of my
www.ortmaster.com application, and I have found some expressions that seem
to be unsupported. I'm not a Query expert, but I tried some fairly common
statements and got errors, so I'd like to know what is or is not allowed.

Some things that work are as follows:

SELECT * FROM ORTRES WHERE
TESTDATE > "8/18/2010" AND
RECLID < "Test2" AND
NOT TESTRESULT LIKE "*%"
ORDER BY TESTDATE DESC

SELECT * FROM ORTRES WHERE
TESTDATE BETWEEN "8/18/2010"
AND "12/3/2010"

SELECT * FROM ORTRES WHERE
TESTTIME > "18:23"

And these do not:

SELECT * FROM ORTRES
WHERE TESTDATE < NOW()

WHERE TESTDATE < CURDATE()

WHERE TESTDATE < TODAY()

I have not been able to find specific information on the BDE as to what is
supported. Some other resources I found are:
http://www.w3schools.com/sql/default.asp
http://delphi.about.com/od/database/l/aa060199.htm

(this series may be a good
tutorial for me to recommend to customers)

There's not much on the usenet database newsgroups, and especially the
Borland groups seem dead.

Thanks,

Paul

[a***@aol.com]

I have been experimenting with SQL statements in the database portion of mywww.ortmaster.comapplication, and I have found some expressions that seem
to be unsupported. I'm not a Query expert, but I tried some fairly common
statements and got errors, so I'd like to know what is or is not allowed.
SELECT *  FROM ORTRES WHERE
TESTDATE > "8/18/2010" AND
RECLID < "Test2"  AND
NOT TESTRESULT LIKE "*%"
ORDER BY TESTDATE DESC
SELECT *  FROM ORTRES WHERE
TESTDATE BETWEEN "8/18/2010"
AND "12/3/2010"
SELECT *  FROM ORTRES WHERE
TESTTIME > "18:23"
SELECT *  FROM ORTRES
WHERE TESTDATE < NOW()
WHERE TESTDATE < CURDATE()
WHERE TESTDATE < TODAY()
I have not been able to find specific information on the BDE as to what is
supported. Some other resources I found http://youtu.be/hWU2pvj_xIc series may be a good
tutorial for me to recommend to http://youtu.be/eSDeIzEEjAY
There's not much on the usenet database newsgroups, and especially the
Borland groups seem dead.

To my mind the issue is that SQL statements are executed by an SQL
server of some sort. That server will execute only functions which it
supports within that statement (? built-in functions ?). The server
you use does not have built-in functions of Now(), Today() and
CurDate().

AIUI you could use parameters (names prefixed with a colon in Delphi)
in an SQL statement. Your local SQL client replaces parameters with
actual values before calling the SQL server with the statement. Your
code must provide a SQL language compatible value to the parameter
before calling the SQL statement.

Alan Lloyd

[a***@aol.com]

Paul

Having looked a bit further, what I called "built-in functions" should
have been named "stored procedures".

I think you could usefully look at TQuery.Params, TParams & TParam,
and their examples, in Delphi Help.

Alan Lloyd

[Maarten Wiltink]

Post by a***@aol.com
Having looked a bit further, what I called "built-in functions" should
have been named "stored procedures".

Probably not. Stored procedures are things you as the database user write;
built-in functions are already there.

Groetjes,
Maarten Wiltink

[P E Schoen]

Post by Maarten Wiltink

Post by a***@aol.com
Having looked a bit further, what I called "built-in functions"
should have been named "stored procedures".

Probably not. Stored procedures are things you as the database
user write; built-in functions are already there.

I thought I could do this using parameters, but I ran into problems. So I
used this:

procedure TfmOrtDatabase.btEditSQLselClick(Sender: TObject);
var i: Integer;
pos: PChar;
S, Today: String;
begin
if btEditSQLsel.Caption = 'Edit SQL' then begin
Memo1.Show;
Memo1.Lines := fmReclData.qRes.SQL;
btEditSQLsel.Caption := 'Finish';
end
else begin // Finished edit
fmReclData.qRes.Close;
for i := 0 to Memo1.Lines.Count-1 do begin
S := Memo1.Lines[i];
pos := StrPos( pchar(UpperCase(S)), pchar('TODAY') );
if ( pos <> nil ) then begin
Today := DateToStr(Date);
Memo1.Lines[i] :=StringReplace(S, 'Today', '"'+Today+'"',
[rfIgnoreCase]);
end;
end;
fmReclData.qRes.SQL := Memo1.Lines;
Memo1.Hide;
btEditSQLsel.Caption := 'Edit SQL';
fmReclData.qRes.Open;
tsResultsEnter(self);
end
end;

Possibly not the best, but it works. And it even works if the user enters:

SELECT * FROM ORTRES
where TESTDATE < Today - 200

Maybe I should use the more standard Today() or NOW()

It took me awhile to get this working. I've been using Perl and PHP lately,
and I had to review some Delphi syntax. If you want to see this application
it is available on my website www.ortmaster.com.

Paul

[a***@aol.com]

Post by P E Schoen
I thought I could do this using parameters, but I ran into problems. So I

I think parameters would have something like (appropriately wrapped in
user interface) :

var
qResParam : TParam;

// once on initialisation
qResParam := TParam(fmReclData.qRes.Params.Add);
pResParam.AsString := DateToStr(Date - 200);
qResParam.Name := 'Date';

then use :Date in your query

Maybe better still show a TDateTimePicker set to Date-200 and use the
returned date

Alan Lloyd