Re: tds_fdw binary column - Mailing list pgsql-general

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