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");
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 list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
