Accessing Unicode (ntext, nvarchar) text fields from SQL Server over ODBC
benr_mc at cogapp.com
Tue Mar 12 15:35:37 EDT 2013
I'm having this problem using LiveCode, but I don't know whether that's where
the issue resides. So this may be OT - but I'd appreciate any suggestions anyway!
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.
So is there a way that a LiveCode app, using the current ODBC drivers, can
retrieve data correctly from an nvarchar or ntext field? Or at least, is
there some simpler solution than apply the correction above to all my queries?
Any suggestions or tips gratefully received.
More information about the Use-livecode