PDA

View Full Version : store unsigned char array to bytea column of psql table



Surajkumar
14th June 2015, 16:59
Hello,
I want to store unsigned char array which contains value from 0 to 255 Into a bytea column of psql table through Qt.I have converted unsigned char array into byte array. But I am facing problem in storing.
When I store data manually to the table using following query:
Insert into test values (decode ('137138','hex') ).
The value I want to store is 0x89 and 0x8A.
But when I retrive through Qt into byte array, each value is considered as single byte.

Kindly help for the same.

ChrisW67
14th June 2015, 22:44
You attempt to put three bytes, none of which are 0x89 or 0x8a, into a column and expected 0x89 0x8a. I am confused.

You get a QByteArray out of the bytea column with three bytes in it. Are you. saying that QByteArray::size() returns one?

Surajkumar
15th June 2015, 06:45
Thank you for ur reply.
The problem is i want to store unsigned char buffer data into BYTEA column of psql through qt.
for Example:
unsigned char data[5];
data[0]=254;
data[1]=255;
data[2]=138;
data[3]=129;
data[4]=130;
The data buffer i have to insert into BYTEA column . so i converted data buffer to QByteArray as follows:
QByteArray dataArr3;
dataArr3.resize(5);
for(int i=0;i<5;i++)
{
dataArr3=data[i];
}
and i tried to insert same dataArr3 buffer to BYTEA column but it is not happening.
query13.prepare("INSERT INTO test values (:dt1)");
query13.bindValue(":dt1",dataArr3);
query13.exec();
I have created table as: CREATE TABLE test(data BYTEA).
Then I inserted data manually through psql command prompt:
INSERT INTO test values ('137138');
But data in psql is stored has
byte 0=1
byte 1=3
byte 2=7...
..
..
byte5=8.
But i want 137 as single byte and 138 as another byte and same i want to retrive.

Lesiok
15th June 2015, 08:33
First : in for loop You are setting next byte to dataArr3 not appending them.
Second : You can create data buffer with one line :
QByteArray dataArr3(data,data.size());
More often you should look into the documentation.

Surajkumar
15th June 2015, 10:31
HI,
sorry i have posted wrongly in the below thread it is as follows:
for(int i=0;i<5;i++)
{
dataArr3=data[i];
}

I am assigning dataArr3[i]=data[i]; in the loop.It's working fine.
I think storing data as byte in psql has some trouble.

ChrisW67
15th June 2015, 14:41
In your C++ code you have not bothered to check if the query prepared or executed OK. You have not looked at any error message that might be returned. You have not told us what, if anything, actually made it into the database. Not much we can do with this.



INSERT INTO test values ('137138');
But data in psql is stored has
byte 0=1
byte 1=3
byte 2=7...
..
..
byte5=8.

Postgresql has stored what you asked it to store: the characters '1', '3', '7', '1', '3', '8' (that is bytes with values 49, 51, 55, 49, 51, 56 at a guess)

In your original post you are providing '137138' to decode() and telling it that it was hex, i.e. bytes 0x13, 0x71, and 0x38.

If you want two bytes with decimal value 137 and 138 then give it two bytes with something like these:


INSERT INTO test values (decode('898a', 'hex'));
INSERT INTO test values (decode('\211\212', 'escape'));
INSERT INTO test values (E'\\x898a');
INSERT INTO test values (E'\211\212');

Surajkumar
27th June 2015, 07:22
Hi,
I have inserted the data as said above i.e,
INSERT INTO test (data) values (decode('ff8a', 'hex'));
but when i retrive using the following query:
QSqlQuery qry;
QByteArray res1;
res1.resize(5);
qry.prepare(" select encode(data,'hex') from test");
if( qry.exec())
{
while(qry.next())
{
res1=qry.value(0).toByteArray();
}
}

res1 contains data as follows:
res1[0]='f';
res1[1]='f';
res1[2]='8';
res1[3]='a';

But i need data as byte which is 0xff has one byte and 0x8a as another byte.
I also looked into qt doc's regarding the datatype compatibility between psql and qt.
There is no datatype which holds bytea column data, it has other datatypes like clob,blob,int ,string etc.How can i convert this bytea as unsigned char array data?

In psql i can get the bytes using get_byte() function it gives only a single byte from a binary string.
For me i have data bytes more than 3k. so get_byte() take more time while retriving.

Kindly suggest any other changes that i can make to retrive data from bytea column as byte and store it unsigned char buffer.

Thank's in advance.

Lesiok
27th June 2015, 17:49
From encode PostgreSQL function doc : Encode binary data into a textual representation. If You want to retrieve binary data don't use encode function.

Surajkumar
29th June 2015, 06:16
Hi,
But without using encode function if directly i am getting the data from following query:
QUERY:select data from test
It gives data same as above i.e,
I have stored: \xff898a, 3 bytes.
when i retrieve to byte array it is,
res[0]=f;
res[1]=f;
res[2]=8;
res[3]=9;
res[4]=8;
res[5]=a;

The variable res is ByteArray.