CRM 2011 Integration with A+ using Scribe Insight — ODBC query issues

I’ve recently been working to integrate CRM 2011 with A+. I am using Scribe Insight as the go-between and I have been dealing with a few ODBC query issues. Unfortunately, when connecting through an ODBC connection, you are not able to use all of your standard SQL query syntax or functions. And, as I soon found out, not all ODBC drivers act the same.

After creating my jobs through the iSeries ODBC driver, I was told that I should use the Scribe DB2 Wire Protocol driver to connect to A+. This allows for the embedding of credentials so the job can run while a user is not logged in. For the jobs I had already created, I had to compare various date/time data (that were not in Date/Time fields but char fields) to the Scribe variable “LastRunDateTime”. This was needed to only run against records that had changed or been added since the last time the job was run and rows were returned. In order to compare these char fields, I had to parse and concatenate them into an ODBC TIMESTAMP format. All went well–until I changed my ODBC connection to use the new driver. Now all my parsed/concatenated strings were coming back with an error that they were not in a TIMESTAMP format. Trust me they were. I even went so far as to start with a formated string (2013-04-15 12:00:00.000000) and replace individual parts with my parsed field data. For some reason, some of the time entries caused the error. No real rhyme or reason.

Solution:
As usual, after sleeping on it, I decided to parse the known quantity which was the “LastRunDateTime” variable. This variable always came back as an ODBC TIMESTAMP so I could easily parse and concatenate it to match the format of the non-standard date/time fields. I was also able to concatenate the non standard fields so I had a way to compare both Date and Time to the LastRunDateTime” variable. However, I did have to take into account that when the time in the non-standard Time field was less than 12:00:00, the preceding “0” was removed. So I had to add it back. You’ll also note that I had to concatenate an empty character when I had 6 integers for my time (WHEN 6 THEN ”||ATOATM). If I didn’t, when I had 5 characters, the preceding “0” in my ELSE would be removed.

Here’s an example of my comparison query:

ATOADT||
CASE LENGTH(TRIM(ATOATM))
WHEN 6 THEN ”||ATOATM
ELSE ‘0’||ATOATM
END
>=
right(year( :LastRunDateTime),2)||
CASE length(TRIM(month( :LastRunDateTime)))
WHEN 1 THEN ‘0’||month( :LastRunDateTime)
ELSE ”||month( :LastRunDateTime)
END||
CASE length(TRIM(DAy( :LastRunDateTime)))
WHEN 1 THEN ‘0’||DAy( :LastRunDateTime)
WHEN 2 THEN ”||DAy( :LastRunDateTime)
END
||
CASE LENGTH(TRIM(hour( :LastRunDateTime)))
WHEN 1 THEN ‘0’||hour( :LastRunDateTime)
WHEN 2 THEN ”||hour( :LastRunDateTime)
END||
CASE LENGTH(TRIM(minute( :LastRunDateTime)))
WHEN 1 THEN ‘0’||minute( :LastRunDateTime)
WHEN 2 THEN ”||minute( :LastRunDateTime)
END||
CASE LENGTH(TRIM(second( :LastRunDateTime)))
WHEN 1 THEN ‘0’||second( :LastRunDateTime)
WHEN 2 THEN ”||second( :LastRunDateTime)
END

So far so good. All has been functioning flawlessly.

I hope this helps – and, of course, let us know if you have any questions.