How to save QByteArray in postgresql

Mikeeeeee

Hi!
Created an oid column in postgresql for storing QByteArray.
I read that oid is an analog of BLOOD, the BLOB itself is not in my database.
But with this type of data I can't write QByteArray, I get this error:

However, only this query works with the oid (only a number is written to this data type):

INSERT INTO public.categories(
    name, image)
    VALUES ( 'hh', 100);

Please tell me how to write QByteArray ? To some other data type?

SGaist

Hi,

You should provide:

the code that you are using to create the query
the table schema
the version of Qt you are using
the version of PostgreSQL you are running
the OS you are running

Mikeeeeee

Postgre 12
Qt 5.14
windows 7 64 bit
Table:

-- Table: public.categories

-- DROP TABLE public.categories;

CREATE TABLE public.categories
(
    id integer NOT NULL DEFAULT nextval('categories_id_seq'::regclass),
    name text COLLATE pg_catalog."default",
    image oid,
    CONSTRAINT categories_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public.categories
    OWNER to postgres;

query:

void DataBase::addCategories(QString name, QByteArray image)
{
    QSqlQuery query;
    query.prepare("INSERT INTO public.categories ("
                  "name, "
                  "image "
                  ") VALUES ( "
                  ":name, "
                  ":image "
                  " ) ;");

        query.bindValue(":name", name);
        query.bindValue(":image", image);

        if (!query.exec()) {
            /*qDebug()<<"not write";*/
            QTextStream out(stdout);
            out<<"not write \r\n";
            out<<query.lastError().text()<<" \r\n";
        }
        else {
            /*qDebug()<<"write";*/
            QTextStream out(stdout);
            out<<"write \r\n";
        }
}

QByteArray image:

    QByteArray bArray;
    QBuffer buffer(&bArray);
    buffer.open(QIODevice::WriteOnly);
    myImage.save(&buffer, "BMP");
    QString imageString("data:image/bmp;base64,");
    imageString.append(QString::fromLatin1(bArray.toBase64().data()));
   QByteArray image;
   image += imageString;

SGaist

Why are you doing all these QString conversions ? You are working with QByteArray, QString is not the same. It stores data as UTF-16. There's really no need for that.

Mikeeeeee

I write the string in json and pass it to the server, and the server writes it to the database. But how do I write Qbytearray to postgresql?

Christian Ehrlicher

@Mikeeeeee said in How to save QByteArray in postgresql:

But how do I write Qbytearray to postgresql?

By simply passing the QByteArray to bindValue() and using a proper datatype. If you want a blob, use a bytea. If it should be a json string to use by postgresql, use json or jsonb (but it must be a QString then iirc)