PostgreSQL database convert printable characters to octets with bytea column on linux platform

Question

here is the scenario:

CREATE TABLE amaaa
(
  laaaid integer NOT NULL DEFAULT 0,
  blobdata bytea
)
INSERT INTO amaaa(laaaid)VALUES (1);
SQLRETURN connect()
{
  SQLRETURN ret; /* ODBC API return status */

  /* Allocate an environment handle */
  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myestenv);

  /* We want ODBC 3 support */
  if (!ret) ret = SQLSetEnvAttr(myestenv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

  /* Allocate a connection handle */
  if (!ret) ret = SQLAllocHandle(SQL_HANDLE_DBC, myestenv, &dbc);

  SQLSetConnectOption(dbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON);
  const char* connectStr = "ByteaAsLongVarBinary=1;LFConversion=0;DSN=AMPG961;UID=postgres;PWD={postgres}";
  if (!ret) ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)connectStr, strlen(connectStr), NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

  /* Allocate a statement handle */
  if (!ret) ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

  if (ret) getErr(ret);

  return ret;
}

void disconnect()
{
  /* Disconnect and free all the handles */
  SQLRETURN ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt);
  ret = SQLDisconnect(dbc);
  ret = SQLFreeHandle(SQL_HANDLE_DBC, dbc);
  ret = SQLFreeHandle(SQL_HANDLE_ENV, myestenv);
}

const USHORT LEN = 3;
SQLRETURN update()
{
  SQLRETURN ret = connect();
  if (ret) return ret;

  const unsigned char vLob[LEN] = "v=";
  long lBlobLen = LEN - 1;
  int lMainRecordId = 1;

  SQLLEN cbValue = lBlobLen;

  ret = SQLPrepare(stmt, (SQLCHAR*)"UPDATE \"amaaa\" set blobdata = ? where laaaid = ?;", SQL_NTS);

  if (!ret) ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT,
      SQL_BINARY, SQL_LONGVARBINARY, 0,
    0, (void*)vLob, lBlobLen, &cbValue);

  SQLLEN cbLen = 4;

  ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT,
                      SQL_C_LONG, SQL_INTEGER, 0,
                      0, (void*)&lMainRecordId, sizeof(long), &cbLen);

  if (!ret)  ret = SQLExecute(stmt);
  if ((ret != SQL_SUCCESS) && (ret != SQL_NEED_DATA) && (ret != SQL_SUCCESS_WITH_INFO)) {
    printf("SQLExecDirect Failed\n\n");
    getErr(ret);
    disconnect();
  }
  return ret;
}

when i query blobdata column from database, the value is always 'v\075',actually i the value which i input is 'v='; but the same code has no problem on windows platform. any idea about it?

thanks in advance.

The question is unclear. Nothing is ever inserted into the table, so it must be empty. There should be an `odbc` tag with this query, abd `c` rather than `c++`. — Laurenz Albe, Nov 15 '16 at 11:48

score 0 · Answer 1 · answered Nov 16 '16 at 13:31

It looks like everything is fine!

The bytes you stored in the database are the ASCII values for v=, which are 118 and 61, or hexadecimal 0x76 and 0x3D, or octal 0166 and 075.

Exactly these two bytes get stored in the bytea column!

The apparent problem is with the tool you use to query the database.
It seems to display all bytes that correspond to ASCII characters as the corresponding characters and all other bytes as three-digit octal numbers (preceeded by a backslash).
That's why the value appears as v\075 to you.

score 0 · Answer 2 · answered Nov 17 '16 at 04:03

0

The problem can be reproduced on CentOS 7.2 which use psqlODBC 9.03 that is newest in the CentOS RPM.

the problem is fixed by upgrade psqlODBC version from 9.03 to 9.05.

the psqlODBC 9.05 is compiled by myself on CentOS.

hope helpful for others.

answered Nov 17 '16 at 04:03

Ramin

43
1
4

PostgreSQL database convert printable characters to octets with bytea column on linux platform

2 Answers2