How can I insert binary data to Virtuoso RDF storage in plain queries and with parameter binding via ADO.NET calls?
The following example shows different methods for insert binary data to Virtuoso RDF storage in plain queries and with parameter binding via ADO.NET calls.
# Test_Bin.cs
using System;
using System.Runtime.InteropServices;
using System.Text;
using System.Data;
using OpenLink.Data.Virtuoso;
#if ODBC_CLIENT
namespace OpenLink.Data.VirtuosoOdbcClient
#elif CLIENT
namespace OpenLink.Data.VirtuosoClient
#else
namespace OpenLink.Data.VirtuosoTest
#endif
{
class Test_Bin
{
[STAThread]
static void Main(string[] args)
{
IDataReader myread = null;
IDbConnection c;
c = new VirtuosoConnection("HOST=localhost:1111;UID=dba;PWD=dba;");
IDbCommand cmd = c.CreateCommand();
int ros;
try
{
c.Open();
cmd.CommandText = "sparql clear graph <ado.bin>";
cmd.ExecuteNonQuery();
//insert binary as base64Binary
cmd.CommandText = "sparql insert into graph <ado.bin> { <res1> <attr> \"GpM7\"^^<http://www.w3.org/2001/XMLSchema#base64Binary> }";
cmd.ExecuteNonQuery();
//insert binary as hexBinary
cmd.CommandText = "sparql insert into graph <ado.bin> { <res2> <attr> \"0FB7\"^^<http://www.w3.org/2001/XMLSchema#hexBinary> }";
cmd.ExecuteNonQuery();
//prepare for insert with parameter binding
cmd.CommandText = "sparql define output:format '_JAVA_' insert into graph <ado.bin> { `iri($?)` <attr> `bif:__rdf_long_from_batch_params($?,$?,$?)` }";
//bind parameters for insert binary as base64Binary
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = "p1";
param.DbType = DbType.AnsiString;
param.Value = "res3";
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p2";
param.DbType = DbType.Int32;
param.Value = 4;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p3";
param.DbType = DbType.AnsiString;
param.Value = "GpM7";
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p4";
param.DbType = DbType.AnsiString;
param.Value = "http://www.w3.org/2001/XMLSchema#base64Binary";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//bind parameters for insert binary as hexBinary
param = cmd.CreateParameter();
param.ParameterName = "p1";
param.DbType = DbType.AnsiString;
param.Value = "res4";
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p2";
param.DbType = DbType.Int32;
param.Value = 4;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p3";
param.DbType = DbType.AnsiString;
param.Value = "0FB7";
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p4";
param.DbType = DbType.AnsiString;
param.Value = "http://www.w3.org/2001/XMLSchema#hexBinary";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//bind parameters for insert binary as byte[]
param = cmd.CreateParameter();
param.ParameterName = "p1";
param.DbType = DbType.AnsiString;
param.Value = "res5";
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p2";
param.DbType = DbType.Int32;
param.Value = 3;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p3";
param.DbType = DbType.Binary;
byte[] bin_val = {0x01, 0x02, 0x03, 0x04, 0x05};
param.Value = bin_val;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "p4";
param.DbType = DbType.AnsiString;
param.Value = System.DBNull.Value;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//execute select and check the results
cmd.CommandText = "sparql SELECT ?s ?o FROM <ado.bin> WHERE {?s ?p ?o}"; ;
myread = cmd.ExecuteReader();
int r = 0;
while (myread.Read())
{
Console.WriteLine("=== ROW === "+r);
for (int i = 0; i < myread.FieldCount; i++)
{
string s;
if (myread.IsDBNull(i))
Console.Write("N/A|\n");
else
{
object o = myread.GetValue(i);
Type t = myread.GetFieldType(i);
s = myread.GetString(i);
Console.Write(s + "[");
if (o is SqlExtendedString)
{
SqlExtendedString se = (SqlExtendedString)o;
Console.Write("IriType=" + se.IriType + ";StrType=" + se.StrType + ";Value=" + se.ToString());
Console.Write(";ObjectType=" + o.GetType() + "]|\n");
}
else if (o is SqlRdfBox)
{
SqlRdfBox se = (SqlRdfBox)o;
Console.Write("Lang=" + se.StrLang + ";Type=" + se.StrType + ";Value=" + se.Value);
Console.Write(";ObjectType=" + o.GetType() + "]|\n");
object v = se.Value;
if (v is System.Byte[])
{
byte[] vb = (byte[])v;
for (int z = 0; z < vb.Length; z++)
{
Console.WriteLine(""+z+"="+vb[z]);
}
}
}
else
Console.Write(o.GetType() + "]|\n");
}
}
r++;
}
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
finally
{
// if (myread != null)
// myread.Close();
if (c.State == ConnectionState.Open)
c.Close();
}
}
}
}
Output log for example is in the log.txt:
# log.txt === ROW === 0 res1[IriType=IRI;StrType=IRI;Value=res1;ObjectType=OpenLink.Data.Virtuoso.SqlExtendedString]| GpM7[Lang=;Type=http://www.w3.org/2001/XMLSchema#base64Binary;Value=GpM7;ObjectType=OpenLink.Data.Virtuoso.SqlRdfBox]| === ROW === 1 res2[IriType=IRI;StrType=IRI;Value=res2;ObjectType=OpenLink.Data.Virtuoso.SqlExtendedString]| 0FB7[Lang=;Type=http://www.w3.org/2001/XMLSchema#hexBinary;Value=0FB7;ObjectType=OpenLink.Data.Virtuoso.SqlRdfBox]| === ROW === 2 res3[IriType=IRI;StrType=IRI;Value=res3;ObjectType=OpenLink.Data.Virtuoso.SqlExtendedString]| GpM7[Lang=;Type=http://www.w3.org/2001/XMLSchema#base64Binary;Value=GpM7;ObjectType=OpenLink.Data.Virtuoso.SqlRdfBox]| === ROW === 3 res4[IriType=IRI;StrType=IRI;Value=res4;ObjectType=OpenLink.Data.Virtuoso.SqlExtendedString]| 0FB7[Lang=;Type=http://www.w3.org/2001/XMLSchema#hexBinary;Value=0FB7;ObjectType=OpenLink.Data.Virtuoso.SqlRdfBox]| === ROW === 4 res5[IriType=IRI;StrType=IRI;Value=res5;ObjectType=OpenLink.Data.Virtuoso.SqlExtendedString]| 0102030405[System.Byte[]]|