| content
| - %META:TOPICPARENT{name="VirtTipsAndTricksGuide"}%
---+How can I Load Data into Virtuoso in Transaction Mode (ie auto commit OFF)
---+++ What
Data can be loaded into Virtuoso in transaction mode ie auto commit OFF using the TTLP_MT() and <nowiki>RDF_LOAD_RDFXML_MT()</nowiki> functions available in the latest version 7 release.
---+++ Why
This is needed such that using have control over the final committal of loaded data into the database as part of a transaction (single or distributed), enabling the manual commit or rollback of the data by the user or application logic.
---+++ How
In Virtuoso 7 a new parameter <code>transactional</code> has been added to the TTLP_MT() and <nowiki>RDF_LOAD_RDFXML_MT()</nowiki> functions call to control the transaction mode which is "0" by default ie off and can be turned on by setting it to "1":
<verbatim>
DB.DBA.TTLP_MT (
in strg varchar,
in base varchar,
in graph varchar := null,
in flags integer := 0,
in log_mode integer := 2,
in threads integer := 3,
in transactional int := 0
)
DB.DBA.RDF_LOAD_RDFXML_MT (
in strg varchar,
in base varchar,
in graph varchar,
in log_mode integer := 2,
in threads integer := 3,
in transactional int := 0
)
</verbatim>
---+++ Using the Virtuoso "isql" command line tool
The Virtuoso "isql" command line tool can be use to readily demonstrate this as follows:
1 Setting the "isql" tool into "manual" commit mode with the command:
<verbatim>
SET AUTOCOMMIT MANUAL
</verbatim>
1 Perform the data load with the Virtuoso [[http://docs.openlinksw.com/virtuoso/fn_ttlp_mt.html][TTLP_MT()]] or [[http://docs.openlinksw.com/virtuoso/fn_rdf_load_rdfxml_mt.html][RDF_LOAD_RDFXML_MT()]] functions
1 Commit or Rollback the transaction/work as required with the <code> commit work</code> or <code>rollback work</code> commands
The example "isql" session below demonstrates how this can be done:
<verbatim>
SQL> set AUTOCOMMIT MANUAL;
SQL> sparql drop silent graph <data.nt>;
Done. -- 1 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
0
1 Rows. -- 1 msec.
SQL> DB.DBA.TTLP_MT (file_to_string_output ('./data.nt'), '', 'data.nt', 512, 1, 1, 1);
Done. -- 3 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
100
1 Rows. -- 1 msec.
SQL> rollback work;
Done. -- 0 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
0
1 Rows. -- 1 msec.
SQL> DB.DBA.TTLP_MT (file_to_string_output ('./data.nt'), '', 'data.nt', 512, 1, 1, 1);
Done. -- 3 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
100
1 Rows. -- 0 msec.
SQL> commit work;
Done. -- 1 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
100
1 Rows. -- 0 msec.
SQL> rollback work;
Done. -- 0 msec.
SQL> sparql select count(*) from <data.nt> where {?s ?p ?o};
callret-0
INTEGER
_______________________________________________________________________________
100
1 Rows. -- 0 msec.
SQL>
</verbatim>
---++Using the Virtuoso Jena Provider
The following sample code can compiled and run to demonstrate how transaction behaviour can be controlled in the Virtuoso Jena provider.
1 Java source code:
<verbatim>
$cat Ex1.java
import java.io.*;
import java.sql.*;
public class Ex1 {
public static void main(String[] args)
{
try {
Class.forName("virtuoso.jdbc4.Driver");
String urlDB = "jdbc:virtuoso://mf64:1111/log_enable=1";
Connection conn = DriverManager.getConnection(urlDB,"dba","dba");
Statement st;
st = conn.createStatement();
st.execute("sparql clear graph <testlog>");
conn.setAutoCommit(false);
String data = load_ttl("test.ttl");
if (data!=null) {
insert_data(data, "testlog", conn);
// Commit or Rollback the transaction
// conn.commit();
// System.out.println("Commit insert");
conn.rollback();
System.out.println("Rollback insert");
String query = "sparql SELECT * from <testlog> WHERE {?s ?p ?o}";
ResultSet rs = st.executeQuery(query);
prnRs(rs);
rs.close();
}
st.close();
conn.close();
} catch (Exception e) {
System.out.println("Ex="+e);
}
}
public static String load_ttl(String fname)
{
try {
BufferedReader r = new BufferedReader(new FileReader(fname));
StringBuilder sb = new StringBuilder();
String s;
while((s = r.readLine())!=null)
sb.append(s);
return sb.toString();
} catch (Exception e) {
}
return null;
}
public static void insert_data(String data, String gr_name, Connection conn)
{
try{
PreparedStatement ps = conn.prepareStatement("DB.DBA.TTLP_MT (?, ?, ?, ?, ?, ?,?)");
ps.setString(1, data);
ps.setString(2, "");
ps.setString(3, gr_name);
final int NQUAD_LEVEL = 512;
ps.setInt(4, NQUAD_LEVEL);
ps.setInt(5, 2);
ps.setInt(6, 1);
ps.setInt(7, 1);
System.out.println("Executing insert..");
ps.execute();
ps.close();
} catch (Exception e) {
System.out.println("Ex:"+e);
}
}
public static void prnRs(ResultSet rs)
{
try {
ResultSetMetaData rsmd;
System.out.println(">>>>>>>>");
rsmd = rs.getMetaData();
int cnt = rsmd.getColumnCount();
while(rs.next()) {
Object o;
System.out.print("Thread:"+Thread.currentThread().getId()+" ");
for (int i = 1; i <= cnt; i++) {
o = rs.getObject(i);
if (rs.wasNull())
System.out.print("<NULL> ");
else
System.out.print("["+ o + "] ");
}
System.out.println();
}
} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
System.out.println(">>>>>>>>");
}
public static String create_query(String fname)
{
try {
BufferedReader r = new BufferedReader(new FileReader(fname));
StringBuilder sb = new StringBuilder();
String s;
while((s = r.readLine())!=null)
sb.append(s);
if (sb.length()>0)
return "sparql insert into graph <testyear> { "+ sb.toString() + " } ";
} catch (Exception e) {
}
return null;
}
}
</verbatim>
1 Sample dataset being inserted:
<verbatim>
$ cat test.ttl
<xxx> <P01> "test1" .
<xxx> <P01> "test2" .
<xxx> <P01> "test3" .
<xxx> <P01> "test4" .
<xxx> <P01> "test5" .
</verbatim>
1 Compile and run the sample program, with transaction commit (conn.commit(); in source code) or rollback (conn.rollback(); in source code) to see the transactional behaviour:
<verbatim>
//Commit
$ javac -classpath ".:../lib/junit-4.5.jar:../lib/jena-arq-2.10.1.jar:../lib/jena-iri-0.9.6.jar:../lib/jena-core-2.10.1.jar:../lib/jena-core-2.10.1-tests.jar:../../../libsrc/JDBCDriverType4/virtjdbc4.jar:../virt_jena2.jar:../lib/jcl-over-slf4j-1.6.4.jar:../lib/log4j-1.2.16.jar:../lib/slf4j-api-1.6.4.jar:../lib/slf4j-log4j12-1.6.4.jar:../lib/xercesImpl-2.11.0.jar:../lib/xml-apis-1.4.01.jar" Ex1.java
$ java -classpath ".:../lib/junit-4.5.jar:../lib/jena-arq-2.10.1.jar:../lib/jena-iri-0.9.6.jar:../lib/jena-core-2.10.1.jar:../lib/jena-core-2.10.1-tests.jar:../../../libsrc/JDBCDriverType4/virtjdbc4.jar:../virt_jena2.jar:../lib/jcl-over-slf4j-1.6.4.jar:../lib/log4j-1.2.16.jar:../lib/slf4j-api-1.6.4.jar:../lib/slf4j-log4j12-1.6.4.jar:../lib/xercesImpl-2.11.0.jar:../lib/xml-apis-1.4.01.jar" Ex1
Executing insert..
Commit insert
>>>>>>>>
Thread:1 [xxx] [P01] [test1]
Thread:1 [xxx] [P01] [test2]
Thread:1 [xxx] [P01] [test3]
Thread:1 [xxx] [P01] [test4]
Thread:1 [xxx] [P01] [test5]
>>>>>>>>
// Rollback
$ javac -classpath ".:../lib/junit-4.5.jar:../lib/jena-arq-2.10.1.jar:../lib/jena-iri-0.9.6.jar:../lib/jena-core-2.10.1.jar:../lib/jena-core-2.10.1-tests.jar:../../../libsrc/JDBCDriverType4/virtjdbc4.jar:../virt_jena2.jar:../lib/jcl-over-slf4j-1.6.4.jar:../lib/log4j-1.2.16.jar:../lib/slf4j-api-1.6.4.jar:../lib/slf4j-log4j12-1.6.4.jar:../lib/xercesImpl-2.11.0.jar:../lib/xml-apis-1.4.01.jar" Ex1.java
$ java -classpath ".:../lib/junit-4.5.jar:../lib/jena-arq-2.10.1.jar:../lib/jena-iri-0.9.6.jar:../lib/jena-core-2.10.1.jar:../lib/jena-core-2.10.1-tests.jar:../../../libsrc/JDBCDriverType4/virtjdbc4.jar:../virt_jena2.jar:../lib/jcl-over-slf4j-1.6.4.jar:../lib/log4j-1.2.16.jar:../lib/slf4j-api-1.6.4.jar:../lib/slf4j-log4j12-1.6.4.jar:../lib/xercesImpl-2.11.0.jar:../lib/xml-apis-1.4.01.jar" Ex1
Executing insert..
Rollback insert
>>>>>>>>
>>>>>>>>
$
</verbatim>
---++Related
* [[VirtTipsAndTricksGuide][Virtuoso Tips and Tricks Collection]]
* [[http://docs.openlinksw.com/virtuoso/fn_ttlp_mt.html][TTLP_MT() function]]
* [[http://docs.openlinksw.com/virtuoso/fn_rdf_load_rdfxml_mt.html][RDF_LOAD_RDFXML() function]]
|
![[RDF Data]](/fct/images/sw-rdf-blue.png)
![[cxml]](/fct/images/cxml_doc.png)
![[csv]](/fct/images/csv_doc.png)
![[text]](/fct/images/ntriples_doc.png)
![[turtle]](/fct/images/n3turtle_doc.png)
![[ld+json]](/fct/images/jsonld_doc.png)
![[rdf+json]](/fct/images/json_doc.png)
![[rdf+xml]](/fct/images/xml_doc.png)
![[atom+xml]](/fct/images/atom_doc.png)
![[html]](/fct/images/html_doc.png)