11/22/2024 4:50:13 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 ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Stored Procedure
Posted: 27 Mar 06 10:51 PM
|
Maybe this is a kind of old question. I've browse it around, many people said not to use sp on slx,
but who knows in this forums...
I use sp on slx 6.1 and it works just fine, but i can't use it in slx 6.2, anybody knows? it said
"error parse sql"
fyi, this is my code:
set conSQL = Application.GetNewConnection
strSQL = "Exec sysdba.sp_AddContact " _
& " @externalaccountno='" & companyNo & "'" _
& ", @account='" & companyName & "'" _
& ", @title='" & lvwContact.SelectedItem & "'" _
& ", @lastname='" & lvwContact.SelectedItem.SubItems(1) & "'" _
& ", @address1='" & lvwContact.SelectedItem.SubItems(2) & "'" _
& ", @city='" & lvwContact.SelectedItem.SubItems(3) & "'" _
& ", @workphone='" & lvwContact.SelectedItem.SubItems(4) & "'" _
& ", @mobile='" & lvwContact.SelectedItem.SubItems(5) & "'" _
& ", @email='" & lvwContact.SelectedItem.SubItems(6) & "'" _
& ", @webaddress='" & lvwContact.SelectedItem.SubItems(7) & "'"
conSQL.CursorLocation = 3
conSQL.Execute(strSQL)
conSQL.Close
set conSQL = nothing
i also try sp with parameter like usual vb script code based on msdn but it also failed ...
any help, will be gratefull ... 
thx,
-fajar
|
|
|
|
Re: Stored Procedure
Posted: 28 Mar 06 10:07 AM
|
fiogf49gjkf0d I thought running most stored procedures through the 6.2 provider is not supported. The provider does not know what to do with them. In order to run a stored procedure you would need to bypass the provider and use a native SQL connection. This would bypass security and logging, but it would any ways if you were using a stored procedure. |
|
|
|
Re: Stored Procedure
Posted: 28 Mar 06 10:56 AM
|
fiogf49gjkf0d Fajar,
Perhaps the 6.2 provider would allow the syntax if you didn't use the named parameters? Try that syntax and you might have more luck.
Anyway, you are aware that none of this will synchronize, right? |
|
|
|
Re: Stored Procedure
Posted: 28 Mar 06 7:56 PM
|
benadryl and pregnancy benadryl pregnancy safety fiogf49gjkf0d mr ryan,
sp without named parameters, you mean like this one:
strSQL = "Exec sysdba.sp_AddContact " _
& " default " _
& ", '" & companyNo & "'" _
& ", '" & companyName & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(1) & "'" _
& ", default " _
& ", default " _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(2) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(3) & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(4) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(6) & "'" _
& ", '" & lvwContact.SelectedItem & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(7) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(5) & "'"
sorry but it also return the same error message "failed to parse sql"
any idea?
thx,
-Fajar |
|
|
|
Re: Stored Procedure
Posted: 28 Mar 06 8:54 PM
|
am i pmsing or pregnant quiz am i pregnant quiz
it said "variable undefined DBOpenSQLFromDatabase" ... ah slx 6.2 makes a newbie like me confused ...
anyway thx,
-Fajar
sqlConStr = "Driver={SQL Server}; " _
& "Server=SLXServer; " _
& "Database=slxserver; " _
& "UID=xxx; " _
& "PWD=xxx;"
sqlStr = "Exec sysdba.sp_AddContact " _
& " default " _
& ", '" & companyNo & "'" _
& ", '" & companyName & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(1) & "'" _
& ", default " _
& ", default " _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(2) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(3) & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(4) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(6) & "'" _
& ", '" & lvwContact.SelectedItem & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(7) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(5) & "'"
vHandle = DBOpenSQLFromDatabase (sqlConStr, sqlStr, True)
if LogixErrors Then
msgBox "Handle:" & vHandle
msgBox LogixErrorText
exit sub
end if
dbClose(vHandle) |
|
|
|
Re: Stored Procedure
Posted: 28 Mar 06 10:12 PM
|
Ah yes, I think I remember now what is causing your problem. I seem to remember something about getting a failed to parse error when you use "exec" and the proc owner prefix. Try dropping the "exec sysdba." part, like this:
strSQL = "sp_AddContact " _
& " default " _
& ", '" & companyNo & "'" _
& ", '" & companyName & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(1) & "'" _
& ", default " _
& ", default " _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(2) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(3) & "'" _
& ", default " _
& ", '" & lvwContact.SelectedItem.SubItems(4) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(6) & "'" _
& ", '" & lvwContact.SelectedItem & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(7) & "'" _
& ", '" & lvwContact.SelectedItem.SubItems(5) & "'"
Give that a shot, I just tried it out something similar and worked for me (although I do get the failed to parse error with the owner qualifier and exec also)
BTW, unless you really need to use a proc (and have no remotes), I'd advise against it for updates/insterts and stick to just selecting data only. That whole unsupported thing, you know |
|
|
|
Re: Stored Procedure
Posted: 29 Mar 06 12:52 AM
|
fiogf49gjkf0d thx for all the reply and here is my summary:
you can use sp in slx 6.2 but...:
1. you can't use parameterized query, you sholud use plain query.
2. drop the keyword "exec" and the "schema of the sp", just give the name of the sp.
3. you can't use 'default' keyword instead, you should give every parameter for the sp.
(this one cause me several hours.. damn.)
regards,
-Fajar |
|
|
|
Re: Stored Procedure
Posted: 06 Apr 06 9:18 AM
|
fiogf49gjkf0d I use this...
Before use stored procedures:
CREATE TABLE [EXECUTESQL] (
[SQL] [varchar] (2500) COLLATE Cyrillic_General_CI_AS NULL -- !!!!Your COLLATE
) ON [PRIMARY]
CREATE TRIGGER sysdba.EXECUTESQLQUERY
ON sysdba.EXECUTESQL
FOR INSERT
AS
DECLARE @SQL VARCHAR(2500)
SELECT @SQL = SQL FROM inserted
ROLLBACK TRANSACTION
EXEC(@SQL)
In SLX:
strSQL = "insert into executesql (SQL) values(' & replace(strSQL,"'","''") & ')"
I know that is not right to use stored procedures but ... )
|
|
|
|
Re: Stored Procedure
Posted: 19 Jul 07 12:41 PM
|
Hi Fajar,
I am very new to SalesLogix. I need to call a store procone ID from SalesLogix. The store proc add records to the table and after it return one value(ID) back to the user
I would really appreciate if you can help me out. I have tried the way you posted but still not working.... |
|
|
|
Re: Stored Procedure
Posted: 19 Jul 07 12:42 PM
|
Originally posted by subash shrestha
Hi Fajar,
I am very new to SalesLogix. I need to call a store proc from SalesLogix. The store proc add records to the table and it returns one value(ID) back to the user
I would really appreciate if you can help me out. I have tried the way you posted but still not working.... |
|
|
|
|
|
Re: Stored Procedure
Posted: 19 Jul 07 2:12 PM
|
This is how i tried,
ISLX_InsertAddress is a store procedure.......
strSQL = "ISLX_InsertAddress" _
& ",CNKFGA000CDV " _
& ",HOME" _
& ",40 Fairway RD" _
& ",Newark" _
& ",DE" _
& ",19711" _
& ",ADMIN" _
& ",' & now & '"
dim conSQL
set conSQL = Application.GetNewConnection
conSQL.CursorLocation = 3
Set rs = conSQL.Execute(strSQL)
GetIdAfterInsert = rs.Fields(0).value
rs.Close
conSQL.Close
set conSQL=nothing
I get the parse error and i am using sales Logix 6.2
Can you please someone suggest how we can solve this problem. |
|
|
|
Re: Stored Procedure
Posted: 19 Jul 07 8:13 PM
|
May I ask why you are doing this using stored procedures? This is not how SLX has been designed to work. What will you do if you set up remote users and/or offices? None of this stuff will work because stored procedure execution bypasses SLX synchronisation.
PP |
|
|
|
Re: Stored Procedure
Posted: 20 Jul 07 3:16 AM
|
Originally posted by Phil Parkin
May I ask why you are doing this using stored procedures? This is not how SLX has been designed to work. What will you do if you set up remote users and/or offices? None of this stuff will work because stored procedure execution bypasses SLX synchronisation.
PP |
|
I using a program that all update/insert/delete queries executes throw slx oledb provider |
|
|
|
Re: Stored Procedure
Posted: 20 Jul 07 7:02 AM
|
May I ask why you are doing this using stored procedures?
I am asking the same question. Why use SPs to insert and update records? It bypasses security and creates additional problems when it comes to remote users and remote offices. You are also compromising the integrity of the database because the provider maintains creating key fields and updates all of the create and modify fields.
Its much simpler to just create the code upfront, put it through the provider which is then compliant with the SalesLogix architecture.
Another issue is that by circumventing the provider you are creating an unsupported environment. In this case, if you have to contact your business partner or SalesLogix support they WILL NOT be able to support you. You'll be on your own.
Just my .02
John G.
|
|
|
|
Re: Stored Procedure
Posted: 20 Jul 07 7:55 PM
|
That sounds fine - update, insert and delete queries via the SLX OLEDB Provider will sync to remotes.
Just not stored procedures. |
|
|
|
Re: Stored Procedure
Posted: 03 Aug 07 8:51 AM
|
Originally posted by John Gundrum
May I ask why you are doing this using stored procedures?
|
|
I understand that insert/delete/update usp will not sync (should I put it in my whish list???), but I still think usp-s come in handy when writing complex SELECT statements.
When writing/running queries in MS SQL I know that it will improve performance (usp does not to recompile when rerun, query plan does not have to be calculated again). Is still the same in SLX? Does anyone tested this?
|
|
|
|
Re: Stored Procedure
Posted: 03 Aug 07 9:21 AM
|
| It is valid to call a stored proc from SLX for a complex SELECT statement - however, one thing to keep in mind that SLX security will not be applied to the results. Meaning that users will likely be able to see records in the results that they would not normally be able to see. |
|
|
| |
|
Re: Stored Procedure
Posted: 03 Aug 07 2:46 PM
|
From experience, SQL views are better suited for "complex queries" that will not parse through the provider. Plus, they are now natively supported. I don't want to start a religious debate over the usefullness of stored procedures but there are many ways to solve data access needs. Trying to force stored procedures into an environment that doesn't support them does not make any sense IMO.
Again, I am not claiming there is never a need or place for stored procedures. I am simply suggesting that you take a good hard look at why you are relying on them.
Timmus |
|
|
|
Re: Stored Procedure
Posted: 06 Aug 07 9:29 AM
|
Maybe their shop is SQL Proc savvy....it's in their comfort level. I just showed the guys a simple Account Import script in VB .Net using Command Parameter ADO stuff and they got a glazed look over their eyes......hundreds of lines of code (the usual store 20 rows in 16 tables kinds of stuff for SLX)......
Anyway, some shops use SQL Procs like we use VB Script or Ryan Farley uses C# .Net.....
Whatever happened to Pascal anyway.... |
|
|
|
Re: Stored Procedure
Posted: 15 Aug 07 8:56 AM
|
Pascal? I have a copy sitting in a closet somewhere. 
Anyone have a TRaSh-80 to run it on?  |
|
|
|
Re: Stored Procedure
Posted: 16 Aug 07 8:40 AM
|
| Pascal is still used. It's called Delphi now and SalesLogix is developed in it. Ahhh, the TRS-80 from Radio Shack. My first program ever was making my name scroll across the screen on a TRS-80. Then we moved up to the Apple IIC. |
|
|
|
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!
|
|
|
|
|
|
|
|