11/26/2024 10:22:19 PM
|
| |
| 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 writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Executing SQL on MSSQL and Oracle easily
Posted: 17 Nov 06 4:17 AM
|
fiogf49gjkf0d Hi all,
Having a host database that runs on Oracle and a remote office that runs on MSSQL isn't the most ideal of scenarios but thats what I'm stuck with, anyway... when I'm required to run SQL statements that include calculating a day using the current date it becomes a problem.
I cannot run these sort of statements via Workgroup Admin as of the cross-platform (different now functions) and when it comes to running them through a script the connection.execute(sqlstatement) is very unreliable when it comes to syncing across to remotes and the remote office which is obviously a huge problem.
Due to this, in order to run a simple update statement I have to (using ADO) select the recordset then cycle through each of the records which seems so much for a simple update statement.
Are there any easier methods for approaching this? Any help appreciated.
Kev |
|
|
|
Re: Executing SQL on MSSQL and Oracle easily
Posted: 17 Nov 06 8:02 AM
|
fiogf49gjkf0d I'm afraid I don't have an answer and would love to hear suggestions too. Oracle prefers "SYSDATE" as opposed to NOW(). I've run into this once already myself and had to hard code a date (it was a one time code run) but that sucked and I see further problems moving forward. |
|
|
|
Re: Executing SQL on MSSQL and Oracle easily
Posted: 17 Nov 06 10:00 AM
|
fiogf49gjkf0d One thing to keep in mind that using date functions inline in SQL insert/update statements will cause issues with sync & UTC conversion, regardless of whether you have Oracle/MSSQL/etc.
For example, if you did this (on MSSQL):
update account set modifydate = getdate() where accountid = 'AXXXX0000001'
You are not using an ISO formatted date, which will cause the update to choke when synched to a remote (the remote won't get the update applied). Also you are not allowing the provider to properly convert to UTC when stored in the database.
So, avoid doing that. Instead, use updateable recordsets or dates as variables and properly converted to an ISO format. (You can see an article on updateable recordsets here: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=76). The problem comes from the fact that the provider doesn't get it's chance to work with the date and do the necessary conversions when placed inline. This does mean you're working more row-by-row, looping through records performing updates, but you're at least ensuring that sync will properly happen and that the updates can make it out to only those who actually have access to the records you're updating.
A nice side effect is that it will work correctly on MSSQL & Oracle as well  |
|
|
|
Re: Executing SQL on MSSQL and Oracle easily
Posted: 17 Nov 06 10:12 AM
|
fiogf49gjkf0d Nice. Thanks for the article. That addressed my issues and gets me thinking about my next question. I'll start a new thread through because it's not really relevant to this one and could be useful for others. |
|
|
|
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!
|
|
|
|
|
|
|
|