Accessing Unicode (ntext, nvarchar) text fields from SQL Server over ODBC

Ben Rubinstein benr_mc at cogapp.com
Tue Mar 12 14:35:37 CDT 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.

TIA

Ben



More information about the use-livecode mailing list