MSSQLServerDatabase Hard Crash

Yeah the MERGE is ok but makes the SQL a total mess every time you want to write something that you need to add/update which is why I thought it would be a great feature to have in the MBS SQL. Didnt know that SQLlite had REPLACE. If it isnt added to the MBS plugin then I might have a go at writing it myself, I think I might be able to :wink: but then again will probably take me a month where Christian would do it in a hour (plus it would work!!!).

you get used to it - it’s just like any other SQL statement :wink: Mind you, not the clearest example above

The advantage to Merge is that you, the developer, is in control of the matching criteria whereas REPLACE & INSERT OR REPLACE (IIRC) rely on the unique constraints to determine whether to update or insert.

Yeah, I guess i guess it works both ways. I might just write a little function that takes the INSERT statement and the UPDATE statement and returns back the MERGE statement to use. Thanks for the example and the help.

Interested that you said the native MSSQL plugin doesnt support MSSQL 2012 when that is exactly what I talk to every day. Still think I am going to get the MBS SQL plugin as the VARCHAR(MAX) issue drives me mad.

oh right - maybe the fix is out there then - I’m only going by the Feedback case which says “Fixed & waiting verification from testing staff”.

If I can help further, just DM me. :slight_smile:

Thanks again, just noticed you are a fellow Brit!!!

Rich: Are you saying for me to create the temp table for them to populate using the SP?

My code (somewhat generically) is

[code]rs=DB.SQLSelect(“EXECUTE MyStoredProc”)

If DB.Error Then
// Log an error and e-mail the humans…
Else
If rs<>Nil Then
f=GetFolderItem(MyFileToWrite)

  If f<>Nil Then
    File=TextOutputStream.Create(f)
    
    File.WriteLine BeforeData 
    
    cnt=rs.FieldCount
    While Not rs.EOF
      ReDim Temp(-1)
      For q=1 to cnt
        Temp.Append Trim(rs.IdxField(q).Value)
      Next
      File.WriteLine Join(Temp, Separator)
      
      rs.MoveNext
    Wend
    File.Close
  End
End

End[/code]

I was just having a look at your code (to see if I could learn anything) and I hope you dont mind me suggesting this but I was thinking that it may be possible to make it a little quicker because as I understand it, doing a ReDim in a loop slows things down especially when you have large amounts of rows coming from a database. As far as I can see you dont really need the array and could do it like this which might make it a little quicker. Somebody else may say the IF is slower than the ReDim and that the string concatenation is slower, I am not sure about this with Xojo.

[code]Dim Temp as string

While Not rs.EOF
Temp = “”
For q=1 to cnt
Temp = Temp + Trim(rs.IdxField(q).Value)
If q < cnt then Temp = Temp + Separator
Next

File.WriteLine Temp

rs.MoveNext
Wend[/code]

The reason behind the array to build the string is because of the overhead involved with string=string + string. There can be many fields so with each iteration more memory and time is consumed.

Yes, I know ReDim has a penalty too but it seems to be quicker to build the string with a join.

Thanks for the explanation, thats really useful to know. I didnt think that Xojo suffered with strings being concatenated. Also didnt know that the arrays with a join is quicker. I hope you didnt think my making the suggestion was rude.

Not at all.

Every time you want to add something to a string XOJO has to make a copy of the string, as the string grows so does the memory requirements and time, it adds up over hundreds or millions of iterations.

That’s my understanding.

I guess that is why I have heard of people allocating large memory blocks and writing to the block, I think Kem did something like this in his string class. Maybe worth a look at to see if you could make it even quicker, with millions of iterations it might make a difference.

You could download the MBS plugin and try it. At least it’ll tell you if it’s a problem with the Native plugin or not?

I imagine memory blocks would make a huge difference. I really need to get my head around them and start using them where I can.

I think I will try the MBS plugin next.

You should really profile the code and see if it makes any difference at all. Unless your strings grow really big, you may not see any difference. Are we talking thousands of fields per record here?

Have I misunderstood - Are you actually getting a recordset and the problem is on the recordset loop or is it crashing on the SQLSelect statement?

Interestingly, Join is faster for a relatively small number of strings. Like a 2x difference at 10 of em.

Couldn’t you just do all the string concatenation in your stored procedure? This T-SQL statement run over the AdventureWorks2008R2 sample database takes every rows [name] column and turns it into a csv.

[code]
DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', ‘’) + Name
FROM Purchasing.Vendor
WHERE Name IS NOT NULL

SELECT @Names AS ‘Product_Name’[/code]

example output
Australia Bike Retailer, Allenson Cycles, Advanced Bicycles, Trikes, Inc., Morgan
and so forth.

Obviously you’ll need to play with the above to make it work for you. But, this way the data comes out in the format you want and your resultset in Xojo simply has one row with one column which is your concatenated string.

None of this Xojo looping rubbish :wink:

(the above is just one way to do it!)

Yes… this is a much better way to do this.

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.