Getting UTF8 data from Oracle, using revdb

[Pierre Sahores]

Hi Ben,

Did't had to deal with Oracle backends for a while now but it should work in the same way PostgreSQL UTF8 configured dbs does. Here is what works under PostgreSQL 8.4 to 9.2 :


The worflow configuration context : LC Editor client app / Web Browser <- internet -> LC server socket lisrener .lc script <--> LC lib stack.livecode <- localhost -> PostgreSQL ;


Each LC server to PostgreSQL connection begins with the following line of code :

revExecuteSQL myDatabaseID,"SET client_encoding TO 'LATIN9';"

before any inteaction (INSERT  /UPDATE / SELECT) follows as below described :


1.- Sending "INSERT / UPDATE" urlencoded data via "POST" to the server :

- Web browsers : UTF8 sets a as the charset (Head metatag of the displayed HTML) ;

1.2.- Insert/Update with LC Editor client app running under windows (ISO charset) : nothing to code on the LC Editor client app side : PostgreSQL automatically converts the ISO received datas to UTF8 before inserting them in the database ;

1.3.- Insert/Update with LC Editor client app running under macos x (Mac Roman charset) : i just apply a MacToISO conversion to the datas to be posted to the server before sending them : PostgreSQL automatically converts the ISO received datas to UTF8 before inserting them in the database ;

1.4.-  Insert/Update with LC Editor client app running under Linux (untested) : should work in the same way Windows does ;


2.- Retrieving "SELECT" data from the server and displaying them on the client app :

2.1.- Web browsers : UTF8 sets as the charset (Head metatag of the displayed HTML) ;

2.2.- LC client app running under windows or android : nothing to do ;

2.3.- LC client app running under mac os x or iOS : IsotoMac conversion of the received data before using them ;

2.4.- LC client app running under Linux (untested) : should work in the same way Windows does ;


In your own context, and as long as no special coding would be needed to have your Windows LC client app against PostgreSQL, i suspect that using :

revExecuteSQL myDatabaseID,"SET client_encoding TO 'LATIN9';"

should do the job. If not testing other client_encoding options would be what to test first.


Best Regards,

Pierre


Le 14 févr. 2014 à 20:35, Ben Rubinstein a écrit :

> I'm having trouble getting some data from an Oracle database using a LiveCode app.
> 
> The application is running on a Windows 2008r2 machine, addressing Oracle Database 11g Release 11.2.0.2.0 - 64bit Production.
> 
> The particular column in the database is apparently in UTF8, according to the Oracle SQL Developer application.  And making the SQL query in that app returns the text as expected.  But making the query through the LiveCode, characters such as the degree symbol, or a right single quote, come through as "?".  I don't think this is happening in my script - I think that the data has been pre-converted before LiveCode gets it.  (Unfortunately I can only access the database by moving a compiled standalone onto the Windows 2008 machine and running it there - I can't operate interactively.)
> 
> Does anyone have any experience of this, including that might confirm that it should be possible?  If this was through ODBC I'd be looking at the DSN properties for character set conversion, but for Oracle it's a direct driver, so I don't know how this would work.
> 
> Is it possible that the Oracle driver is just so old (I can't see any evidence that it's been updated for years) that it somehow connects to the database in a way that causes the database not to offer it UTF8?
> 
> Any suggestions, tips, or personal experience gratefully received.
> 
> Ben
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

--
Pierre Sahores
mobile : 06 03 95 77 70
www.sahores-conseil.com