|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
DateTime often wron
Posted: 23 Jul 09 7:26 AM
|
Hello,
we are using Saleslogix 7.2.0.1501. We have an .NET application, which connects via SLX OLE DB provider. When the .NET application INSERTs or UPDATEs rows in the database, it sometimes (not allways) write the wrong time - when the row is read next it shows 1 hour later.
The datetime fields are written as parameter in a sql command. In SLXProfiler I see the wrong data:
---------- Client SQL ---------
INSERT INTO ZBeitrag(ZBeitragID, BeitragsNr, CreateDate, CreateUser, Aktiv_Von, Aktiv_Bis, Anzahl_Aufmacher_Soll, Anzahl_Bilder_Soll, Anzahl_Grafiken_Soll, Anzahl_Kaesten_Soll, Anzahl_Seiten_Soll, Anzahl_Tabellen_Soll, KZ_Anzeige_Verkauft, KZ_Muss_Artikel, KZ_Autorkorrektur, Vorlagenname, Beitragsart, Bemerkung, Gesperrt_vor_Aktiv_Datum, Leistungsart, ModifyDate, ModifyUser, PfadID_Dokumente, Schlagwort, SeccodeID, Status, Datum_Status_Gesetzt, Urspr_BeitragsID, Honorar, Glossar_Soll, Urspr_Ausgabe, Urspr_Jahr, Urspr_LfdNr, Urspr_ObjektID, Urspr_RubrikID, UserID_Anzeige_Verkauft, UserID_Redakteur, KZ_Vorschau_fuer_Kunde, Termin_Startdatum, Termin_Enddatum, Termin_Eintrittspreis, Termin_KZ_Gewinnspiel, Termin_KZ_Online, Termin_Typ_Key, Termin_Typ_Name, Termin_Org_Key, Termin_Org_Name, Termin_Ort_Key, Termin_Ort_Name) VALUES ([DBTYPE_STR,"Q9R2WA26WVWJ"], [DBTYPE_I4,310855], [DBTYPE_DBTIMESTAMP,20090723 12:57:29.154], [DBTYPE_STR,"U9R2WA0000JY"], [DBTYPE_DBTIMESTAMP,20090723 12:57:29.154], [NULL], [DBTYPE_I4,0], [DBTYPE_I4,0], [DBTYPE_I4,0], [DBTYPE_I4,0], [DBTYPE is unknown], [DBTYPE_I4,0], [DBTYPE_STR,"F"], [DBTYPE_STR,"F"], [DBTYPE_STR,"F"], [NULL], [DBTYPE_I4,3], [NULL], [DBTYPE_STR,"F"], [DBTYPE_I4,10], [DBTYPE_DBTIMESTAMP,20090723 12:57:44.091], [DBTYPE_STR,"U9R2WA0000JY"], [DBTYPE_STR,"Q9R2WA0G90U5"], [NULL], [DBTYPE_STR,"F9R2WA000002"], [DBTYPE_I4,10], [DBTYPE_DBTIMESTAMP,20090723 12:57:29.154], [NULL], [DBTYPE is unknown], [DBTYPE_STR,"F"], [NULL], [NULL], [NULL], [NULL], [NULL], [NULL], [DBTYPE_STR,"U9R2WA0000JY"], [DBTYPE_STR,"F"], [NULL], [NULL], [DBTYPE is unknown], [DBTYPE_STR,"F"], [DBTYPE_STR,"F"], [NULL], [NULL], [NULL], [NULL], [NULL], [NULL])
---------- Executed SQL ----------
INSERT INTO ZBeitrag(ZBeitragID, BeitragsNr, CreateDate, CreateUser, Aktiv_Von, Aktiv_Bis, Anzahl_Aufmacher_Soll, Anzahl_Bilder_Soll, Anzahl_Grafiken_Soll, Anzahl_Kaesten_Soll, Anzahl_Seiten_Soll, Anzahl_Tabellen_Soll, KZ_Anzeige_Verkauft, KZ_Muss_Artikel, KZ_Autorkorrektur, Vorlagenname, Beitragsart, Bemerkung, Gesperrt_vor_Aktiv_Datum, Leistungsart, ModifyDate, ModifyUser, PfadID_Dokumente, Schlagwort, SeccodeID, Status, Datum_Status_Gesetzt, Urspr_BeitragsID, Honorar, Glossar_Soll, Urspr_Ausgabe, Urspr_Jahr, Urspr_LfdNr, Urspr_ObjektID, Urspr_RubrikID, UserID_Anzeige_Verkauft, UserID_Redakteur, KZ_Vorschau_fuer_Kunde, Termin_Startdatum, Termin_Enddatum, Termin_Eintrittspreis, Termin_KZ_Gewinnspiel, Termin_KZ_Online, Termin_Typ_Key, Termin_Typ_Name, Termin_Org_Key, Termin_Org_Name, Termin_Ort_Key, Termin_Ort_Name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Our company is in Germany, so we are at GMT+01:00 + 1 hour DST = GMT+02:00. The Createdate of the above row was 13:57:29, so when calculating the right time to write to the database it should be 11:57:29 and not 12:57:29. When I re-select the row with the application I get the time 14:57:29.
Any idea? What is here wrong? 
Yours
Thorsten |
|
|
|
Re: DateTime often wron
Posted: 23 Jul 09 11:12 AM
|
What happens if you look at the data directly in the Database?
In other words, run SLX Profiler, to capture the statement, but right after it is inserted, verify what value was actually written to the DB.
Then reselect it from the App. That will let you determine if the problem is with the data actually going into the DB, or with the app reading the data.
Also, are you doing any conversion on the data time when writing it from the .NET application? You shouldn't as the Provider would be the one doing the conversion for you.
And, is the machine where this code running from configured with the correct Time Zone? SLX relies on the OS for the DST information.
|
|
|
|
Re: DateTime often wron
Posted: 24 Jul 09 1:01 AM
|
The data in the database is also wrong.
We do no modification/conversation at the datetime fields. We write 13:57:29, in the database is written 12:57:29 and when we read back the data we get 14:57:29. When reading the conversion of the OLEDB provider is correct, but not when writing...
I've checked the time zone and DST informations. It is correctly displayed in Control Panel and the API function GetTimezoneInformation() also gives the right values. |
|
|
|
Re: DateTime often wron
Posted: 24 Jul 09 1:33 AM
|
Originally posted by Thorsten Mahr
Hello,
Our company is in Germany, so we are at GMT+01:00 + 1 hour DST = GMT+02:00. The Createdate of the above row was 13:57:29, so when calculating the right time to write to the database it should be 11:57:29 and not 12:57:29. When I re-select the row with the application I get the time 14:57:29.
|
|
This got me a bit confused here as well.
Client SQL is the data before it is parsed.
The value on the Client SQL for the Create Date shows 12:57, so it should've gone to the DB as 10:57 (not 13:57).
The parser is not adjusting the date at all, it is writing it to the DB as 12:57 and then when it is read, it is properly adjusting it to 14:57 (GMT + 2).
The problem is that the SLX Parser is not adjusting this field and this is likely caused because it couldn't parse it because it was wrapped by a DBTYPE[]
I haven't done an insert statement through .NET in a while, but I typically just use a String parameter myself.
Try it that way and see if that helps. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|