I’m having an issue I hope someone can shed some light on.
I wrote a Windows service to pull data from a Microsoft SQL server and write the data to files. Our reporting team creates a stored procedure that pulls the data from the massively complex database in the field order desired. I execute the SP and write the data out according to the specs from the vender. So far this arrangement has worked well.
However, someone in reporting had to do some pretty impressive SQL-FU to get a sophisticated file together. (Impressive to my limited SQL knowledge) He used temp tables (like “#results”) to build the recordset to return to me. My service crashes. A hard crash in the SQL plugin, taking the service down. The error points to the plugin stepping on memory that doesn’t long to it. The SP works when called from MS Studio Manager and Navicat so the SP is good, returns results as expected and we know how long it takes.
If I use the native client to execute the SP it will kick it off and after the SP has run and the data should be returned it crashes.
If I use ODBC (connect string or DSN) it stops right away returning the error “Invalid cursor state”.
If I use the ActualODBC trial then I get results through ODBC on a Mac. (returns only 3 records in the trial version, but it works)
This has happened with two stored procedures so far, both have temp tables involved. One has many temp tables in it. I see no way around other than to manually kick off a SP to populate a real table and read from there. This is not desirable because this is supposed to be automated.
This has our reporting team and SQL Guru baffled. They played with the SP, changing to global temp tables, ensuring the user has the rights to the temp tables, using variables. Nothing made it work. Nothing changed the error(s).
Is there something in the native client that’s getting confused when the results come in?
I was under the impression that the server does its thing with a stored procedure and returns a recordset like a normal SELECT would. Is this not the case?
If it is the prepared statement issue you can safely change this to use dynamic SQL to get round this. Using stored procedures prevents SQL Injection as any parameters passed get treated as data even if the user is a bad guy and tries putting SQL commands in.
IMPORTANT NOTE: It is only safe to use Dynamic SQL when using stored procedures. Direct SQL calls are open to SQL injection.
Patrick:
I’m just doing an sqlselect(“EXEC MyStoredProc”). Then loop through the recordset and write line by line. No prepared statements.
I hadn’t known about dynamic SQL, I’ll have to look into it, but seeing the stored procedure source I’m thinking it’s a level of complexity I’m not sure I want to move to the service. Not to mention any changes to structure would require a re-compile and change control etc…
Christian:
I’ll have to give it a try. Not sure I can win a “Buy a third party plugin” argument, but if it works, maybe it will help my case.
Anthony:
Yeah, I thought of that and immediately shivered. I did fall back to RB2012r2.1 and it does exactly the same thing.
Several (like 43) other stored procedures work. All are simpler queries not using temp tables.
One of the problem children is kind of hard to explain. We have to build a file containing four rows of information per item. So rows 1-4 are item 1, 5-8 are item 2 etc. The data is located in multiple tables that all have to be pulled together to make this happen. Moving the work to the XOJO side sounds like a lot of hitting the server over and over looping through all the tables. Something best left for the server to do the heavy lifting as I understand it. Also, we would prefer to keep the stored procedures where the reporting team can make adjustments if needed.
I too am using MS SQL with Xojo and SPs and the thing you have to remember is that MS SQL has a tendency to default to returning calculated strings (e.g. from concatenation or merging from other tables etc) as NVarChar(MAX) which can cause Xojo to hard crash (not always but sometimes othertimes it just gives and error). The way around it is to do a CAST(fieldname as VARCHAR(5000)) in your returning SP for strings. 5000 appears to be the max you can go before Xojo has a bit of an issue (sometimes!). TEXT types as Wayne has said are a toally NoNo in Xojo and again you have to CAST them. The way I do it is I have a function in MS SQL called fnFormatForXojo which does the CAST. The advantage is that later on when the bug is fixed I can simply change the function to return the unchanged field rather than having to edit all the SPs in a rush. I have also just started to play with MBS SQL Plugin and it appears in my web app to be a little faster but havent used it enough you to be sure.
I would be really interested to know why? What extra do you get? Is it quicker? Are their any issues to watch out for? Do you use the SQLConnectionMBS or SQLDatabaseMBS? Do you find one is better than the other?
Well, it started for me when I discovered the native MSSQL plugin couldn’t talk to SQL Azure and SQLMBS could (not sure if this is still the case). Supposedly SQLMBS is faster, maybe it is, maybe it isn’t - that’s not why I use it.
IMHO Xojo’s native database plugins seem to be in need of some updating/fixing and these are slow to be arriving. On several occasions I’ve highlighted a need/issue/problem to Christian and he’s made a change to the SQLMBS plugin that day and sent it out to me to test and then I have it! It then goes into the next prerelease. Xojo can’t work to those timescales though
Let’s take the MSSQL plugin as an example
if there were a bug in SQLPreparedStatementMBS like the one that exists in the native plugin then I’m convinced it’d be fixed/released in very short order. The feedback request for this bug was created 15 Mar 2013 and appears to not be released (though marked as fixed). That is worth the price of admission to SQLMBS for me.
Another feedback is that the native plugin doesn’t communicate with SQL 2012, that was created 12 Apr 2013 and was marked as fixed 12 Feb 2014 (not released). Frankly, not good enough - and what about SQL 2014?
I use SQLConnectionMBS which has more options available than SQLDatabaseMBS. However SQLDatabaseMBS is easier to migrate to/from the native plugins in terms of Xojo code.
The slightly awkward thing with SQLMBS is getting all the libraries together for the different DB platforms across the 3 Operating Systems. Only yesterday I had brain fail and had to get Christian to remind where to get the Linux SQLite library would be located. In some ways if Christian could put together an example project with all the libraries, build steps for the different platforms it would make life slightly easier. Oh, and I do struggle with MBS documentation generally…
If the native plugins were given the attention I think they should be, I’d certainly look at them again. I’m just not convinced Xojo engineers can spare the time to get into them.
Why does a plugin fix have to fall within Xojo’s normal release cycle? If these MSSQL bugs are fixed, just release a new version of the plugin, why do we have to wait for 2014 R2?
Patrick, that was exactly what I was after and thanks for taking the time to write about it. One thing I would like to see added to the MSSQL plugin is a REPLACE method like the INSERT method. Basically the method would first do an INSERT and if that fails it would then do an UPDATE using the parameters passed in. This would work exactly like the MySQL REPLACE function. The difference is that it would not do a search first as on very large tables this takes a lot of time and resources. The more efficient way is to do the INSERT and if it fails (due to a record already existing) then it does an UPDATE instead. I dont know if Christian will read this thread but would love to know if he would consider adding this as that would push me over to using MBS in all my projects.
Sorry, should have made it clearer, MS SQL does NOT support the REPLACE function, only MySQL as far as I know so the magic would have to happen in your plugin so that it would be possible in all DB’s.
sorry maybe I could have been clearer - SQL 2008 introduced the MERGE operation which should be what your looking for. An example I found online
merge tablename as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
And SQLite has the operation INSERT OR REPLACE which does the same.
