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!
|
|
ExecuteNonQuery and other mysteries
Posted: 11 Oct 07 2:33 PM
|
All,
I'm baffled by the apparent limitations of the SLX provider. I'm using the OLEDB connection in .NET. One example that still stumps me is that procedures seem to fail if you try to pass more than 20 parameters. The exception tells me that a parameter that was expected wasn't provided. I've been told that the SLX provider doesn't officially support stored procedures, but as long as I limit the number of parameters, it seems to work.
The one thing that doesn't seem to work is ExecuteNonQuery. It doesn't throw any exception at all. It just doesn't do anything. The workaround I've implemented follows:
object duh = cmd.ExecuteScalar();
I'd love more information if anyone has any, but mostly I hope this saves someone the hours I spent trying to figure out why my stored procedures weren't doing anything when called by SLX.
Thanks,
Eric |
|
|
|
Re: ExecuteNonQuery and other mysteries
Posted: 11 Oct 07 2:51 PM
|
Hi Eric,
I use ExecuteNonQuery with the SLX provider all the time, never any problems. I don't, however, use the SLX provider to call stored procs that often. Since they're not supported, you can't really expect that they'll still just work anyway, right? |
|
|
|
Re: ExecuteNonQuery and other mysteries
Posted: 12 Oct 07 7:08 AM
|
| I also use ExecuteNonQuery all of the time with no issues. What operations are you trying? One limitation that I know if is that you cannot do UPDATE .... FROM commands in any shape or form. |
|
|
|
Re: ExecuteNonQuery and other mysteries
Posted: 12 Oct 07 12:46 PM
|
| I do update queries all the time using the provider. There are no issues that I know of with ExecuteNonQuery. If you post a sample of your SQL, we can take a look at it. |
|
|
|
Re: ExecuteNonQuery and other mysteries
Posted: 16 Oct 07 6:50 AM
|
I also use the ExecuteScalar, NonQuery and other ADO.net functions without issue. What would baffle me is specfically the fact that you are getting no errors. I have not seen where ADO.net would hold back if an error did indeed occurr.
Mark |
|
|
| |
| |
|
Re: ExecuteNonQuery and other mysteries
Posted: 08 Nov 07 9:34 AM
|
I think the ExecuteNonQuery issue I was encountering may have only been problem with Stored Procedures, which aren't supported. 
Now I'm running into the problem that, absent stored procedures, I have to pass my queries as strings. What's more, I can only send one statement per execution, because if you try to send multiple semicolon delimited statements the provider chokes on the semicolon, "Failed to parse SQL." 
Further, when I try to use parameters on text commands I run into issues. I've seen examples where people use ? as a parameter placeholder, but if I need to use the same parameter more than once, I have to re-add the parameter for each instance of the ? and make very sure they are in the correct order. 
When I try to use the @parameter name, I get an error that I must declare a scalar variable. How do I do that if I can't pass multiple statements in a single command?!? 
It looks like "best practice" is to use a StringBuilder to create the query string and use Replace to substitute values into the string. Have fun passing in dates this way, or even strings that might have a character that requires escaping. SQL Injection, anyone? 
I also understand you can't execute an UPDATE query with a FROM and JOIN clauses. So, do I need to denormalize my tables? 
Would it be fair to assume that there is no support for transactions, either? 
All in all, given that data access is the primary function of the application, shouldn't SalesLogix put a but more effort into developing a more robust provider? Maybe someone else can set me straight. |
|
|
|
Re: ExecuteNonQuery and other mysteries
Posted: 28 Sep 09 11:52 AM
|
I know this thread has been dead for a while, and that the statement "SLX provider not suppporting stored procs" holds for older versions of SLX. I use SLX 7.5.1. Does anybody know whether SLX provider in SLX verison 7.5.1 supports stored procs or not?
Thanks for answering.
-Andrew |
|
|
| |
|