On 03/28/2017 11:29 PM, Beppe Mazzola wrote:
Hi SergeyThanks for the hint.Without the parameter CHARSET=UTF-8 <http://127.0.0.1:1111/CHARSET=UTF-8/> in the connection string it works! Strange, yesterday night without parameter it didn't work...I'm now using the tool SqlWorkbench/J to perform the upload of the data from a .csv file into Virtuoso RDB. I try first with a single table / single row. With the following command I have the file uploaded, WbImport -type=text -file='C:\LocalPrograms\dev\virtuoso\scripts\pesticidesRdfGen\csvFiles\V_SEM_MRL_HST_FTN-1line.csv' -delimiter=, -table=DB.PEST.V_SEM_MRL_HST_FTN -quoteChar="; but again the encoding is not correct. Immagine incorporata 1 If I add the parameter -encoding=UTF-8 in the command the tool does not encode correctly the text yet, despite the stored text is different. The file content is the following. FOOTNOTES_ID,MRL_HST_ID,LANGUAGE_CD,FOOTNOTE_TEXT 1092,233534,"BG","Спиротетрамат и неговият метаболит BYI08330-енол, изразен като спиротетрамат" I see there are parameters to change the encoding for the SQL script, for the XML file, for the text file. I am not sure which one I should change, and where.
[Sergey] Yes, I see an issue in the ParametersMetaData, I am preparing fix for this issue. It will be ready tomorrow.
Thanks Beppe 2017-03-28 10:35 GMT+01:00 Sergey Malinin <sergmali...@gmail.com <mailto:sergmali...@gmail.com>>: On 03/28/2017 07:32 AM, Beppe Mazzola wrote:Hi I want to use the JDBC driver in order to load data into my Virtuoso relational DB. As first thing I would like to see the driver working fine inserting in the DB non-Latin characters. So I have used this Java code import java.sql.Statement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class BulkLoader { public static Connection getConnection() throws SQLException { try { Class.forName("virtuoso.jdbc4.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } Connection conn = DriverManager.getConnection("jdbc:virtuoso://127.0.0.1:1111/CHARSET=UTF-8/ <http://127.0.0.1:1111/CHARSET=UTF-8/>", "dba", "dba");[Sergey] Connection string must be without /CHARSET=UTF-8 , because the option /CHARSET=UTF-8 must be used only, if you execute SPARQL queries and it must NOT be used for SQL Connection conn = DriverManager.getConnection("jdbc:virtuoso://127.0.0.1:1111/ <http://127.0.0.1:1111/CHARSET=UTF-8/>", "dba", "dba");return conn; } public static void main(String[] args) { Connection conn; try { conn = getConnection(); String sql = "INSERT INTO R2RML.TEST.PRODUCT (id,name) VALUES (1,N'Спиротетрамат и неговият метаболит')"; Statement st = conn.createStatement(); int res = st.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } } } to insert some text in a table with the following structure create table R2RML.TEST.PRODUCT ( id INTEGER, name NVARCHAR(100), PRIMARY KEY (id) ); and I get the following result SELECT * from R2RML.TEST.PR <http://R2RML.TEST.PR>ODUCT; Immagine incorporata 1 The text is still inserted incorrectly in the table. I have removed the SQL_UTF8_EXECS setting from virtuoso.ini ;SQL_UTF8_EXECS = 1 and used the Virtuoso driver virtjdbc4.jar I have tried to prepare the statement before executing it, but nothing changes. Does anyone see something wrong or missing? Thanks! Cheers Beppe 2017-03-23 17:05 GMT+00:00 Beppe Mazzola <beppemazz...@gmail.com <mailto:beppemazz...@gmail.com>>: Hi Sergey Thank you again for your feedback. I have noticed now that even the Virtuoso stored procedures DB.DBA.csv_register() and DB.DBA.csv_loader_run() are not compatible with unicode text. So it seems that my ETL module related to the data loading has to be reimplemented using the drivers you suggested, restoring the NVARCHAR columns in the RDB. Thanks again Cheers Beppe 2017-03-23 15:07 GMT+00:00 Sergey Malinin <sergmali...@gmail.com <mailto:sergmali...@gmail.com>>: On 03/23/2017 12:45 AM, Beppe Mazzola wrote: Thanks Sergey Your suggestion inspired me to do some more trials. [...] The data in my project are loaded with an ETL process implemented using the Virtuoso bulk load feature (DB.DBA.csv_register() and DB.DBA.csv_loader_run() procedures). Can I still I use the ODBC / JDBC drivers to load my data into the RDB as somebody suggested? [Sergey] It is a bad idea to store UTF8 data in VARCHAR() column, it isn't portable and you will need decode UTF-8 data by hand, if you get data from table via JDBC/ODBC drivers. 1) I think, that "SQL_UTF8_EXECS = 1" options will be usefull only, if you want to create tables/columns with Non-Latin names like for example create table "таблица1" (... 2) If you need to store Unicode data in table, you must to use datatypes: NCHAR, NVARCHAR, LONG NVARCHAR, NCLOB !!! 3) The JDBC/ODBC drivers inserts Unicode data properly to datatypes above. For ODBC programs the ODBC procedures like SQLExecDirectW / SQLPrepareW must be used for properly send Unicode data to server. 4) If you want insert Unicode data to table or to use Unicode value in WHERE, the N' prefix must be used. Example to insert: insert into table test values(1, N'АБВГД'); 5) I write already, that there is a bug in the Virtuoso Conductor UI, so it send Unicode data to server via wrong way. NOW Conductor UI works properly only for select unicode data from tables. So try to use any tools that works via ODBC/JDBC/ADO.NET <http://ado.net/> Virtuoso drivers, it will works properly and you will not have problems with use unicode data. For example for JDBC => http://www.sql-workbench.net/downloads.html <http://www.sql-workbench.net/downloads.html> Cheers Beppe 2017-03-21 11:20 GMT+00:00 Sergey Malinin <sergmali...@gmail.com <mailto:sergmali...@gmail.com> <mailto:sergmali...@gmail.com <mailto:sergmali...@gmail.com>>>: On 03/21/2017 04:42 AM, Beppe Mazzola wrote: Related to the problem I have about storing non-Latin character in Virtuoso RDB, I see now that I haven't provided all the info related to it. So it was not evident that the Hungarian text written in the DB was not the same read from it immediately after, despite the Virtuoso UTF-8 set-up has been performed I bring here again the test case (now simplified and completed) to reproduce the problem. It has been performed with Conductor Interactive SQL. It looks, that Conductor Interactive SQL does something wrong, when SQL insert(and etc) queries are executed, it inserts wrong data to table. But Conductor Interactive SQL shows unicode data properly, if it was inserted via ODBC/JDBC/ADO.NET <http://ado.net/> <http://ADO.NET <http://ado.net/>> . Also note: 1) you don't need to set "SQL_UTF8_EXECS = 1" in virtuoso.ini http://docs.openlinksw.com/virtuoso/wideidentifiers/ <http://docs.openlinksw.com/virtuoso/wideidentifiers/> <http://docs.openlinksw.com/virtuoso/wideidentifiers/ <http://docs.openlinksw.com/virtuoso/wideidentifiers/>> for simple insert unicode data to columns. 2) for insert unicode values, the prefix N must be used insert into test values(1, N'АБВГ') 3) try insert unicode data via ODBC/JDBC/ADO.NET <http://ado.net/> <http://ADO.NET <http://ado.net/>> drivers, it must be inserted properly. ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org!http://sdm.link/slashdot _______________________________________________ Virtuoso-users mailing list Virtuoso-users@lists.sourceforge.net <mailto:Virtuoso-users@lists.sourceforge.net> https://lists.sourceforge.net/lists/listinfo/virtuoso-users <https://lists.sourceforge.net/lists/listinfo/virtuoso-users>-- Best Regards,Sergey Malinin
-- Best Regards, Sergey Malinin
smime.p7s
Description: S/MIME Cryptographic Signature
------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________ Virtuoso-users mailing list Virtuoso-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/virtuoso-users
