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 ??? :)

asked 15 Nov '11, 15:33

Daz%20Liquid's gravatar image

Daz Liquid
861182338
accept rate: 28%

edited 17 Nov '11, 15:53

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

(15 Nov '11, 17:19) Volker Barth

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'
(15 Nov '11, 18:13) Daz Liquid
Replies hidden

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...

(16 Nov '11, 05:51) Volker Barth

It returns 'Off' for both databases.

(16 Nov '11, 05:53) Daz Liquid

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?

(16 Nov '11, 08:50) Volker Barth
Replies hidden

Exactly the same behaviour :(

(16 Nov '11, 09:34) Daz Liquid
showing 3 of 6 show all flat view

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.

permanent link

answered 18 Jun '12, 14:00

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

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

permanent link

answered 17 Nov '11, 13:21

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

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?

(17 Nov '11, 15:52) Daz Liquid
1

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,

(18 Nov '11, 11:28) Jeff Albion
Replies hidden

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

(18 Nov '11, 13:06) Daz Liquid
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×113
×62
×26
×1

question asked: 15 Nov '11, 15:33

question was seen: 1,354 times

last updated: 18 Jun '12, 14:00