11/26/2024 12:24:17 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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 general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Date problem in Import Script
Posted: 02 Aug 06 2:37 PM
|
fiogf49gjkf0d We very recently upgraded to v6.2 and we have an Oracle database. We have Legacy Script that we use to import data using a .TXT file to build accounts on our database. The script currently has a variable declared to store the current date. I set that variable using this code:
dtCurrentDate = Format(Now, "yyyy-mmm-dd")
then on my database Insert statement I use the dtCurrentDate variable to use this data on all inserted records. I can see the date in the database using SQLPLUS to view data, but when I go into SLX Client, the day is one day prior. particularly when I look in the Notes/history tab, the completed date is showing up one day prior, but the database has the correct date in the History table. I am aware of v6.2 using GMT but I'm not sure how to change this script to account for that. Any help appreciated? thanks |
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 2:50 PM
|
fiogf49gjkf0d Are you importing these via the SLX provider or via a direct connection to the Oracle database? |
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 2:54 PM
|
fiogf49gjkf0d What timezone is the PC in that is running the imports.
What timezone is the SlxServer in (ie the system that is running the SlxProvider)? |
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 3:02 PM
|
fiogf49gjkf0d I have a view built off the 'Tools' menu in the client that executes a script to do the Inserts to the database. I am connected to the database thru my OLE DB connection that was built. would this be considered via the provider or not really? I'm not sure....but this script does do the Inserts to the tables.
sorry I'm so confusing. thnks |
|
|
| |
|
Re: Date problem in Import Script
Posted: 02 Aug 06 3:12 PM
|
fiogf49gjkf0d What does the code look like that makes the connection for the import? Is is standard DBOpenSQL stuff, or some connection string. What exactly do you mean by "thru my OLE DB connection that was built"?
The reason for this question is that SLX 6.2 stores all dates as UTC, and then when viewed via the client translates the UTC date to the local date for the pc (based on timezone settings in Windows). Usually when someone sees behavior like what you describe it is because they are not inserting their data via the SLX provider. This means the date value you save in the database will be converted to local time as if it were UTC - but since it is not UTC you're really just subtracting your UTC offset from that date (which if no time is specified will put it on the previous day).
Make sense? |
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 3:25 PM
|
fiogf49gjkf0d I'm just not real clear on this b/c since I'm running this script thru the client, I consider it to be thru the SLX Provider, right? this script was created to run via the SLX Client and I am signed on in the client when I run the script. In the script I set a SQL string with the Insert statement and use this to insert it - dbexecutesql strSQL.
It sort of makes sense what your saying, but in my case I am inserting it thru the provider and viewing it thru the provide and everything is in the same time zone.
|
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 3:51 PM
|
fiogf49gjkf0d Would it be possible to post some code (or at least some parts of it)? Without that we really won't be able to track anything down. |
|
|
|
Re: Date problem in Import Script
Posted: 02 Aug 06 4:40 PM
|
fiogf49gjkf0d In addition to SQL statement(s) that Ryan asked for, can you also tell us what Service Packs and HotFixes were installed. |
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 6:37 AM
|
fiogf49gjkf0d I had the same problem. We were migrating from 5.2 to 6.2 and all the dates were wrong. Here's what I did to solve the problem:
I logged on to the saleslogix support site here:
http://support.saleslogix.com/login/index.php3?DestinationPage=/home/index.php3?cellid=100000001011
and downloaded the SLXTZ library file and SDK here:
http://support.saleslogix.com/home/index.php3?cellid=409030001011
I read it the documentation. There are functions to go from GMT to Local time and from local time to GMT. I installed the DLL. I referenced the SLXTZ.DLL in the Insert program so that I could call the functions in the object. Then I wrapped it in my own function in the Insert program (language: VB6).
Here is the essential code:
Public Function AdjustTime(datInDate As Date) As Date
Dim objTzHelper As New SLXTZ.TzHelper
Dim objCurrentTz As ITimeZone
Set objCurrentTz = objTzHelper.TimeZones.CurrentTimeZone
AdjustTime = objTzHelper.LocalToGMT(datInDate, daAutoAdjustment)
Then I could just call it as a function:
MyDate = AdjustTime(MyDate)
|
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 8:47 AM
|
fiogf49gjkf0d Yes I can post some of the code that pertains to what I'm having a problem with. We are on V6.2 Service Pack 3 with hot fixs 3 & 5.
in my script I am setting an element to be current date by doing this:
dim dtCurrentDate as string
dtCurrentDate = Format(Now, "yyyy-mmm-dd")
then my insert code looks like this:
****************************************************************************
strSQL = "INSERT INTO HISTORY (HISTORYID,TYPE,ACCOUNTID,CONTACTID,ACCOUNTNAME,CONTACTNAME,PRIORITY,CATEGORY,STARTDATE,DURATION,DESCRIPTION," & _
TIMELESS,RECURRING,USERID,USERNAME,ORIGINALDATE,RESULT,CREATEDATE,CREATEUSER,MODIFYDATE,MODIFYUSER,COMPLETEDDATE,COMPLETEDUSER,NOTES,LONGNOTES) "
strSQL = strSQL & "VALUES ('" & strNewhistid & "', "
strSQL = strSQL & "'262147', "
strSQL = strSQL & "'" & strNewaccid & "', "
strSQL = strSQL & "'" & strNewconid & "', "
strSQL = strSQL & chr(39) & strcompany & chr(39) & ", "
if strlname <> "" then
strSQL = strSQL & chr(39) & strfname & " " & strlname & chr(39) & ", "
else
strSQL = strSQL & chr(39) & strcompany & chr(39) & ", "
end if
strSQL = strSQL & "'None', "
strSQL = strSQL & "'Source Import', "
strSQL = strSQL & "'" & dtcurrentdate & "', "
strSQL = strSQL & "15, "
strSQL = strSQL & "'Source Import', "
strSQL = strSQL & "'T', "
strSQL = strSQL & "'F', "
strSQL = strSQL & "'ADMIN', "
strSQL = strSQL & "'Administrator', "
strSQL = strSQL & "'" & dtcurrentdate & "', "
strSQL = strSQL & "'Complete', "
strSQL = strSQL & "'" & dtcurrentdate & "', "
strSQL = strSQL & "'ADMIN', "
strSQL = strSQL & "'" & dtcurrentdate & "', "
strSQL = strSQL & "'ADMIN', "
strSQL = strSQL & "'" & dtcurrentdate & "', "
strSQL = strSQL & "'ADMIN', "
strSQL = strSQL & "'Record imported from Source Data', "
strSQL = strSQL & "'Record imported from Source Data')"
logixclearerror
dbexecutesql strSQL
if logixerrorcode <> 0 then
setpropertyof "memoSQL","text",getpropertyof("memoSQL","text") & Chr$(13) & Chr$(10) & _
"There was an error inserting the History record #" & lngreccount_c & ": " & logixerrortext & " - (" & logixerrorcode & ") strSQL: " & strSQL
end if
***********************************************************
This is a legacy script not a new script created in v6.2 - I was hoping to not have to rewrite for 6.2
I did see what dave sent about the GMTtoLocal function and I will go look at that documentation, but I wasn't sure if I could access those functions in this legacy script. do you know? |
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 9:32 AM
|
fiogf49gjkf0d I still have lots of Legacy scripts running. I usually do not have problem, however,
when I do have problems it is usually with dates. I have also had issues with how
the provider works after applying an SLX ServicePack or HotFix.
Anyway, the 6.2 provider wants dates expressed as "yyyymmdd hh:nn:ss". Can
you try changing the formatting of dtcurrentdate and see what happens.
BTW, If you don't call out the time I believe the provider is going to default it to either
midnight or noon so it will be best to add it to the format string.
|
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 11:41 AM
|
fiogf49gjkf0d thanks Frank, I tried changing the format to include the Time but I got a runtime error saying type mismatch on it, so it didn't like that. this is what I changed it to:
dtCurrentDate = Format(Date, "yyyymmdd hh:nn:ss")
I had it as "Format(Now,........" first but it didn't like that either.
|
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 11:43 AM
|
fiogf49gjkf0d Dave - when you opened that SLXTZ file, it tries to install something.....but if I do this will I have to install
this on all client machines if I go to production with this? or do I just need to install the DLL on my pc. I'm going to try and find the documentation on this and read it I just didn't know it was something I had to load on my pc. |
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 12:00 PM
|
fiogf49gjkf0d Install the library file where you want to call it. If you just need it to tweak the dates on the way into the database, you only need to install it on the server where the import program runs. |
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 1:10 PM
|
fiogf49gjkf0d If you changed from a DBExecuteSQL to a DBOpenSQL/DBInsert/DBSetValue/etc the problem should go
away (as this allows the SLX provider opportunity to work with the date values).
ie:
Dim lHnd As Long
lHnd = DBOpenSQL("select * from history where 1=0", False)
DBInsert lHnd
DBSetValue lHnd, "historyid", strNewHistID
DBSetValue lHnd, "type", "262147"
'...continue with the rest of the fields
DBPost lHnd
DBClose lHnd
I prefer this syntax anyway since you don't have to worry about single quotes in the strings, converting to ISO dates, etc as well.
I have many legacy scripts still running on customer systems and have not needed to use the time zone sdk for them and have
not had problems with UTC date conversion.
-Ryan |
|
|
|
Re: Date problem in Import Script
Posted: 03 Aug 06 2:29 PM
|
fiogf49gjkf0d Ryan,
I use the literal SQL statement method in some of my scripts and the dates
get properly stored in GMT format.
As a test, I just issued the following SQL statement using the Execute SQL
function in the 6.2 WorkGroup Admin against a Oracle 8.1.7 DB
insert into history (historyid, createdate) values ('1','20060803 14:28:00')
The statement executed and the date was properly GMT and stored in the
database. When I display the date with SLQPlus using...
select historyid, TO_CHAR(createdate, 'DD-MON-YYYY HH24:MI:SS') from history where historyid='1'
...the createdate displays as "03-AUG-2006 18:28:00"
When I view the date through the Provider using...
select historyid, createdate from history where historyid='1'
...the createdate displays as "08/03/2006 2:28:00 PM"
Is it possible that something else is going on? Like maybe (as you questioned) the connection is not going through the provider, or a ServicePack or HotFix.
|
|
|
|
Re: Date problem in Import Script
Posted: 04 Aug 06 8:57 AM
|
fiogf49gjkf0d Using inline SQL statments to insert or update dates will work and the provider will properly interpret them on the way in into UTC time, where appropriate, only if the dates are in ISO format (20060804 08:52:51).
Any other date formats will insert into the database, however the provider will not adjust them for UTC so the dates go into the database with exactly the same date and time as the source. When the provide reads these dates back to display in the client or through the Admin's execute SQL utility, the UTC conversion will happen on them causing the dates to appear different than what the source was. If you insert dates not in ISO format when using in-line SQL you must be responsible for correctly adjusting them for UTC.
Personally I would go with Ryan's approach of using DBInsert and DBSetValue as this eliminates your problem as he described.
|
|
|
|
Re: Date problem in Import Script
Posted: 04 Aug 06 10:27 AM
|
fiogf49gjkf0d Kris,
Yes I understand all this and I always use ISO formatted dates. Yes Sondra can rewrite her code but it seems like she should not have to.
My reply to Ryan way focused on Sondra's reply Posted: 03 Aug 06 11:41 AM. The reply indicated that a type related error was thrown after Sondra change the format. I can generate the same error if I issue my sample SQL statement directly through the Oracle OLEDB Provider. Any thoughts on this?
|
|
|
|
Re: Date problem in Import Script
Posted: 07 Aug 06 3:01 PM
|
fiogf49gjkf0d Thanks Ryan - I did go in and change the script to use the DBOpenSQL/DBInsert/DBSetVAlue as you suggested and that worked for me.
I did see then in the other posts where they were saying that I could have done with literal values of the date/time, but this way allows me to not have to change the script if I run it next month....next year..whatever.
thanks to everyone for their help.  |
|
|
|
Re: Date problem in Import Script
Posted: 14 Nov 06 7:27 AM
|
fiogf49gjkf0d I work with Sondra and we are having the same problem with an update. What is the syntax for an update using DBOpenSQL/etc. ? The code we currently have is the following:
strSQL = "UPDATE C_OPPORTUNITY_EXT SET "
strSQL = strSQL & "EFF_DATE = '" & streffdate & "', "
strSQL = strSQL & "COMPETITOR = '" & strCompetitor & "', "
strSQL = strSQL & "MODIFYUSER = '" & CurrentUserID & "', "
strSQL = strSQL & "MODIFYDATE = '" & Format(Now, "dd-mmm-yyyy") & "' "
strSQL = strSQL & "WHERE OPPORTUNITYID = '" & stroppid & "'"
logixclearerror
dbexecutesql strSQL
Any help will be greatly appreciated. |
|
|
|
Re: Date problem in Import Script
Posted: 14 Nov 06 8:35 AM
|
fiogf49gjkf0d You would change it to this:
Dim lHnd As Long
lHnd = DBOpenSQL("select * from c_opportunity_ext where opportunityid = '" & stroppid & "'", False)
DBEdit lHnd
DBSetValue lHnd, "EFF_DATE", streffdate
DBSetValue lHnd, "COMPETITOR", strCompetitor
DBSetValue lHnd, "MODIFYUSER", CurrentUserID
DBSetValue lHnd, "MODIFYDATE", Now
DBPost lHnd
DBClose lHnd |
|
|
| |
|
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!
|
|
|
|
|
|
|
|