Board index » delphi » "Where" clause and live queries

"Where" clause and live queries

It is my impression that a "where" clause is an acceptable part of the
syntax for a query which can return a live result.  From Delphi 3 help...
"The BDE returns a live result set only if the SELECT syntax of the query
conforms to the syntax requirements for a live result set."

Unfortunantly, this has not been my experience with SQL Server 6.5.  For
simplicity's sake, I've set up a test program entirely within
the form designer; no actual code is involved.  On the form,
I have a TTable, TDataSource, and TQuery component, all set
up properly to access the table I want through a BDE alias that
accesses the remote SQL Server database.  If the TQuery.SQL property is

  select * from master

then it always works, whether the query is live or not.  However,
if the TQuery.SQL property is

  select * from master where MasterID=1

then the query only works if RequestLive=FALSE.  If RequestLive=TRUE,
I get an EDBEngineError "Could not find object" (error code 8712)
when I try to set the query's Active property to TRUE in the Object
Inspector.

I have never seen this error in any other circumstance.  Live queries
with a "where" clause work fine with Paradox and Interbase, and live
queries without a "where" clause work fine in SQL Server.  What am I
missing?

System specs:
Delphi 3.02 running on Windows 98 (same problem under Windows 95)
Microsoft SQL Server 6.5 SP3 on Windows NT Server 4.0 SP3
BDE 5.0
native MSSQL driver (same problem with ODBC SQL Server driver)

Thanks in advance.

-Mark
Industrial Images

 

Re:"Where" clause and live queries


Quote
Mark Pilgrim <*NO*SPAM*...@sprintmail.com> wrote in message

<6s4i11$oe...@forums.borland.com>...

Quote
>It is my impression that a "where" clause is an acceptable part of the
>syntax for a query which can return a live result.  From Delphi 3 help...
>"The BDE returns a live result set only if the SELECT syntax of the query
>conforms to the syntax requirements for a live result set."

When you're dealing with remote servers, whether or not the BDE rules are
met, the SQL server is the one that makes the final decision on whether or
not the result set can be edited or not.

Quote
>However, if the TQuery.SQL property is

>  select * from master where MasterID=1

>then the query only works if RequestLive=FALSE.  If RequestLive=TRUE,
>I get an EDBEngineError "Could not find object" (error code 8712)
>when I try to set the query's Active property to TRUE in the Object
>Inspector.

This is correct behavior and is documented in the Delphi help files.
Attempting to request a live result set and getting a non-live one with
Paradox or dBase through the BDE returns a non-live result set. Doing the
same thing with a remote server results in an error.

Quote
>I have never seen this error in any other circumstance.  Live queries
>with a "where" clause work fine with Paradox and Interbase, and live
>queries without a "where" clause work fine in SQL Server.  What am I
>missing?

Each server has its own rules. As the help file states, you have to
reference your server's documentation to answer this question.

Juan

Re:"Where" clause and live queries


Quote
Juan Jimenez (TeamB) wrote in message <6s4rea$p...@forums.borland.com>...
>> However, if the TQuery.SQL property is

>> select * from master where MasterID=1

>>then the query only works if RequestLive=FALSE.  If RequestLive=TRUE,
>>I get an EDBEngineError "Could not find object" (error code 8712)
>>when I try to set the query's Active property to TRUE in the Object
>>Inspector.

>This is correct behavior and is documented in the Delphi help files.

Yes, I have read the help files and expect to receive an error when I ask
for a live query that SQL Server does not return.  It just seemed to me that
live queries would be relativley useless if one could not use a "where"
clause.  I was wondering if I am missing something re: SQL Server and live
Queries.  From other postings on the newsgroup I gather that they are to be
avoided alltogether, and so I will attempt to do so.

Thanks,

Mark
Industrial Images, Inc.

Re:"Where" clause and live queries


<shrug> All I can say is "talk to Microsoft". Not that it will do any good
unless you have contacts in national TV, but you can still try...

Quote
Jon Claney <*NO*SPAM*...@sprintmail.com> wrote in message

<6s7abh$s...@forums.borland.com>...
Quote

>Yes, I have read the help files and expect to receive an error when I ask
>for a live query that SQL Server does not return.  It just seemed to me
that
>live queries would be relativley useless if one could not use a "where"
>clause.  I was wondering if I am missing something re: SQL Server and live
>Queries.  From other postings on the newsgroup I gather that they are to be
>avoided alltogether, and so I will attempt to do so.

>Thanks,

>Mark
>Industrial Images, Inc.

Re:"Where" clause and live queries


Mark,

Is your ID column indexed? It needs to be to return a live result.

Regards,
Richard

Other Threads