store unsigned char array to bytea column of psql table

[Surajkumar]

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]

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]

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]

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());

QByteArray dataArr3(data,data.size());

To copy to clipboard, switch view to plain text mode 

More often you should look into the documentation.

[Surajkumar]

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]

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.

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

To copy to clipboard, switch view to plain text mode 

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');

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');

To copy to clipboard, switch view to plain text mode 

[Surajkumar]

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]

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]

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.