Problem with SDAC 4 prof. and RefreshQuick

Sergio Bertolotti · Post by **Sergio Bertolotti** » Mon 26 Mar 2007 14:29

Dear Support,
yesterday I update to new version of SDAC 4.x professional for Delphi 7 professional.
I use Microsoft SQL Server 2005 Express edition with service pack 2 in italian language.
I see a new function RefreshQuick.
How can I use correctly this new method ?
If I understand correctly, in my table, I must define a unique key and a timestamp field to use it.

This is metadata of my table :

CREATE TABLE [dbo].[RAPPORTINI] (
[CONT] int IDENTITY(1, 1) NOT NULL,
[CODCLI] varchar(8) COLLATE Latin1_General_CI_AS NOT NULL,
[CODUTE] varchar(6) COLLATE Latin1_General_CI_AS NOT NULL,
[DATA] datetime NULL,
[NUMORE] numeric(9, 2) NULL,
[NOTE] text COLLATE Latin1_General_CI_AS NULL,
[IMG_SCANNER] image NULL,
[SPEDITO] bit CONSTRAINT [DF__RAPPORTIN__SPEDI__1CF15040] DEFAULT (0) NOT NULL,
[FATTURATO] bit NOT NULL,
[SYNCFIELD] timestamp NULL,
[UTENTE] char(30) COLLATE Latin1_General_CI_AS CONSTRAINT [DF__RAPPORTIN__UTENT__1DE57479] DEFAULT user_name() NOT NULL,
[ULTMOD] datetime CONSTRAINT [DF__RAPPORTIN__ULTMO__1ED998B2] DEFAULT getdate() NOT NULL,
CONSTRAINT [PK_RAPPORTINI] PRIMARY KEY CLUSTERED ([CONT]),
CONSTRAINT [FK_RAPPORTINI] FOREIGN KEY ([CODCLI])
REFERENCES [dbo].[ANAGRAFICA_CLIENTI] ([CODCLI])
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

I do that , but when I call in my TMSQuery this method after insert a new record and execute a post ,I obtain an error message.
This table is a detail table of another table - ANAGRAFICA_CLIENTI - and there is a foreign key between field CODCLI of table RAPPORTINI and CODCLI of table ANAGRAFICA_CLIENTI.

I copy SQL statement that I use in my TMSQuery :

SQL Select :
select * FROM
RAPPORTINI
WHERE CODCLI=:CODCLI
ORDER BY DATA

SQL Insert :
INSERT INTO RAPPORTINI
(CODCLI, CODUTE, DATA, NUMORE, NOTE, IMG_SCANNER, SPEDITO, FATTURATO)
VALUES
(:CODCLI, :CODUTE, :DATA, :NUMORE, :NOTE, :IMG_SCANNER, :SPEDITO, :FATTURATO)

SQL Update :
UPDATE RAPPORTINI
SET
CODCLI = :CODCLI, CODUTE = :CODUTE, DATA = :DATA, NUMORE = :NUMORE, NOTE = :NOTE,
IMG_SCANNER = :IMG_SCANNER, SPEDITO = :SPEDITO, FATTURATO = :FATTURATO,
UTENTE = user_name(), ULTMOD = getdate()
WHERE
CONT = :Old_CONT

SQL Delete :
DELETE FROM RAPPORTINI
WHERE
CONT = :Old_CONT

SQL Refresh :
SELECT
RAPPORTINI.CODCLI, RAPPORTINI.CODUTE, RAPPORTINI.DATA, RAPPORTINI.NUMORE,
RAPPORTINI.NOTE, RAPPORTINI.IMG_SCANNER, RAPPORTINI.SPEDITO, RAPPORTINI.FATTURATO,
RAPPORTINI.SYNCFIELD
FROM
RAPPORTINI
WHERE
CONT = :CONT

If I create a new DB with a table and with an unique field and a timestamp field I haven't any type of problems.

Can you tell me more about this new interesting method ?

Best regards.
Sergio B.

Jackson · Post by **Jackson** » Tue 27 Mar 2007 09:30

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column. This allows SDAC to quickly retrieve to the client side changes applied to the server by other clients. SDAC generates refresh statement that retrieves only rows with value of timestamp field grater than maximum cached timestamp field value. The RefreshQuick method works in such way. For more information please see MSDN and SDAC help.
Please specify the exact error message that you get.

Sergio Bertolotti · Post by **Sergio Bertolotti** » Tue 27 Mar 2007 11:49

Dear EvgeniyM,

I obtain two error message :

First at disegn time. If I open my MSQuery SQL editor and change for example Refresh SQL statement I obtain :

Invalid class typecast.

Second at runtime. If I insert a new record in my table rapportini, I obtain this error message :

Sintassi non corretta in prossimità di ':' .

I translate it for you : Syntax not correctly in proximity of ':'.

I control my parameter but all is OK.

I use Borland Delphi 7 version professional ver. 7.0 Build 8.1 .My project was originally created with SDAC 3.X.
I install this new version of SDAC 4.x and recompile all project.
After this I test this new feature in my rapportini table, and at this point I obtain this problem.
As I precedently tell you, if I create a new project with a table with unique/primary key and timestamp field all is OK.
If I use Refresh method instead of RefreshQuick all is OK at runtime but at design time the first error still remain.

Best regards.
Sergio B.

Jackson · Post by **Jackson** » Wed 28 Mar 2007 10:51

We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem; include definition of your own database objects; don't use third party components.

Also supply us following information
- Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor

Sergio Bertolotti · Post by **Sergio Bertolotti** » Thu 29 Mar 2007 12:23

Dear EvgeniyM,

I send a small test project to your e-mail address : evgeniym*crlab*com.
I stay tune for news on my problem.

Best regards.

Sergio Bertolotti

Jackson · Post by **Jackson** » Fri 30 Mar 2007 11:31

Thank you for your sample. It did help us very much.
We have reproduced the problem and fixed it.
This fix will be included in the next build of SDAC 4.
Please watch for announcements at the forum.