Re: tds_fdw binary column

[Aleš Zelený <zeleny.ales@xxxxxxxxx>]

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@xxxxxxxxxxx> 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@xxxxxxxxxxx