Re: tds_fdw binary column

Hello, thanks for the testcase!

First of all, some more environment information:

Foreign server:

[local]:5432 postgres(at)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(at)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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>