Hi Sergey
Thanks 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.
[image: 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.
Thanks
Beppe
2017-03-28 10:35 GMT+01:00 Sergey Malinin <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/", "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.PRODUCT;
>
> [image: 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>:
>
>> 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>:
>>
>>> 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
>>>
>>>
>>>> Cheers
>>>>
>>>> Beppe
>>>>
>>>>
>>>> 2017-03-21 11:20 GMT+00:00 Sergey Malinin <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/>
>>>> 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
> listVirtuoso-users@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/virtuoso-users
>
>
>
> --
> Best Regards,
> Sergey Malinin
>
>
------------------------------------------------------------------------------
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
