Proxy table padding

[former_SQLA_member1694874]

I'm seeing some strange behaviour when using proxy tables on v10.0.1.4075, or at least I think it's strange, maybe it's perfectly normal and I'm missing something obvious 🙂 When I'm joining from a local table to a proxy table I have to trim my local field to make the join work correctly. Is there something I need to do when creating the proxy server, or is this just as expected ?

If I create a table and add some data to a database

create table db2_prod (db2_product char(10));
insert INTO DB2_PROD VALUES ('FOO       ');

Then create a similar table on another database

create table db1_prod (db1_product char(10));
insert INTO DB1_PROD VALUES ('FOO       ');

And create a proxy connection to the first database

create server DB2 class 'SAODBC' using 'Driver=SQL Anywhere 10;DSN="";ENG=mydb;UID=dba;PWD=sql;commlinks=tcpip{host=LOCALHOST,to=15;Dobroadcast=NONE;port=2639}';
create existing table PRX_DB2_PROD AT 'DB2.mydb.dba.DB2_PROD';

Now this sql returns a result

SELECT * FROM DB1_PROD join prx_db2_prod on trim(db1_product)=db2_product

But this one doesn't

SELECT * FROM DB1_PROD join prx_db2_prod on db1_product=db2_product

---

Update

I can just about cope with the above behaviour by using the trim, however I'm trying to copy data from the proxy table and my copied data is being trimmed as it comes across.

This sql

select * into #bar from prx_db2_prod

Copies db2_product from the proxy table into a local table as

'FOO'

instead of

'FOO       '

---

Update

I was sure this would be unrelated (it seems to be more related to powerbuilder) but while searching I found this.

Newsgroup link

Using the native driver for Sybase System 10 to acess a fixed character field char(12) the query returned just the field content: 8 characters. Accessing the same field using an ODBC Driver the same query returned the field content plus 3 spaces padding, resulting in 12 characters There are some difference on spaces padding when using native or ODBC drivers?

So I created my own ODBC connection and created the proxy server using that instead of 'SAODBC'

 create server DB2 class 'ODBC' using 'mydb'

And now the fields aren't trimmed and everything works, however I don't really want to go around creating my own ODBC connections, help ??? 🙂

[VolkerBarth]

Are these blank-padded databases? (No, I don't know whether this might make a difference...)

[former_SQLA_member1694874]

I don't think so, I have to pad the fields myself. Also this sql won't return any results (as indeed it shouldn't)

Select * from DB2_PROD where db2_product = 'FOO'

[VolkerBarth]

What does

SELECT DB_PROPERTY ('BlankPadding');

reveal for both databases? I would still suspect that there is something extraordinary - At least I have never noticed this behaviour with SA proxy databases...

[former_SQLA_member1694874]

It returns 'Off' for both databases.

[VolkerBarth]

FWIW, the NG link talks about Sybase System 10, i.e. a former ASE version from midst of the 90s IIRC. I wouldn't think this is related to SQL Anywhere 10...

BTW: Does the behaviour vary when using VARCHAR instead of CHAR?

[former_SQLA_member1694874]

Exactly the same behaviour 😞

[jeff_albion · Accepted solution]

This bug has now been fixed in 12.0.1.3748 and above: http://search.sybase.com/kbx/changerequests?bug_id=691040

CR Number: 691040

Fixed Version: 12.0.1.3748

Description: When fetching a string value containing trailing blanks from a proxy table, the trailing blanks would have been stripped. This problem has now been fixed and the string value will be exactly as returned by the underlying driver.

The 12.0.1.3748 EBF has been requested and will be released as soon as its ready to our EBF website.

[Former Member]

Hello,

I believe you have found a bug in the Remote Data Access support. Please open a support case to get the problem resolved.

Thanks, Karim

[former_SQLA_member1694874]

Looks like the bug has been introduced since 3960, I've rolled back to that and everything is working again now. I guess from a few recent posts on here 3960 is one of the more stable v10 releases. I assume you can use this post to register the support case yourself Karim as I don't have a support plan?

[jeff_albion]

Hi Daz,

FYI, bugs can always be freely submitted using our non-priority 'Create Bug Report Case' feature on Case Express: http://case-express.sybase.com/cx/

I have reproduced this bug, and I have submitted this bug on your behalf as technical support case #11706917 and have opened the bug submission as CR #691040. I'll report back here once this has issue has been resolved.

Thank you for your bug report.

Cheers,

[former_SQLA_member1694874]

That's great , thanks for been so helpful Jeff.