On 03/28/2017 07:32 AM, Beppe Mazzola wrote:
HiI 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 <[email protected]
<mailto:[email protected]>>:
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 <[email protected]
<mailto:[email protected]>>:
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 <[email protected]
<mailto:[email protected]> <mailto:[email protected]
<mailto:[email protected]>>>:
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/virtuoso-users
-- 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 [email protected] https://lists.sourceforge.net/lists/listinfo/virtuoso-users
