MSSQLServerDatabase Hard Crash

Great point!

One thing I demoed at XDC was getting JSON & XML direct from the database and simply populating a XOJO XML/JSON object with the one row/one column recordset. Let the Database do the work :slight_smile:

[quote=100145:@Rich Hatfield]Also, not sure if it will make a difference… I see in his code he is using:

rs.IdxField(q).Value

instead of:

rs.IdxField(q).StringValue

I am curious if one of the columns is defined as an integer and IdxField.Value is treating it as such when trying to write to a string.[/quote]
Shouldn’t be a problem. Value is a variant, so it will auto-convert the integer to a string representation. If IdxField is an integer field, then

Trim(rs.IdxField(q).Value)

is equivalent to

Trim(Str(IdxField(q).IntegerValue))

The original problem isn’t with my code is my assumption. This is a system that does 43 other stored procedures just fine.

The problem is with three stored procedure, all use a temp table internally (#temp) crashes my app at the point at which it should be getting a record set back (Native connection). Or “Invalid Curser State” (ODBC) immediately after sending the request to the server, no time for the stored procedure to execute. Yes, I know I could try ADO but I’m not overly fond of that.

The other 43 SP’s do not use temp tables internally. This is how I arrived at the problem being the SP.

As for the data type, in all cases for this app the end result is a text file and my method has been fine so far. I know you can get into trouble with certain conversions but everything has checked out fine.

As for having the server do the string concatenation, my method is fine. The reason I was involved in this project and created the app (which I call DataPuller, not creative I know) was that one of the members of the reporting team was having difficulty in formatting a row with the proper columns quoted while maintaining the field separator.

An advantage of my doing the file formatting is that the same SP could be used for more than one vender who may want a different delimiter or want no quoting. The SP remains uncluttered and unconcerned with quoting and delimiters. If the vender wants to add or remove columns the reporting team does that, my app doesn’t care.

[quote=100168:@Zane Reinbold]The original problem isn’t with my code is my assumption. This is a system that does 43 other stored procedures just fine.

The problem is with three stored procedure, all use a temp table internally (#temp) crashes my app at the point at which it should be getting a record set back (Native connection). Or “Invalid Curser State” (ODBC) immediately after sending the request to the server, no time for the stored procedure to execute. Yes, I know I could try ADO but I’m not overly fond of that. [/quote]

As a quick test, I can confirm your issue.

I created a simple store procedure that uses #temp tables:

[code]create procedure P_TEST as

select cast(‘one’ as varchar(50)) [col_1], cast(‘two’ as varchar(50)) [col_2], cast(‘three’ as varchar(50)) [col_3]
into #T_TEMP;

select * from #T_TEMP;
[/code]

if you call the SP, exec P_TEST, from SQL Management Studio… it returns the desired results. If you however do the same in Xojo and assign it to a recordset… it will crash.

as a work around… go ahead create a non in memory temp table and then have the SP truncate the table before inserting data into it and return results.

Not sure if this will work (never tried it) but how about using a select to get the data from your SP rather than an execute. A bit of a hack but may get round the Xojo issue with the temp table. For example:

SELECT * FROM OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters') WHERE somefield = anyvalue

Suppose my point is that where your looping for every row 1000, 2000 whatever, by making a small change to your stored procedure everything gets done in one set based operation and you don’t have to loop. Performance big win!

Doesn’t necessarily get you round the problem of the hard crash but would help negate the loop and performance hit.

I would also parameterise your delimiter/quoting options in your procedure.

Wouldn’t really overcomplicate a stored procedure at all, my view is if your going to use stored procedures you may as well USE stored procedures.

But that’s just me :slight_smile:

I created feedback case (https://xojo.com/issue/34037)>]34037 based on my findings.