Thread: tds_fdw binary column

tds_fdw binary column

From

Aleš Zelený

Date:

09 October 2018, 19:07:03

Hello,

I've an issue with foreign table using tds_fdw from PostgreSQL 10 to Sybase ASE 15.7.

The issue is, that primary key, which I have to use for my predicate is in Sybase data type binary.

Foreign table maps ID column from Sybase binary to PostgreSQL BYTEA.

Simple select is smooth:
[local]:5432 postgres@postgres:7650
=# select branch_id from ase.tds_tbl limit 1;
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
┌────────────────────┐
│ branch_id │
├────────────────────┤
│ \x000246000944242d │
└────────────────────┘
(1 row)

Whereas select using the ID fails:

[local]:5432 postgres@postgres:7650
=# select * from ase. tds_tbl where branch_id = E'\\x000246000944242d'::bytea;
ERROR: DB-Library error: DB #: 102, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

Time: 0.213 ms

Thanks for any hints.

Kind regards Ales Zeleny

Re: tds_fdw binary column

From

Adrian Klaver

Date:

09 October 2018, 22:08:12

On 10/9/18 12:07 PM, Aleš Zelený wrote:
> Hello,
> 
> I've an issue with foreign table using tds_fdw from PostgreSQL 10 to 
> Sybase ASE 15.7.
> 
> The issue is, that primary key, which I have to use for my predicate is 
> in Sybase data type binary.
> 
> Foreign table maps ID column from Sybase binary to PostgreSQL BYTEA.
> 
> Simple select is smooth:
> [local]:5432 postgres@postgres:7650
> =# select branch_id from ase.tds_tbl limit 1;
> NOTICE:  tds_fdw: Query executed correctly
> NOTICE:  tds_fdw: Getting results
> ┌────────────────────┐
> │     branch_id      │
> ├────────────────────┤
> │ \x000246000944242d │
> └────────────────────┘
> (1 row)
> 
> Whereas select using the ID fails:
> 
> [local]:5432 postgres@postgres:7650
> =# select * from ase. tds_tbl where branch_id = 
> E'\\x000246000944242d'::bytea;
> ERROR:  DB-Library error: DB #: 102, DB Msg: General SQL Server error: 
> Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Have you done the above, look at the error log for the server?

> Time: 0.213 ms
> 
> Thanks for any hints.


> Kind regards Ales Zeleny


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: tds_fdw binary column

From

Aleš Zelený

Date:

10 October 2018, 08:31:48

Hello,

my fault, I've forgot to mention that I have only DSN and database user/password credentials with no access to the box with Sybase. trying to reach service vendor support, but it might take some time and I hoped I've done some mistake on my side...

Kind Regards

Ales Zeleny

st 10. 10. 2018 v 0:08 odesílatel Adrian Klaver <adrian.klaver@aklaver.com> napsal:

On 10/9/18 12:07 PM, Aleš Zelený wrote:
> Hello,
>
> I've an issue with foreign table using tds_fdw from PostgreSQL 10 to
> Sybase ASE 15.7.
>
> The issue is, that primary key, which I have to use for my predicate is
> in Sybase data type binary.
>
> Foreign table maps ID column from Sybase binary to PostgreSQL BYTEA.
>
> Simple select is smooth:
> [local]:5432 postgres@postgres:7650
> =# select branch_id from ase.tds_tbl limit 1;
> NOTICE: tds_fdw: Query executed correctly
> NOTICE: tds_fdw: Getting results
> ┌────────────────────┐
> │ branch_id │
> ├────────────────────┤
> │ \x000246000944242d │
> └────────────────────┘
> (1 row)
>
> Whereas select using the ID fails:
>
> [local]:5432 postgres@postgres:7650
> =# select * from ase. tds_tbl where branch_id =
> E'\\x000246000944242d'::bytea;
> ERROR: DB-Library error: DB #: 102, DB Msg: General SQL Server error:
> Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Have you done the above, look at the error log for the server?

> Time: 0.213 ms
>
> Thanks for any hints.

> Kind regards Ales Zeleny

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: tds_fdw binary column

From

Adrian Klaver

Date:

10 October 2018, 13:30:18

On 10/10/18 1:31 AM, Aleš Zelený wrote:
> Hello,
> 
> my fault, I've forgot to mention that I have only DSN and database 
> user/password credentials with no access to the box with Sybase. trying 
> to reach service vendor support,  but it might take some time and I 
> hoped I've done some mistake on my side...

Hmm, some digging found this:

https://github.com/tds-fdw/tds_fdw/issues/88

If your credentials allow it you might try the suggestion in the above 
to see if you can get a more detailed error message.

> 
> Kind Regards
> Ales Zeleny
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: tds_fdw binary column

From

Aleš Zelený

Date:

10 October 2018, 19:20:31

Hello, thanks for the testcase!

First of all, some more environment information:

Foreign server:

[local]:5432 postgres@postgres:13713
=# \des+ ase
                                                                                      List of foreign servers
┌───────────┬──────────┬──────────────────────┬──────┬─────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│   Name    │ Owner   │ Foreign-data wrapper │ Type │ Version │                                           FDW options
├───────────┼──────────┼──────────────────────┼──────┼─────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ ase       │ postgres │ tds_fdw              │      │         │ (servername '<IP ADDR>', port '<PORT NO>', database 'vendor', tds_version '5.0', msg_handler 'notice') │
└───────────┴──────────┴──────────────────────┴──────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

Foreign table:

=# \d ase.tds_tbl
                               Foreign table "ase.tds_tbl"
┌─────────────────────┬────────────────────────┬───────────┬──────────┬─────────┬─────────────┐
│       Column        │          Type          │ Collation │ Nullable │ Default │ FDW options │
├─────────────────────┼────────────────────────┼───────────┼──────────┼─────────┼─────────────┤
│ branch_id           │ bytea                  │           │ not null │         │             │
│ city                │ character varying(60) │           │          │         │             │
│ zip_code            │ character varying(10) │           │          │         │             │
└─────────────────────┴────────────────────────┴───────────┴──────────┴─────────┴─────────────┘
Server: ase
FDW options: (schema_name 'dbo', table_name 'branch')

How Sybase reports that:

use vendor
go

sp_columns branch
go

table_qualifier |table_owner |table_name |column_name         |data_type |type_name |precision |length |scale |radix |nullable |remarks |ss_data_type |colid |column_def |sql_data_type |sql_datetime_sub |char_octet_length |ordinal_position |is_nullable |
----------------|------------|-----------|--------------------|----------|----------|----------|-------|------|------|---------|--------|-------------|------|-----------|--------------|-----------------|------------------|-----------------|------------|
vednor          |dbo         |branch     |branch_id           |-2        |binary    |8         |8      |      |      |0        |        |45           |1     |           |-2            |                 |8                 |1                |NO          |
vednor          |dbo         |branch     |city                |12        |varchar   |60        |60     |      |      |1        |        |39           |3     |           |12            |                 |60                |3                |YES         |
vednor          |dbo         |branch     |zip_code            |12        |varchar   |10        |10     |      |      |1        |        |39           |9     |           |12            |                 |10                |9                |YES         |

Test cases with added msg_handler

test_get_some_id)

Thanks to chosen small table, it fast enough:

=# select branch_id from ase.tds_tbl where branch_id::text = '\x00038500875c3d60';
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
┌────────────────────┐
│ branch_id │
├────────────────────┤
│ \x00038500875c3d60 │
└────────────────────┘
(1 row)

Time: 38.673 ms

We get ID used in later tests:

test_bytea_predicate)

=# select branch_id from ase.tds_tbl where branch_id = E'\\x00038500875c3d60'::bytea;
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 102, Msg state: 181, Msg: Incorrect syntax near 'E'.
, Server: FMI0MA1, Process: , Line: 1, Level: 15
ERROR: DB-Library error: DB #: 102, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
Time: 0.209 ms

Failed as expected after previous tests, but we have new message: Incorrect syntax near 'E'.

Might be some issue with cast handling???

test_bytea_predicate_to_bytea)

[local]:5432 postgres@postgres:13550
=# select branch_id from ase.tds_tbl where branch_id = (select branch_id from ase.tds_tbl where branch_id::text = '\x00038500875c3d60');
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 2715, Msg state: 1, Msg: Can't find type 'bytea'.
, Server: FMI0MA1, Process: , Line: 1, Level: 16
ERROR: DB-Library error: DB #: 2715, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16
Time: 0.249 ms

The error is different, it looks tds_fdw is trying use bytea dat type fro ASE query (guess).

That is what I was able to test.

Kind regards

Ales Zeleny

st 10. 10. 2018 v 15:30 odesílatel Adrian Klaver <adrian.klaver@aklaver.com> napsal:

On 10/10/18 1:31 AM, Aleš Zelený wrote:
> Hello,
>
> my fault, I've forgot to mention that I have only DSN and database
> user/password credentials with no access to the box with Sybase. trying
> to reach service vendor support, but it might take some time and I
> hoped I've done some mistake on my side...

Hmm, some digging found this:

https://github.com/tds-fdw/tds_fdw/issues/88

If your credentials allow it you might try the suggestion in the above
to see if you can get a more detailed error message.

>
> Kind Regards
> Ales Zeleny
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: tds_fdw binary column

From

Adrian Klaver

Date:

11 October 2018, 00:48:30

On 10/10/18 12:20 PM, Aleš Zelený wrote:
> Hello, thanks for the testcase!
> 

> Test cases with added msg_handler
> 
> test_get_some_id)
> Thanks to chosen small table, it fast enough:
> 
> =# select branch_id from ase.tds_tbl where branch_id::text = 
> '\x00038500875c3d60';
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed 
> database context to 'customer'.
> , Server: FMI0MA1, Process: , Line: 0, Level: 0
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed 
> database context to 'vendor'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 0
> NOTICE:  tds_fdw: Query executed correctly
> NOTICE:  tds_fdw: Getting results
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed 
> database context to 'customer'.
> , Server: FMI0MA1, Process: , Line: 0, Level: 0
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed 
> database context to 'vendor'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 0
> ┌────────────────────┐
> │     branch_id      │
> ├────────────────────┤
> │ \x00038500875c3d60 │
> └────────────────────┘
> (1 row)
> 
> Time: 38.673 ms
> 
> We get ID used in later tests:
> 
> test_bytea_predicate)
> =# select branch_id from ase.tds_tbl where branch_id = 
> E'\\x00038500875c3d60'::bytea;
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed 
> database context to 'customer'.
> , Server: FMI0MA1, Process: , Line: 0, Level: 0
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed 
> database context to 'vendor'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 0
> NOTICE:  DB-Library notice: Msg #: 102, Msg state: 181, Msg: Incorrect 
> syntax near 'E'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 15
> ERROR:  DB-Library error: DB #: 102, DB Msg: General SQL Server error: 
> Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
> Time: 0.209 ms
> 
> Failed as expected after previous tests, but we have new message: 
> Incorrect syntax near 'E'.
> Might be some issue with cast handling???

No with the escape syntax, E' is a Postgres extension. See:

https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS


4.1.2.2. String Constants with C-style Escapes


The text cast in your previous example seems to work. Is there an issue 
with using that.

If there is I would file an issue here:

https://github.com/tds-fdw/tds_fdw/issues



> 
> test_bytea_predicate_to_bytea)
> [local]:5432 postgres@postgres:13550
> =# select branch_id from ase.tds_tbl where branch_id = (select branch_id 
> from ase.tds_tbl where branch_id::text = '\x00038500875c3d60');
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed 
> database context to 'customer'.
> , Server: FMI0MA1, Process: , Line: 0, Level: 0
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed 
> database context to 'vendor'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 0
> NOTICE:  tds_fdw: Query executed correctly
> NOTICE:  tds_fdw: Getting results
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed 
> database context to 'customer'.
> , Server: FMI0MA1, Process: , Line: 0, Level: 0
> NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed 
> database context to 'vendor'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 0
> NOTICE:  DB-Library notice: Msg #: 2715, Msg state: 1, Msg: Can't find 
> type 'bytea'.
> , Server: FMI0MA1, Process: , Line: 1, Level: 16
> ERROR:  DB-Library error: DB #: 2715, DB Msg: General SQL Server error: 
> Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16
> Time: 0.249 ms
> 
> The error is different, it looks tds_fdw is trying use bytea dat type 
> fro ASE query (guess).
> 
> That is what I was able to test.
> 
> Kind regards
> Ales Zeleny
> 
> st 10. 10. 2018 v 15:30 odesílatel Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> napsal:
> 
>     On 10/10/18 1:31 AM, Aleš Zelený wrote:
>      > Hello,
>      >
>      > my fault, I've forgot to mention that I have only DSN and database
>      > user/password credentials with no access to the box with Sybase.
>     trying
>      > to reach service vendor support,  but it might take some time and I
>      > hoped I've done some mistake on my side...
> 
>     Hmm, some digging found this:
> 
>     https://github.com/tds-fdw/tds_fdw/issues/88
> 
>     If your credentials allow it you might try the suggestion in the above
>     to see if you can get a more detailed error message.
> 
>      >
>      > Kind Regards
>      > Ales Zeleny
>      >
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com