Board index » delphi » Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME

Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME

Im really stuck with these problems and they cost me very much time. So
please help me. Perhaps its my fault.

Ive got a very simple table on MS SQL Server 6.5

CREATE TABLE dbo.Profile (
        Profil int NOT NULL ,
        Name1 varchar (40) NOT NULL ,
        Name2 varchar (40) NULL ,
        Sprache char (2) NOT NULL DEFAULT ('D'),
        Anlage_wann datetime NOT NULL DEFAULT (getdate()),
        Anlage_von char (10) NOT NULL DEFAULT (suser_name(null)),
        Update_wann datetime NULL ,
        Update_von char (10) NULL ,
        CONSTRAINT Profile_PK_Profil PRIMARY KEY  CLUSTERED
        (
                Profil
        ),
        CONSTRAINT Profile_UC_Profil UNIQUE  NONCLUSTERED
        (
                Profil
        )
)

1) Unique Index problem
-------------------------------------

Query1 (RequestLive=true, UpdateMode=upWhereKeyOnly) contains the following
SQL statement (as a very simple example):

SELECT * from Profile

I linked a TDBGrid and a TNavigator to it (through a standard TDataSource)
and it shows the table contents. So far it works fine. But when I click on
the Refresh button of the TNavigator or issue a Query1.Refresh I get a
message, that the table is not uniquely indexed. As you can see, thats not
true.
To complete the mess, when I update a records field NAME1 you can see in
SQL Monitor:

SQL Prepare: MSSQL - UPDATE Profile SET Name1=:1 WHERE Profil=:2
SQL Data In: MSSQL - Param = 1, Name = Name1, Type = fldZSTRING, Precision
= 40, Scale = 0, Data = Newname1
SQL Data In: MSSQL - Param = 2, Name = Profil, Type = fldINT32, Precision =
0, Scale = 0, Data = 7
SQL Execute: MSSQL - UPDATE Profile SET Name1=:1 WHERE Profil=:2

As you can see, the BDE does know about the unique key. I cant find any
solution. I also tried using dbo.Profile and a user with alias dbo.
This problem exist with any other table on SQL Server through SQL Links,
through ODBC, with Local Interbase and so on. When Im doing the same in
Delphi 1 through ODBC it works exactly as it should.
When I use a TTable instead of a TQuery Refresh works fine.

2) Grid update problem
----------------------------------

When in the same Query1 I set SQL to

SELECT * from Profile order by Name1

and I change a value in column Name1 the Grid is not updated to reflect the
correct order. Please dont tell me to do a Query1.Refresh (see problem 1).

3) Local caching
-------------------------

Why does the BDE load all the data to the Local Cache and doesnt use
server based cursors (I already set SQLQUERYMODE to SERVER in the alias)?

4) SQL Explorer - strange behavior
---------------------------------------------------

When I create a rule or standard in SQL Enterprise Manager and assign it to
a column, SQL Explorer doesnt find them (simply nothing) and shows a
message, when I wanna show the properties of the column. SQL Explorer even
cant find rules and standards created by itself.

IM GETTING MAD ON THIS !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
IS IT A BDE FAILURE? I THINK SO.

My software is Delphi 3.0 C/S german, MS SQL Server 6.5 german (tried
without SP and up to SP3 - same problems)

Every help or hint would be a pleasure to me.
Please save my soul and family !

 

Re:Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME


Carsten;

I don't think the BDE supports the Refresh method for TQuery objects.
You have to call the Close and Open methods to refresh the query.

John

Quote
Carsten Mauer wrote:

> Im really stuck with these problems and they cost me very much time. So
> please help me. Perhaps its my fault.

> Ive got a very simple table on MS SQL Server 6.5

> CREATE TABLE dbo.Profile (
>         Profil int NOT NULL ,
>         Name1 varchar (40) NOT NULL ,
>         Name2 varchar (40) NULL ,
>         Sprache char (2) NOT NULL DEFAULT ('D'),
>         Anlage_wann datetime NOT NULL DEFAULT (getdate()),
>         Anlage_von char (10) NOT NULL DEFAULT (suser_name(null)),
>         Update_wann datetime NULL ,
>         Update_von char (10) NULL ,
>         CONSTRAINT Profile_PK_Profil PRIMARY KEY  CLUSTERED
>         (
>                 Profil
>         ),
>         CONSTRAINT Profile_UC_Profil UNIQUE  NONCLUSTERED
>         (
>                 Profil
>         )
> )

> 1) Unique Index problem
> -------------------------------------

> Query1 (RequestLive=true, UpdateMode=upWhereKeyOnly) contains the following
> SQL statement (as a very simple example):

> SELECT * from Profile

> I linked a TDBGrid and a TNavigator to it (through a standard TDataSource)
> and it shows the table contents. So far it works fine. But when I click on
> the Refresh button of the TNavigator or issue a Query1.Refresh I get a
> message, that the table is not uniquely indexed. As you can see, thats not
> true.
> To complete the mess, when I update a records field NAME1 you can see in
> SQL Monitor:

> SQL Prepare: MSSQL - UPDATE Profile SET Name1=:1 WHERE Profil=:2
> SQL Data In: MSSQL - Param = 1, Name = Name1, Type = fldZSTRING, Precision
> = 40, Scale = 0, Data = Newname1
> SQL Data In: MSSQL - Param = 2, Name = Profil, Type = fldINT32, Precision =
> 0, Scale = 0, Data = 7
> SQL Execute: MSSQL - UPDATE Profile SET Name1=:1 WHERE Profil=:2

> As you can see, the BDE does know about the unique key. I cant find any
> solution. I also tried using dbo.Profile and a user with alias dbo.
> This problem exist with any other table on SQL Server through SQL Links,
> through ODBC, with Local Interbase and so on. When Im doing the same in
> Delphi 1 through ODBC it works exactly as it should.
> When I use a TTable instead of a TQuery Refresh works fine.

> 2) Grid update problem
> ----------------------------------

> When in the same Query1 I set SQL to

> SELECT * from Profile order by Name1

> and I change a value in column Name1 the Grid is not updated to reflect the
> correct order. Please dont tell me to do a Query1.Refresh (see problem 1).

> 3) Local caching
> -------------------------

> Why does the BDE load all the data to the Local Cache and doesnt use
> server based cursors (I already set SQLQUERYMODE to SERVER in the alias)?

> 4) SQL Explorer - strange behavior
> ---------------------------------------------------

> When I create a rule or standard in SQL Enterprise Manager and assign it to
> a column, SQL Explorer doesnt find them (simply nothing) and shows a
> message, when I wanna show the properties of the column. SQL Explorer even
> cant find rules and standards created by itself.

> IM GETTING MAD ON THIS !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> IS IT A BDE FAILURE? I THINK SO.

> My software is Delphi 3.0 C/S german, MS SQL Server 6.5 german (tried
> without SP and up to SP3 - same problems)

> Every help or hint would be a pleasure to me.
> Please save my soul and family !

--
John

jnol...@firstam.com
(714) 643-8328 voice
(714) 643-0364 fax

Re:Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME


Carsten Mauer schrieb in Nachricht <01bcfbf1$a086a500$4083a8c0@mauer1>...

Quote
>4) SQL Explorer - strange behavior
>---------------------------------------------------

>When I create a rule or standard in SQL Enterprise Manager and assign it to
>a column, SQL Explorer doesnt find them (simply nothing) and shows a
>message, when I wanna show the properties of the column. SQL Explorer even
>cant find rules and standards created by itself.

Found cause of problem no 4) - seems to be a real failure !!!

Inspecting the statements used by SQL Explorer it does the following (stil
in 3.02):

SELECT
A.crdate
, A.sysstat
, B.name
, A.name
, A.id
, A.uid
, A.type
, A.userstat
, A.indexdel
, A.schema_ver
, A.refdate
, A.version
, A.deltrig
, A.instrig
, A.updtrig
, A.seltrig
, A.category
, A.cache
FROM
dbo.sysobjects A
, dbo.sysusers B
WHERE (user_name(A.uid) <> 'dbo')                             <---------
fault !!!
AND (A.type = 'R')
AND (  (A.uid = B.uid))

above line should read (I think)

WHERE (user_name(A.uid) = 'dbo')

Does anyone else work with MS SQL Links? Is there a workaround?

Re:Refresh problem using TQuery with SQL Server - SOS PLEASE HELP ME


Carsten

You cannot use the Refresh method with a TQuery.  You must close and
then reopen the query (or use a TTable where Refresh does work).

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Other Threads