search

lunarmodules / luasql

LuaSQL is a simple interface from Lua to a DBMS.
http://lunarmodules.github.io/luasql
539 stars 191 forks source link

[ODBC] NCHAR, WCHAR, Unicode Support #36

Open blumf opened 9 years ago

blumf commented 9 years ago

This is a common issue with the LuaSQL.ODBC driver, so I'm folding several tickets into this one.

Problem

The ODBC driver only supports local codepage text, running into Unicode text, such as NCHAR columns, doesn't work.

Fetching uses SQLGetData with SQL_C_CHAR type, Executing uses SQLPrepare rather than SQLPrepareW

So, just mod the code to use the WCHAR types? Well... apart from the overhead of transliterating all the text, that has two issues:

  1. That could easily break existing code that expects local codepage data
  2. WCHAR support is tricky cross platform. ODBC expects UCS-2 form like Windows in general. *nix uses UTF32, but the ODBC API still runs on UCS-2.

    Proposed Solution

  3. Make some transliteration functions to handle UCS-2 <-> UTF8 conversion across all platforms
    • Win32 API has MultiByteToWideChar etc. functions for this
    • *nix system can expect iconv? Is that true for Macs also?
  4. Create new functions utf8_execute and utf8_fetch that support the WCHAR data. Also add local_execute/fetch functions that just link to the existing ones.
  5. Some kind of connection option to flip the default execute and fetch functions.

Comment, etc. appreciated...

beauregardmark commented 9 years ago

I think the solution approach you've suggested makes sense, and I don't have much to add for improvements other than: cur:getcoltypes support WCHAR, WTEXT, and WVARCHAR and possibly apply conversion functions in the application if column types come from these data types.

blumf commented 9 years ago

Check out the 9b6db204f6e403340c9494df090baec3f945acc5 ODBC_Unicode branch

Added utf8_fetch and utf8_execute functions.

Seems to work okay, although I don't have any DBs with NCHAR columns to test against (I mostly rescue data from the horrors of Access)

It's Windows only and needs some more tidying so a connection config can flip the default fetch/execute behaviour.

blumf commented 9 years ago

Uploaded a Lua 5.3 EXE with LuaSQL.ODBC DLL here: https://www.sendspace.com/file/vdgo6i md5sum ef259efe3f884806fd2fbafcce4f9ef1 *lua53-luasql_odbc_test-20150813.zip

beauregardmark commented 9 years ago

I did try the .dll and see it is only designed for lua 5.3. We are still on lua 5.1. Any chance recompiling for Lua 5.1 for testing?

From: blumf [mailto:notifications@github.com] Sent: Thursday, August 13, 2015 8:11 AM To: keplerproject/luasql Cc: Beauregard, Mark Subject: Re: [luasql] [ODBC] NCHAR, WCHAR, Unicode Support (#36)

Uploaded a Lua 5.3 EXE with LuaSQL.ODBC DLL here: https://www.sendspace.com/file/vdgo6i md5sum ef259efe3f884806fd2fbafcce4f9ef1 *lua53-luasql_odbc_test-20150813.zip

— Reply to this email directly or view it on GitHubhttps://github.com/keplerproject/luasql/issues/36#issuecomment-130645105.

blumf commented 9 years ago

Okay, but it's likely that there'll be issues caused by differences in compiler used to build this and whatever you're using. You'd be better off just trying with the Lua EXE packaged in the zips and doing some basic tests there.

https://www.sendspace.com/file/pgya1n md5sum d2611c7f58b916bca54f90f209c22365 *lua51-luasql_odbc_test-20150813.zip