Board index » delphi » General SQL Error, MSSQL 6.5, SQL Links 4.51

General SQL Error, MSSQL 6.5, SQL Links 4.51

We have a form that opens a bunch of read-only TTables connected to a MS
SQL 6.5 database, BDE 4.51 and the SQL Links driver. We mix TTable and
TQuery in the application--the TTables being used solely to support
ListBoxes. We have about 25 databse tables open in the application when
we see the problem. The code is something like:

try
  mytable.open;
except
  ShowMessage('Error');
end;

We never get the try exception fired, but when we run in the IDE we will
get a General SQL Error with the open. All of these TTables are tied to
the drop down lists of TDBComboListBoxes, and on the tables that fail,
the boxes won't drop down--even though the TTable's active property is
TRUE. SQL Monitor shows an "Unmapped SQL Error 0" returned on the Open.
The MSSQL monitor shows no error.

To add to the confusion, one can do some other things with the
application and then return to this form and find that the drop-downs
are working fine.

We are at a loss to figure out what is going on.

--
Kevin Davidson
QS Technologies, Inc.
Suite 1106 NationsBank Plaza, Greenville, SC 29601
ke...@qsinc.com
voice: (864) 232-2666 / fax: (864) 370-2230

 

Re:General SQL Error, MSSQL 6.5, SQL Links 4.51


Quote
>We have a form that opens a bunch of read-only TTables connected to a MS
>SQL 6.5 database, BDE 4.51 and the SQL Links driver. We mix TTable and
>TQuery in the application--the TTables being used solely to support
>ListBoxes. We have about 25 databse tables open in the application when
>we see the problem. The code is something like:

>try
>  mytable.open;
>except
>  ShowMessage('Error');
>end;

Get rid of SQL Links - I had some mysterious problems with them myself
(about a year ago), so to the hell with it! - now I use ODBC drivers and
everything's OK. ;=)

  Kind regards,

     Konstantin V {*word*224}ov aka JHVHS
                                        jh...@quadrus.ru

  Quadrus-M   http://www.quadrus.ru

Re:General SQL Error, MSSQL 6.5, SQL Links 4.51


Ah, well when we tried the ODBC driver, we started getting these "OPERATION
NOT APPLICABLE" errors all over the place.

Quote
"Konstantin V. {*word*224}OV" wrote:
> >We have a form that opens a bunch of read-only TTables connected to a MS
> >SQL 6.5 database, BDE 4.51 and the SQL Links driver. We mix TTable and
> >TQuery in the application--the TTables being used solely to support
> >ListBoxes. We have about 25 databse tables open in the application when
> >we see the problem. The code is something like:

> >try
> >  mytable.open;
> >except
> >  ShowMessage('Error');
> >end;

> Get rid of SQL Links - I had some mysterious problems with them myself
> (about a year ago), so to the hell with it! - now I use ODBC drivers and
> everything's OK. ;=)

>   Kind regards,

>      Konstantin V {*word*224}ov aka JHVHS
>                                         jh...@quadrus.ru

>   Quadrus-M   http://www.quadrus.ru

--
Kevin Davidson
QS Technologies, Inc.
Suite 1106 NationsBank Plaza, Greenville, SC 29601
ke...@qsinc.com
voice: (864) 232-2666 / fax: (864) 370-2230

Re:General SQL Error, MSSQL 6.5, SQL Links 4.51


Also when we tried to use MS SQL with the ODBC driver, we got pathologically
slow execution times opening small
TTables (10 rows, 2 columns). Under SQL Links our ap starts in 10
seconds--under ODBC it took 6 minutes. We also ran into errors like "Connection
Busy In Use by other hstmt".

Quote
Kevin Davidson wrote:
> Ah, well when we tried the ODBC driver, we started getting these "OPERATION
> NOT APPLICABLE" errors all over the place.

--
Kevin Davidson
QS Technologies, Inc.
Suite 1106 NationsBank Plaza, Greenville, SC 29601
ke...@qsinc.com
voice: (864) 232-2666 / fax: (864) 370-2230

Re:General SQL Error, MSSQL 6.5, SQL Links 4.51


Kevin,

  I'm wondering why you mixed ttables and tqueries together in the first
place.
Do you feel you get better response time by using them for listboxes? The
obvious
is to not use them but you seem to want to keep them. I was curious why.

Craig

(I'm actually in Spartanburg, SC... Go figure)

Quote
Kevin Davidson <ke...@qsinc.com> wrote in message

news:370A6491.C7FEC1D5@qsinc.com...
Quote
> Also when we tried to use MS SQL with the ODBC driver, we got
pathologically
> slow execution times opening small
> TTables (10 rows, 2 columns). Under SQL Links our ap starts in 10
> seconds--under ODBC it took 6 minutes. We also ran into errors like
"Connection
> Busy In Use by other hstmt".

> Kevin Davidson wrote:

> > Ah, well when we tried the ODBC driver, we started getting these
"OPERATION
> > NOT APPLICABLE" errors all over the place.

> --
> Kevin Davidson
> QS Technologies, Inc.
> Suite 1106 NationsBank Plaza, Greenville, SC 29601
> ke...@qsinc.com
> voice: (864) 232-2666 / fax: (864) 370-2230

Re:General SQL Error, MSSQL 6.5, SQL Links 4.51


Craig, wanna job? Call me.

Originally we started out with all TQuerys. Our AP has literally hundreds of
database tables. We were trying to deploy against both Paradox and SQL
databases. We found that there is a limit (undocumented) on the number of
Paradox TQuerys that can be open at the same time; it's a memory constraint
tied to the number of queries, the number of fields and the aggregate size of
the SQL statements. So what seemed a good workaround was to use TTables for
all the drop down boxes (TTables don't count against the limit) and TQueries
for all the interesting stuff.

We found that with SQL Server, that TTables work better as sources for drop
down boxes than TQuerys because TTable manage the transfer of data from the
server on an as-needed basis, where TQuerys transfer the whole result set
every time. TTables have the disadvantage that all columns are retrieved
whether they are used or not, but the tables we use for dropdown boxes have
few columns to begin with.

I must say that database programming with Delphi is hugely frustrating
because of things that work strangely, fail occasionally, break when you
scale up and are inconsistent from database to database. I'm hoping that
native DAO will help things out in the future.

Oh, we got rid of our General SQL error by moving the opens to another place
in the code. I don't have a clue as to why this worked.

Quote
Craig Baugh wrote:
> Kevin,

>   I'm wondering why you mixed ttables and tqueries together in the first
> place.
> Do you feel you get better response time by using them for listboxes? The
> obvious
> is to not use them but you seem to want to keep them. I was curious why.

> Craig

> (I'm actually in Spartanburg, SC... Go figure)

> Kevin Davidson <ke...@qsinc.com> wrote in message
> news:370A6491.C7FEC1D5@qsinc.com...
> > Also when we tried to use MS SQL with the ODBC driver, we got
> pathologically
> > slow execution times opening small
> > TTables (10 rows, 2 columns). Under SQL Links our ap starts in 10
> > seconds--under ODBC it took 6 minutes. We also ran into errors like
> "Connection
> > Busy In Use by other hstmt".

> > Kevin Davidson wrote:

> > > Ah, well when we tried the ODBC driver, we started getting these
> "OPERATION
> > > NOT APPLICABLE" errors all over the place.

> > --
> > Kevin Davidson
> > QS Technologies, Inc.
> > Suite 1106 NationsBank Plaza, Greenville, SC 29601
> > ke...@qsinc.com
> > voice: (864) 232-2666 / fax: (864) 370-2230

--
Kevin Davidson
QS Technologies, Inc.
Suite 1106 NationsBank Plaza, Greenville, SC 29601
ke...@qsinc.com
voice: (864) 232-2666 / fax: (864) 370-2230

Other Threads