I have encountered a strange problem with updating a MSSQL database using recordsets. I have been working on creating this rather extensive program for some number of months now and have never seen this particular error until I introduced a new set of circumstances today.
Basically, here’s the steps I’m following:
I’m using a SQL SELECT statement to populate a recordset (named rsXsec) with a single record from a SQL database table (named xsecImages).
I prepare it for editing with rsXsec.Edit
Then I change the contents of one field (named ImgX1UR) in the recordset by using the code rsXSEC.Field(“ImgX1UR”).StringValue = someString (which is a string variable)
Then I finish it off with the UPDATE statement (rsXsec.Update)
Once again, I’ve never had a problem with this part of the code for a long time until I tried something different today.
These variations work (and always have) … string values like “O2.5UMNRG_050112_0000” and “O25UM-NRG_050112”. Note that the 1st string has a “.” in it and the 2nd string has a “-” in it.
… BUT this one doesn’t “O2.5UM-NRG_050112” … it throws an error code 3621 (“The statement has been terminated”) which is one of those rather meaningless SQL error statements that apparently says something else actually went wrong before the last error message was trapped and displayed)
The only difference I can see between the strings that work and those that don’t is that using a period (".") or a hyphen ("-") by itself in the string assigned to the recordset field works, but if you have both in the same string, it doesn’t.
At first I thought, “Oh oh, here we go again with another ‘gotta use escape characters routine’” … but this is not a SELECT statement problem (where those errors normally occur) and why in hells bells should you have to “sanitize” the contents of a simple string assigned to a recordset field (whose table column is set up as a VARCHAR(50) data type).
Any ideas??? I’ve about tried everything I can think of and Googled my eyes out. They were already red and watering from this awful head cold I’ve got which this is not helping
Thanks, Paul. But the encodings are the same, and besides, I can go in and hard code a value to the string to make it work the one time (not having BOTH a “.” and a “-”, e.g., “O2.5UMNRG_050112_0000”) and then fail the next by hard coding another value like the following “O2.5UM-NRG_050112_0000” (which has BOTH a “.” and a “-”) … That’s the only difference!
SUCCESS … I think … Don’t you just love it when you find the answer to your own problem and you don’t have the slightest damned idea why what you did works?
Well, after 2 full days of looking and trying everything under the sun to fix this bizarre deal, I finally found something that does make it work. But since I like to learn from these experiences (especially when I have made this much of a time investment), the bad news is I have very little idea why what I did worked … which bothers me to no end since that means I really didn’t “learn” anything in the process!
The idea that Paul Lefebvre offered kept me thinking all night about encodings. The “solution” turned out to be changing the data type in the SQL database table definition from VARCHAR(50) to NVCHAR(50). After reading what I could to see “why” this magically solved my problem, the only substantive difference I see between the 2 data types is the fact that NVARCHAR deals with Unicode and VARCHAR doesn’t … here’s the definitions I found:
varchar [ ( n | max ) ]
Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.
So now, even though my problem is fixed, I actually have more questions than when I started. Besides the fact that I don’t currently understand why what I did fixed things, it makes me wonder, if since the fact that all natively-created strings in Xojo are inherently by default UTF-8 encoding, does this mean that I have to ALWAYS use NVARCHAR for table column definition in order to properly handle data from Xojo strings? That would confuse me royally since I’ve used VARCHAR for just about every such application without any problems to date.
By any chance does anyone have a plausible explanation for this … sure would make me feel better
I’m glad you got it resolved. I don’t know enough about MS SQL Server to come up with a reason, but I can guess!
In your original example, you were putting UTF-8 into a column designed for ASCII (essentially). There must have been some conversion going on, either in the MS SQL Server plugin or on MS SQL Server itself. Perhaps that was getting confused. Does specifically setting your string encoding to ASCII in Xojo allow the update to the VARCHAR column?
Personally, I think using NVARCHAR does make more sense in this day and age, but does not seem truly necessary for your column since you are just putting a very specific string value into it.
Conclusion: Encodings are a pain. I’m glad you got it working.
Thanks once again, Paul, for offering your thoughts. Certainly makes sense and offers a possible explanation. Quite frankly, had it not been for your initial guess last night about encodings in general, I don’t think I would have arrived where I did. I am sincerely appreciative that you took the time to respond … sure saved me lots in return!
Great question, Paul … and one that I might just “learn” something definitive from all of this after all . I’ll give it a go this evening after dinner. My “intellectual curiosity” can be both a blessing and a curse.
No thanks, Norman … been there, done that, and got the tee shirt to prove it!
… And I’ll bet that is one big reason, Norm, why you are as good as you are at what you do … you’re trapped inside with that insidious computer and have no outdoor life or alternative … Well, maybe for a month or so around July. Smartest move I ever made was the one from the snow-belt of the eastern suburbs of Cleveland, Ohio to near Myrtle Beach, SC in 1979 … never looked back!
Yeah keep trying to tell wife we need to move to Costa Rica or something like that
She complains about being far aaa from kids & grandkids & such or something frivolous like that
But yeah if I got near a beach full time I’d probably just snorkel all day
Why Costa Rica, Norman? … there’s a beautiful beach-side place available right now in Belize formerly owned by another software guru who won’t be going back there anytime soon, I can guarantee ya!
Just been to Costa Rica and really liked it - would go back in heart beat
And I already know where the Canadian beach is - been there
Nice place and I already know it has internet - used that
)
