Need some advice from some SQL people!

Is the SQL built into Delphi 2.0, including the local Interbase server,
really
unstable, or have I done something wrong?

I've been using Delphi since last May and have even taught a couple of
courses
in Delphi Windows programming, but until last week I hadn't tried to use
the
SQL capabilities of Delphi.  I'm doing a review of several database
products for
my employer, and I'm using Delphi 2.0 for the test platform, as it will
be the
application development platform used on the project.  As part of the
evaluation
tests I'm loading both 90,000 and 150,000 records into tables from an
ASCII fixed
field files.  After loading the records I need to run a couple of simple
SQL
queries against them.  BTW, I'm running all these tests on a 100MHz
Pentium with
32Mb RAM, 450 MB free disk space, and Windows 95.

Problem 1: Terrible Local Interbase performance.

As a first try I tried to load 8,000 records into a Local Interbase
table by
accessing it through a TTable component.  It took like 40 min!!!!!  
Loading all
90,0000 into a Paradox table only takes 4 min.  

Next I translated the 8,000 records from ASCII fixed field format to an
WISQL script
of SQL "INSERT" statements, added a "CONNECT" statement at the top and a
"COMMIT"
statement at the bottom, and ran it in WISQL.  It only took like 50
secs.

Finally I tried to load the WISQL script, sans the "CONNECT" and
"COMMIT" statements,
by executing Query1.SQL.LoadFromFile and Query1.Open.  When I did the
thing ran for
quite a while, 20 min, then raised an exception saying "Error Creating
Cursor Handle".

So my question is, how does one mass load an Interbase table?

Problem 2: Sometimes SQL works and sometimes it doesn't.

Ok, so I've got these 90,000 and 150,000 record tables and I need to run
the following
SQL query against them (I'm now talking about Paradox, as I can't even
load the tables
into Interbase!):

SELECT TABLE1.NUM,TABLE1.NAME
FROM   TABLE1,TABLE2
WHERE  (TABLE1.NUM  = TABLE2.NUM)
  AND  (TABLE1.NAME = 'Icabod    Crane')
  AND  (TABLE1.BIGNUM BETWEEN 1000000 AND 8000000)
  AND  (TABLE1.NUM  < 50000)
  AND  (TABLE2.BIGALPHA = 'Honolulu')

The query runs in under 1 min. and gives the correct results.

Great!  Now I need to run an even simpler query:

UPDATE TABLE1 SET BIGNUM = BIGNUM - 1

And again the thing grinds away for several mins. and gets an exception
saying
"Error Creating Cursor Handle".

My question is, are the SQL capabilities of Delphi 2.0, or 1.0 for that
matter,
production quality?  Am I doing something wrong here?  

Any help would be greatly valued, especially from Steve T. or another
Borland
person.

TTFN Paul

It sounds like one of your problems is that you are using the Query.Open
method for SQL commands that do not return a cursor.  Use the
Query.ExecSQL method for INSERT, UPDATE, DELETE type of commands and Open
for SELECT statements.  Check the online Delphi help file for the method
'EXECSQL' for additional info on this.  Hope this helps.

Gerald Nunn
Ottawa, Canada

In article <3179A619.7...@srv.net>, p...@srv.net says...

Paul,

In article <3179A619.7...@srv.net>, Paul Whittington <p...@srv.net>
says:

You can only use local-SQL statements against Paradox DB and dBASE DBF
tables only. That means that things like nested SELECTs and JOINs may
not be supported. Check out on-line help.

Also, Delphi is smart enough to convert (where appropriate) your SQL
statments via BDE to the SQL dialect understood by the backend database
system. That would mean that you have to use BDE supported SQL (which
is ANSI-92 if I am correct) in order to exploit the full functionality.

IMHO, Delphi's SQL support is OK and 2.0 improved on several areas
lacking in 1.0. At least better than VDB 5.5 (even compared with Delphi
1.0 !).

Regards.
CL
4.22.96 0150 hr sg.
.
PS: Respond to forum and cc: to email greately appreciated!