3

I'm having this problem using LiveCode, but I don't know whether that's where the issue resides or whether it's to do with the ODBC drivers.

An app built in LiveCode regularly connects to a SQL Server database, over ODBC, to retrieve various data.

The application responsible for the database is being upgraded, and as part of this, all the text fields are being turned into Unicode text fields. Essentially this means that fields that were previously defined as varchar are now defined as nvarchar, and those previously defined as text are now defines as ntext. (This is SQL Server 2008 by the way.)

Using the queries we've always used in the past, we now get a single character (the first character in the field) instead of the whole text. I can work around for this for now by specifying a conversion to varchar in the select query, eg where the app used to make a request like SELECT id, name FROM tab1 it now makes a request like SELECT id, convert(varchar(255), name) AS name FROM tab1

That works - I get back what I used to get - but (a) it's clumsy and (b) this is fine for now, when the client is just migrating all their existing data into the database, but sooner or later they may actually take advantage of this upgrade to enter some Unicode characters into the fields, and then I'll presumably not get them out.

Unfortunately I don't have access to the database or application in order to insert test data - but I'm assuming this problem will arise - and most likely it won't be obvious, ie there'll just be a subtle problem that my app isn't processing the data that was entered (but thinks it is).

So: is there a way that a LiveCode app, using the current ODBC drivers, can retrieve full data correctly from an nvarchar or ntext field?

Ben Rubinstein
  • 121
  • 5
  • What code are you currently using to extract the data from the database? – runrevmark Mar 20 '13 at 13:11
  • See answers to Monte below - really using `revdb_query`, `revdb_movenext` and `revdb_columnbynumber`, but have tried with `revQueryDatabase`/`revMoveToNextRecord`/`revDatabaseColumnNumbered` instead and that produced identical (wrong) results. Using `revDataFromQuery` in a test app on the same database does retrieve the full data, but unfortunately isn't an easy option in this application. – Ben Rubinstein Mar 21 '13 at 12:39
  • Are you using the result of revDatabaseColumnNumbered(), or a 'holder variable' in the third argument? Since UTF-16 is binary data the result cannot be used. – runrevmark Mar 21 '13 at 13:22

3 Answers3

1

Is it possible you are using revDataFromQuery rather than the more robust revQueryDatabase? I don't use revDataFromQuery but my guess is it's using c string functions that don't support UTF16.

Monte Goulding
  • 2,380
  • 1
  • 21
  • 25
  • [sorry, fell foul of SO editing restrictions] Actually it seems the opposite. I have now retrieved sample data using a general SQL tool (I don't have interactive access to this db - can put compiled apps on a box with access). In response to your comment I checked the two apps (both of which I wrote years ago): the real app, which doesn't get Unicode data, uses revdb_query/revdb_columnbynumber/revdb_movenext; whereas the messing-around-tool which does retrieve the data succesfully uses revDataFromQuery. – Ben Rubinstein Mar 21 '13 at 09:30
  • The docs seem a bit confused about what's new and what's a new name: `revQueryDatabase` is supposedly a synonym for `revdb_query`, whereas `revMoveToNextRecord` and `revDatabaseColumnNumbered` are documented as if new functions, separate from `revdb_movenext` and `revdb_columnbynumber`. @Monte - you seem to know a bit about the internals - is this a doc confusion, or is it possible that while revQueryDatabase is just a synonym, other parts of the same suite have been re-implemented in a more sophisticated manner? – Ben Rubinstein Mar 21 '13 at 09:35
  • I don't know about the internals. I'm guessing. Is the query the same in both apps? – Monte Goulding Mar 21 '13 at 12:05
  • The SQL query is the same. I've now hacked a version of my app using `revQueryDatabase`/`revMoveToNextRecord`/`revDatabaseColumnNumbered` and ran that - identical results. So the current status appears to be that `revDataFromQuery` works, `revQueryDatabase` doesn't. Unfortunately, while my test queries are a few K, in production use the results can be >100MB - and this app has run into memory issues before processing large data - so it wouldn't be trivial to shift the code to use `revDataFromQuery`. – Ben Rubinstein Mar 21 '13 at 12:36
  • What are you doing with the data after revDataFromQuery? As in are you positive you aren't losing data after getting the data out. Certain commands will lose everything after a null in a variable. I know at least filter does that so I'm guessing there might be others. – Monte Goulding Mar 21 '13 at 19:44
0

Hmmm. Depends on where the conversion is taking place. I haven't had to deal with this one, so take this with a grain of salt...

If the whole unicode result is making its way through the odbc driver and LiveCode's db layer then you should be able to unidecode the result. It looks like you're getting back double-byte chars and the second byte is zero, which will null-terminate the string if it's being treated as single-byte chars. This, as you noted, is what's happening when you do the conversion to varchar in the SQL request.

If the conversion is taking place in either the odbc layer or LC's db layer, then you're out of luck until that becomes open source and can be rewritten to handle unicode.

So I would say don't do the conversion to varchar, try to unidecode the result before examining it, and you've got a good chance of seeing what you expect.

mwieder
  • 231
  • 2
  • 6
0

So, for the benefit of anyone coming across this: it turns out that the issue - which I should have figured out if I'd only read the correct bit of the docs - is that I was using revDatabaseColumnNumbered in the simple mode - 

put revDatabaseColumnNumbered(iConnID, iColNum) into tData

... but that mode can only return text, which is not in effect what we have here, but binary data. So the solution is to use the second mode,

get revDatabaseColumnNumbered(iConnID, iColNum, "tData")

... at which point we get the full Unicode data, and can then decide what to do with it. Not a bug with the revQueryDatabase code path at all, just my error in how I used revDatabaseColumnNumbered.

Thanks to everyone who tried to help me!

Ben Rubinstein
  • 121
  • 5