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!
|
|
Store proc
Posted: 10 Jul 07 3:52 PM
|
| I am very new to SalesLogix. I heard that we can store proc in SalesLogix but i am still not able to figure out hot to do it. I would really appreciate if you can write some basic steps that we should follow to call store proc? |
|
|
|
Re: Store proc
Posted: 10 Jul 07 4:16 PM
|
| That's not the intended way for SLX to work. All data access in SLX is to be done via the SLX OLEDB provider. This way it will all synchronize to remotes and SLX security can be enforced. Using a stored proc bypasses this. |
|
|
|
Re: Store proc
Posted: 11 Jul 07 3:04 AM
|
Subash,
As Ryan pointed out, using stored procedures will bypass the OLE DB Provider and the changes to the database will not be sent out to remotes. Also, you break the SLX security model when passing SQL commands outside of the OLE DB provider. However, it is possible to use a stored procedure, but you need to understand that doing so could have negative implications. In addition, I have found that any stored proceudre in which you pass paramaters to, will not work properly. To call a stored procedure from a web client script use the following in your Action code:
strSQL = "execute sp_procedurename"
x = DBOpenSQL(strSQL, False)
I beleive that syntax is similar for the LAN client?
If you are trying to run a complex query and want to use the stored proc for performance reasons, this should work fine. If you are trying to run procs that alter data, however you can run into lots of issues such as the ones mentioned above. Also SLX generates it's SQL dynamicly, so this can cause problems when trying to run a stored proceudre in this way. I believe that's whay procs that expect paramaters do not work properly. |
|
|
|
Re: Store proc
Posted: 11 Jul 07 7:02 AM
|
Right on Eric.
Based on the issues you point out (especially security and the fact that SP's have no concept of handling GMT based datetime)... SP's are something to totally avoid.
SP's never have been nor do I ever expect them to be in the SalesLogix "model" of doing things.
Note: Every time I've seen questions about SP's it seems to come from the "dba" world rather than the "application programme/developer" side. SalesLogix treats the db as a "data store" and nothing else. It's a "top down" thing... not "bottom up".
--
rjl |
|
|
|
Re: Store proc
Posted: 11 Jul 07 6:41 PM
|
Executing a stored proc in the LAN client is simple to initiate - eg (assuming a connection object objCon has been created):
strSql = "EXEC [procname]"
objCon.Execute(strSql)
It is something that I have done in the past (and yes, I do have a SQL Server background  ) It's a very fast way of performing data processing on the server - if you architect it right. An example might be for a data import process, where the data is imported into 'staging' tables that are NOT sync'd to remotes.
Here's the process:
Import data
Run the stored proc to process the imported data in the non-sync staging tables
Use standard SLX iterative looping, via the provider, to load the data into 'standard' SLX tables that will sync out.
In line with the advice given above, do not go down this path unless you understand the implications of bypassing SLX OLEDB Provider synchronisation.
|
|
|
|
Re: Store proc
Posted: 12 Jul 07 6:47 AM
|
Yep, know exactly what you are talking about.
Your warning/disclaimer is very appropriate.
I would suggest you put a "test" in your code to make sure you are executing on the main db and not remote:
If NOT OnMainDb Then
'do NOT execute this
Exit.....
End If
Function OnMainDb()
If GetSsalarValue("SELECT DBTYPE FROM SYSTEMINFO WHERE SYSTEMINFOID = 'PRIMARY') = 1 Then
OnMainDb = True
Else
OnMainDb = False
End If
End Function
Function GetScalarValue(sql)
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, Application.GetNewConnection
If Not (rs.EOF Or rs.BOF) Then
GetScalarValue = rs(0).Value & ""
Else
GetScalarValue = ""
End If
rs.Close
Set rs = Nothing
End Function
Function OnMainDb()
.. something like that.. above.. 
--
rjl
|
|
|
|