DATETIME in mssql issue again (Clarion 10)

questions
21

Hi Guennadi,

Clarion normally does what is called optimistic concurrency checking. The where resourceid = 314 is necessary to actually identify the record. And the example I posted above looks like yours. It comes from hand code like:
get(buscase,BUS:PK_BUSPASS)
BUS:modified_date_date = today()
BUS:modified_date_time = clock()
put(buscase)

But the templates when they do a .Fetch incorporate a call to WATCH, so the .Update looks like:

update buscase set modified_date = <:new_value> where id = :id and modified_date = :old_mod_date_value and thiscolumn is NULL and thatcolumn = :oldthatcolumn and ā€¦

where the :Id and :old_mod_date_value and :oldthatcolumn are the values that were stored by the WATCH when the record was grabbed. If it doesnā€™t manage to update the record (0 rows were updated), it will give you the ā€œRecord Changed by another stationā€ error. That can sometimes really be caused by someone else having overwritten the record , but in the Clarion world the more likely cause is that the WATCH stored something that doesnā€™t totally match what is in the database: it truncated the fractional seconds, or only stored the first 20 characters of a 30 character string, etc.

22

Are you seeing a SysDateTime?

Or do you have a cast time(7) in the MS SQL server or DATETIME2(4)?

SELECT CAST('2024-05-08 12:35:29.1234567 +12:15' AS TIME(7)) AS 'time';
DECLARE @datetime2 DATETIME2(4) = '1968-10-23 12:45:37.1237';

Or a Time(7) declared for that table on the MS SQL server?

It might not be a Clarion dct problem.

You could try creating a new temp dct, and import the MS SQL table thats playing up into the dct and see what definition you get back.

23

I still strongly suggest you check the installed ODBC 32 drivers. You need to have a SQL Native Client driver installed and not just use the build-in SQL Driver.
Iā€™ve seen this exact behavior before. An yes, I had other tables and even other columns in the same table that did not get the additional precision digits for the time portion of the inserted value.

1 Like
24

just found that the Clarion driver somehow generates this when UPDATE:

declare @p1 int
set @p1=430
exec sp_prepexec @p1 output,Nā€™@P1 varchar(60),@P2 datetime2,@P3 decimal(9,0)ā€¦etc

so it uses datetime2 for the column defined just as datetime

as result, it generated the wrong updateā€¦ not sure what else I can checkā€¦

25

Can you re-import the file into your dct, with a new name for the file? Now compare the old definition of the file with the new one.

Wizard an app using only the old and new files and see if there is any difference.

26

Geoff,

I already tried it, and the fileā€™s old and new definitions were exactly the same.

The problem (just one more time) is that the other 10 tables with a similar definition (re Datetime column) works fine.

I think I need to check something from the SQL part - but have no idea whatā€¦

27

But did you do the last part? i.e. Wizard an app using ONLY the 2 files?
Not just look at the definitions but actually use them in an isolated environment?
The fact that a datetime2 is being generated suggest a difference.
Maybe some residual definition is somehow getting in the way (No idea how) but

28

What I would say would be most useful here isā€¦

Use the Clarion trace for the file giving you trouble and one of the similar files that works fine.
Make sure you trace from before the file is opened in both cases.

When it opens the file Clarion gets a bunch of information about the file as it is declared on the SQL side. It uses that information, for example to give you the ā€œfile does not match declarationā€ if you have a column in your clarion dictionary that does not have a similarly-named column on the SQL side.

What you are looking for is any difference in the information that Clarion gets for the Modified_date column for your problem file, and for your OK file. And if you can see that difference in the Trace file, you should be able to see a similar difference in the SQL table declaration.

Although having said thatā€¦I just looked at a trace file for my system, which is Oracle, not MSSQL, and the relevant part is a bunch of lines like:

045BCH(2) 11:16:25.460 Getting information for field 11 Getting attribute OCI_ATTR_NAME for Parameter 03E63ED8H Return Code: 0 Time Taken:0.00 secs
045BCH(2) 11:16:25.460 Getting attribute OCI_ATTR_DATA_TYPE for Parameter 03E63ED8H Return Code: 0 Time Taken:0.00 secs
045BCH(2) 11:16:25.460 Getting attribute OCI_ATTR_CHARSET_FORM for Parameter 03E63ED8H Return Code: 0 Time Taken:0.00 secs

And what you are really interested in is the data_type that is returned, which at least in the Oracle trace, it doesnā€™t tell you. With luck the MSSQL trace is more informative :confused:.

1 Like