www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - What is the best way to store bitarray (blob) for pasting in database?

reply Suliman <evermind live.ru> writes:
I have next task.
There is PostgreSQL DB. With field like: id, mydata.

mydata - is binary blob. It can be 10MB or even more.

I need load all data from PostgreSQL to SQLLite.

I decided ti create struct that and fill it with data. And then 
do INSERT operation in sqllite.

But I do not know is it's good way, and if it's ok what data type 
should I use for blob (binary data).

	struct MyData
	{
		string  id;
		string  mydata; // what datatype I should use here??
	}	

	MyData [] mydata;

	MyData md;


	while (rs.next())
	{

	 md.id = to!string(rs.getString(1));
	 md.mydata = to!string(rs.getString(2)); //??

	 mydata ~= md;
	}

....

stmtLite.executeUpdate(`insert into MySyncData(id,mydata) 
values(md.id,md.data)`); //ddbc driver
is it's normal way to insert data?
Apr 06 2016
next sibling parent Alex Parrill <initrd.gz gmail.com> writes:
On Wednesday, 6 April 2016 at 12:56:39 UTC, Suliman wrote:
 I have next task.
 There is PostgreSQL DB. With field like: id, mydata.

 mydata - is binary blob. It can be 10MB or even more.

 I need load all data from PostgreSQL to SQLLite.

 I decided ti create struct that and fill it with data. And then 
 do INSERT operation in sqllite.

 But I do not know is it's good way, and if it's ok what data 
 type should I use for blob (binary data).

 	struct MyData
 	{
 		string  id;
 		string  mydata; // what datatype I should use here??
 	}	

 	MyData [] mydata;

 	MyData md;


 	while (rs.next())
 	{

 	 md.id = to!string(rs.getString(1));
 	 md.mydata = to!string(rs.getString(2)); //??

 	 mydata ~= md;
 	}

 ....

 stmtLite.executeUpdate(`insert into MySyncData(id,mydata) 
 values(md.id,md.data)`); //ddbc driver
 is it's normal way to insert data?
Blobs are byte arrays, so they should be ubyte[]. They shouldn't be strings, which are explicitly text only.
Apr 06 2016
prev sibling parent Vadim Lopatin <coolreader.org gmail.com> writes:
On Wednesday, 6 April 2016 at 12:56:39 UTC, Suliman wrote:
 I have next task.
 There is PostgreSQL DB. With field like: id, mydata.

 mydata - is binary blob. It can be 10MB or even more.

 I need load all data from PostgreSQL to SQLLite.

 I decided ti create struct that and fill it with data. And then 
 do INSERT operation in sqllite.

 But I do not know is it's good way, and if it's ok what data 
 type should I use for blob (binary data).

 	struct MyData
 	{
 		string  id;
 		string  mydata; // what datatype I should use here??
 	}	

 	MyData [] mydata;

 	MyData md;


 	while (rs.next())
 	{

 	 md.id = to!string(rs.getString(1));
 	 md.mydata = to!string(rs.getString(2)); //??

 	 mydata ~= md;
 	}

 ....

 stmtLite.executeUpdate(`insert into MySyncData(id,mydata) 
 values(md.id,md.data)`); //ddbc driver
 is it's normal way to insert data?
Use ubyte[] or byte[] for blob, and setUbytes/getUbytes (setBytes/getBytes) to set/get value There was issue in `bytea` type support in PostgreSQL driver. Now it's fixed.
Sep 21 2016