|
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.
|
|
|
|
Insert records to history from external application
Posted: 27 Jul 10 2:22 PM
|
We are developing our own customer support site in-house using Zend Framework/PHP5 running on Apache/Linux talking to a Microsoft SQL Server. We would like to log certain usages of the support site to our SalesLogix 7.5 installation's History table.
When accounts are created, we capture the SalesLogix UserID of the customer, so we have the link we need. What we are struggling with is finding documentation and/or a method to log an support site activity to the SL history table. I know that we'll need to use the SalesLogix OLE DB provider if we want to write to SL's DB in a sync friendly manner. What I don't know is how to access this through PHP5/Linux... I see that this would be easier for us were it .NET, but it isn't.
The best solution I have come up with is to write the events we want to record to history to a "go-between" table, and then run a DTS package that includes the SLO OLE DB provider on a schedule to write the history records "after the fact" (the history does not have to be real-time).
Does anyone have any better solutions?
Also, can anyone point me to some good documentation or functions I can use to simplify the writing of the history record? I figure my go-between table can record the CONTACTID, DATE, DESCRIPTION and NOTES, everything else can be calculated/created/hard-coded in the DTS package INSERT. Are there any gotchas I might come across? |
|
|
|
Re: Insert records to history from external application
Posted: 29 Jul 10 4:59 AM
|
Hi Robin - the go-between is fine - you could write a simple app in .NET/VB that polls this table and utilises the provider to write the data back to history. Simple use SLXProfiler to record the addition of a history item and you'll see all the cols you need to populate.
|
|
|
|
Re: Insert records to history from external application
Posted: 29 Jul 10 1:33 PM
|
Thanks for the reply! Unfortunately (for me), I'm am not much of a .NET/VB dev, but I can write a mean query! I was hoping to be able to run a simple query on a schedule that would take the entries in my "Log2SLX" table, and write them properly to SalesLogix. I have already written an Opportunity update query that I run in this fashion (although not automated on a schedule) to correct Opp $ values when our currency exchange rates change and it works fine and syncs out to remotes, etc...
I did as you suggest, created a dummy history entry with SLXProfiler and I see a nice INSERT statement:
INSERT INTO HISTORY (HISTORYID,ACTIVITYID,TYPE,ACCOUNTID,CONTACTID,ACCOUNTNAME,CONTACTNAME,STARTDATE,DURATION,DESCRIPTION,TIMELESS,USERID,USERNAME,ORIGINALDATE,RESULT,CREATEDATE,CREATEUSER,MODIFYDATE,MODIFYUSER,COMPLETEDDATE,COMPLETEDUSER,LONGNOTES) VALUES ([DBTYPE_STR,"HUZ1RA006NFU"],[DBTYPE_STR,"HUZ1RA006NFU"],[DBTYPE_I4,262148],[DBTYPE_STR,"AOZ4M0000011"],[DBTYPE_STR,"CGO3YA0000IG"],[DBTYPE_STR,"ACME"],[DBTYPE_STR,"Smith, Robin"],[DBTYPE_DBTIMESTAMP,20100729 00:00:05.000],[DBTYPE_I4,0],[DBTYPE_STR,"NDI Support: Test History Entry"],[DBTYPE_STR,"T"],[DBTYPE_STR,"ADMIN "],[DBTYPE_STR,"Administrator"],[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000],[DBTYPE_STR,"Complete"],[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000],[DBTYPE_STR,"ADMIN "],[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000],[DBTYPE_STR,"ADMIN "],[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000],[DBTYPE_STR,"ADMIN "],[DBTYPE_STR | DBTYPE_BYREF,"The NDI Support Site created this test History Entry as a test of history entry"])
However, I also see a series of UPDATE statements that follow:
UPDATE HISTORY SET USERID=[DBTYPE_STR,"ADMIN "],CREATEDATE=[DBTYPE_DBTIMESTAMP,20100729 14:55:17.000],CREATEUSER=[DBTYPE_STR,"ADMIN "],MODIFYDATE=[DBTYPE_DBTIMESTAMP,20100729 14:55:17.000],MODIFYUSER=[DBTYPE_STR,"ADMIN "],COMPLETEDUSER=[DBTYPE_STR,"ADMIN "],NOTES=[DBTYPE_STR,"The NDI Support Site created this test History Entry as a test of history entry"] WHERE HISTORYID=[DBTYPE_STR,"HUZ1RA006NFU"] AND USERID=[DBTYPE_STR,"ADMIN "] AND CREATEDATE=[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000] AND CREATEUSER=[DBTYPE_STR,"ADMIN "] AND MODIFYDATE=[DBTYPE_DBTIMESTAMP,20100729 14:54:15.000] AND MODIFYUSER=[DBTYPE_STR,"ADMIN "] AND COMPLETEDUSER=[DBTYPE_STR,"ADMIN "] AND NOTES IS NULL
UPDATE HISTORY SET LONGNOTES=[DBTYPE_STR | DBTYPE_BYREF,"The NDI Support Site created this test History Entry as a test of history entry "] WHERE HISTORYID=[DBTYPE_STR,"HUZ1RA006NFU"]
Why is the history record written as an insert but then followed by two updates that seem to duplicate info written in the insert? The initial INSERT seems to do what the two subsequent UPDATE statements do, or am I missing something?
I also see these two more UPDATE statements:
UPDATE CONTACT SET LASTHISTORYDATE = '20100729 10:55:19', LASTHISTORYBY = 'ADMIN' WHERE CONTACTID = 'CGO3YA0000IG'
UPDATE ACCOUNT SET LASTHISTORYDATE = '20100729 10:55:19', LASTHISTORYBY = 'ADMIN' WHERE ACCOUNTID = 'AOZ4M0000011'
I get that this is writing to the ACCOUNT and CONTACT the last modified date/user. Do you think it is fine to not run this portion? I would prefer that my automated insert of a history record not be recorded as a "most recent contact" with the Account/Contact. If this update is just for informational purposes somewhere in the client, then I'll leave it out, but if it is used somewhere else "system-wise", then I should not omit.
Lastly, perhaps a newbie question, but is there a standard way with the OLE DB provider to get SL to generate the HISTORYID/ACTIVITYID I'd need? I dug through some documentation but am a little lost... In standard SQL tables with a uniqueidentifier type, you just do NEWID() and it generates a uniqueidentifier for you. I am looking for the equivalent of the NEWID() that would create the ID of Data Type STANDARDID needed for creating a new history record. Is this documented somewhere that I am missing? In one of the SLX Developer .chm files, I found mention of SLX_DBIDS function. If I have a SQL DTS package that included the SLX OLE DB Provider, can I somehow call this function directly from my SQL INSERT statement?
Thanks
Robin |
|
|
|
Re: Insert records to history from external application
Posted: 29 Jul 10 2:02 PM
|
Right...!
1) Ignore the 2nd updates - not required, just "the way it does it". You have no need, just the insert will do. The 2nd ones are the provider doing its job
2) The update to acc/con aren't needed - especially if you don't want them updated. There's nothing that needs this system wise.
3) The ONLY way of having SLX gen the ID is to run it through the provider. However, you are only running the insert on history. This table should be set to auto-generate an ID. In other words, you omit the primary ID and SLX will just do it for you - rather like NewID() but without the need. Just specify your cols and SLX will do the rest PROVIDED that the table is set to auto-gen. You can check this via Admin/Architect and running DBMgr. Select the HistoryID for the History table and if it's set to auto-generate you're good to go. Otherwise, just click it on and commit !
Regards
Mike
|
|
|
|
Re: Insert records to history from external application
Posted: 29 Jul 10 2:03 PM
|
Right...!
1) Ignore the 2nd updates - not required, just "the way it does it". You have no need, just the insert will do. The 2nd ones are the provider doing its job
2) The update to acc/con aren't needed - especially if you don't want them updated. There's nothing that needs this system wise.
3) The ONLY way of having SLX gen the ID is to run it through the provider. However, you are only running the insert on history. This table should be set to auto-generate an ID. In other words, you omit the primary ID and SLX will just do it for you - rather like NewID() but without the need. Just specify your cols and SLX will do the rest PROVIDED that the table is set to auto-gen. You can check this via Admin/Architect and running DBMgr. Select the HistoryID for the History table and if it's set to auto-generate you're good to go. Otherwise, just click it on and commit !
Regards
Mike
|
|
|
|
Re: Insert records to history from external application
Posted: 29 Jul 10 2:04 PM
|
Right...!
1) Ignore the 2nd updates - not required, just "the way it does it". You have no need, just the insert will do. The 2nd ones are the provider doing its job
2) The update to acc/con aren't needed - especially if you don't want them updated. There's nothing that needs this system wise.
3) The ONLY way of having SLX gen the ID is to run it through the provider. However, you are only running the insert on history. This table should be set to auto-generate an ID. In other words, you omit the primary ID and SLX will just do it for you - rather like NewID() but without the need. Just specify your cols and SLX will do the rest PROVIDED that the table is set to auto-gen. You can check this via Admin/Architect and running DBMgr. Select the HistoryID for the History table and if it's set to auto-generate you're good to go. Otherwise, just click it on and commit !
Regards
Mike
|
|
|
| |
| |
|
Re: Insert records to history from external application
Posted: 06 Aug 10 3:33 PM
|
Hmmm, well I finally found the time to look at this and I have made progress but have hit one stumbling block.
Through Admin, I created a table in my SalesLogix Test DB called Log2SLX (set it to not sync to remotes), added the following fields: LOGDATE, CONTACTID, DESCRIPTION, NOTES and set them to match the equivalent fields in HISTORY table.
I created an INSERT statement that queries this table, pulls the other fields needed (ACCOUNTID, ACCOUNT, etc, from the CONTACT table where CONTACT.CONTACTID = Log2SLX.CONTACTID), "hardcodes" the other fields needed (CATEGORY, TYPE, DURATION, etc). I created a dummy record in this table with a valid CONTACTID in my test SalesLogix DB (the vision being my external app will write entries to this table and a DTS package will run on a schedule to properly insert them to SL History in a sync friendly manner).
I also checked the properties of the HISTORYID field in the HISTORY table and it was NOT set to auto-increment, so I checked ON the "Field is Auto Increment" setting for this field and committed the change to the DB.
I then created a DTS package using the OleDB provider and including the insert statement:
INSERT INTO sysdba.HISTORY
( TYPE, ACCOUNTID, CONTACTID, ACCOUNTNAME, CONTACTNAME, CATEGORY, STARTDATE, DURATION, DESCRIPTION, TIMELESS, USERID, USERNAME, ORIGINALDATE, RESULT, RESULTCODE, CREATEDATE, CREATEUSER, MODIFYDATE, MODIFYUSER, COMPLETEDDATE, COMPLETEDUSER, NOTES, LONGNOTES, ATTACHMENT )
SELECT
262154 AS TYPE, C.ACCOUNTID, C.CONTACTID, C.ACCOUNT, C.LASTNAME + ', ' + C.FIRSTNAME AS ContactName, 'NDI SupportSite E-Mail' AS CATEGORY, N.LOGDATE, 1 AS DURATION, N.DESCRIPTION, 'F' AS TIMELESS, 'ADMIN' AS USERID, 'Administrator' AS USERNAME, N.LOGDATE, 'Complete' AS RESULT, 'DON' AS RESULTCODE, N.LOGDATE, 'ADMIN' AS CREATEUSER, getdate() as CREATEDATE, 'ADMIN' AS MODIFYUSER, getdate() as MODIFYDATE, 'ADMIN' AS COMPLETEDUSER, N.NOTES, N.NOTES, 'F' AS ATTACHMENT
FROM sysdba.CONTACT C, sysdba.NDI_LOG2SLX N
where (C.CONTACTID=N.CONTACTID)
If I execute it, I get the following error:
Statement has terminated: Cannot insert the value NULL into column 'HISTORYID', table SalesLogixTest.sysdba.HISTORY'; column does now allow nulls. INSERT fails.
I went into Admin and double-checked that HISTORYID is set to auto-increment.
As an experiment, I modified the above INSERT to hard-code a fake HISTORYID (I made a unique one up), saved the change, ran the package and it works (and I can see the history entry from SL client) so I am confident everything else is correct, but it would appear that the "field is auto increment" setting does not mean that using the OleDB provider with my DTS package auto-gens the ID for me, unless I am missing something...
Do I need to include some equivalent to the NewID() function used with regular uniqueidentifier SQL fields?
Any ideas will be gratefully appreciated
Robin |
|
|
|
Re: Insert records to history from external application
Posted: 07 Aug 10 4:57 AM
|
Get into the SLX DB Manager (available in both ADMIN and Architect) - go to the history table and set the HISTORYID field to Auto Increment.
Restart the SalesLogix application service.
That should get you over that final hurdle
ws |
|
|
|
Re: Insert records to history from external application
Posted: 07 Aug 10 10:02 AM
|
| Robin, everything looks good. I think, like Walter says, the SalesLogix Server service needs to be re-started (I thought it sent that change to server but apaprently not). So, find out where SLXServer is running and re-start that and you should be fine. |
|
|
|
Re: Insert records to history from external application
Posted: 07 Aug 10 10:04 AM
|
| Robin, everything looks good. I think, like Walter says, the SalesLogix Server service needs to be re-started (I thought it sent that change to server but apaprently not). So, find out where SLXServer is running and re-start that and you should be fine. |
|
|
|
Re: Insert records to history from external application
Posted: 09 Aug 10 9:34 AM
|
Thanks for the replies, I am not having much luck here...
I restarted the SalesLogix service (in fact I rebooted my DB server). The SalesLogix Service I need to restart is running on the DB server (its the machine that my clients all point to under "Server Settings > Select or enter the SalesLogix Server" in the data link properties dialog). Right? I also have it running on my sync server (which I also restarted anyway).
I have checked in the SECTABLEDEFS table that AUTOINCREMENT is set to T for the HISTORYID field in the HISTORY table definition, so I am sure it took the setting.
The DTS package still doesn't work... 
I then tried running my query through Admin's "Execute SQL" feature to rule out any issue with how I have created the DTS package using the OleDB provider, and I get the same error reported back from Admin...
My SLX install is still on SQL2000. I know this is officially not a supported DB server anymore, but "under the hood" it is no different than SQL 2003, which is supported. Could this be the issue? |
|
|
|
Re: Insert records to history from external application
Posted: 12 Aug 10 8:25 AM
|
DTS? I though we were all using SSIS?
We're all (OK so 99% of us) using SQL Server 2005
Anyway... I though that the SLX OLE DB provider must be on that box?
check your datetime formats as well..... |
|
|
|
Re: Insert records to history from external application
Posted: 19 Dec 10 6:06 PM
|
fiogf49gjkf0d I'm having the same issue - even placing the script within the SLX client. I have enabled auto increment on the history table and restarted the service, but still get the "cannot insert nulls" error. Is there anything else that needs to be set?
SLX 7.2.1
Thx
Leon |
|
|
|
Re: Insert records to history from external application
Posted: 19 Dec 10 7:08 PM
|
fiogf49gjkf0d Are you sure that you are getting the error from the PK? It may be coming from a FK on the table (e.g. AccountID).
I would suggest that you use SLX Profiler to get the SQL statement being executed, then test it against SQL Management studio (if using MS SQL) and determine what is the actual cause of the problem. |
|
|
| |
|
Re: Insert records to history from external application
Posted: 19 Dec 10 9:16 PM
|
fiogf49gjkf0d Thx for the replies. I've enabled the auto-increment on the HISTORYID of the HISTORY table. Error is definitely on the HISTORYID, not a FK, after running the profiler - HISTORYID column does not allow nulls (my query for the insert statement definitely returns accountids anyway). The provider is just not generating the ID for that column - even though it is enabled (and excluded from the query as per normal SQL)
EDIT:: Does the provider support an insert using a SELECT statement?
e.g.
insert into history (ACCOUNTID , etc , etc) select accountid, ' ' , ' ' from account where status = 'XX' |
|
|
|
Re: Insert records to history from external application
Posted: 28 Dec 10 10:46 AM
|
fiogf49gjkf0d I don't know if SLX OLE DB Provider provides Insert SELECT statements (have never tried one!).....
AutoIncrement doesn't work on a base 36 artithmetic field does it? And the insert statement will fail due to the null ID Value before the autoincrement kicks in....so your insert statement should fail.
You could always create your own History ID, 12 alpha numeric characters, unique.... |
|
|
|
Re: Insert records to history from external application
Posted: 28 Dec 10 2:45 PM
|
fiogf49gjkf0d Originally posted by RJ Samp
I don't know if SLX OLE DB Provider provides Insert SELECT statements (have never tried one!).....
AutoIncrement doesn't work on a base 36 artithmetic field does it? And the insert statement will fail due to the null ID Value before the autoincrement kicks in....so your insert statement should fail.
You could always create your own History ID, 12 alpha numeric characters, unique....
|
|
The INSERT / SELECT construct does not work with autoincrement (I just tested it). I suggest that you try using a simple INSERT VALUES construct first to verify that autoinc is working. If it is, I'm afraid you might have to re-code to loop round, inserting one painful record at a time. Dull. |
|
|
|
Re: Insert records to history from external application
Posted: 04 Dec 12 4:50 PM
|
fiogf49gjkf0d
I realise this post if over 2 years old but I'm looking for exactly the same thing. I can insert into the HISTORY table but need to set my own 12 character HISTORYID. Did you ever find a way to get one auto-generated?
Regards,
Mick
|
|
|
|
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!
|
|
|
|
|