Re: tds_fdw binary column

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(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 <mailto: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com