Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1058
  • Last Modified:

How do I translate binary date from an as400?

I am programming in VB.net.  I make a connection to an as400 db.  My select statement should return all rows of a particular table.  in the table are two binary fields and a number of other text fields.  However, my dataset does not return the binary fields.  I test the data by opening excel and importing the table.  I have the ODBC connection under Translation - enable "Convert binary data (CCSID 65535) to text".  Otherwise the two binary fields do not show good data.  If I need to include further code I will do so.  I have queried other data from as400 dbs, but not encountered binary data yet.  I have thought about linking the table from my sql server.  Not sure if that will fix my binary conversion problem or not, however I haven't done much of that yet and have not found enough help to get that to work.  Any ideas Experts?  I am giving this question 1000 pts because it is urgent and I am not sure of the difficulty of the question.

My connection string:      
mConnOLE.ConnectionString = "PROVIDER=IBMDA400;DATA SOURCE=mhas400;USER ID=u198711;PASSWORD=Malibu91;DEFAULT COLLECTION=BAXPRDDTA;"

My Select startment:
select  * from BAXPRDDTA.F30008
 
sample data: (Colums IWMCU and IWLEDG are in binary format)
IWMCU      IWLEDG      IWSTLF      IWXSDL
  140      A1      2225      2225
  140      A2      2414      2414
  140      A3      2448      2448
0
williak7
Asked:
williak7
  • 4
  • 3
  • 2
  • +1
1 Solution
 
_b_hCommented:
Is the column defined as binary? If you use Display File FIeld Description DSPFFD on the file, what attributes show for the IWMCU and IWLEDG fields?

If they are defined are binary, I think you need to create an SQL view that redefines them as integers that you can then work with.

Barry
0
 
williak7Author Commented:
yes, in the as400 the fields are binary.  When I connect to the as400 and bring in the dataset through vb.net the two fields do not show.  If I use MS Access the two fields show, but they contain odd characters.

I am not sure how to link the SQL server to the AS400.  I have SQL 2000 console and 2005 Studio Express for SQL loaded if you could explain I would appreciate.  The as400 is called 'mhas400' and the table is called 'BAXPRDDTA'.

thanks
0
 
_b_hCommented:
Your file was likely created using DDS and you need to create an SQL view.

Check out this link:
http://www-912.ibm.com/s_dir/slkbase.nsf/1ac66549a21402188625680b0002037e/c7b7e2f41508dbf286256b500065597c?OpenDocument

Post back if you have questions.
Barry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tliottaCommented:
williak7:

When you say that the fields are defined as "binary", I assume you mean that those fields have a CCSID of 65535. If that's true and they actually contain textual data, then the fundamental problem is that those fields aren't defined correctly. They should _not_ be tagged as 65535.

Practically speaking, that tells DB2 that those fields should _not_ be translated when moved from system to system. The bit patterns are supposed to be maintained rather than being converted between EBCDIC and ASCII or between various language code page/character sets. The data is graphic characters or some other stuff that only some application is supposed to mess with.

But you're saying that these fields are really supposed to contain text, not fixed bit patterns. These fields are supposed to be translated. In your case, when downloaded to VB.net, the text values should be converted to ASCII (Unicode or whatever).

The problem for DB2 is that it has no way to know what translation to perform. It knows what CCSID the target is, but no one has ever told it what the source CCSID was when the data was put into those fields. DB2 was explicitly told to ignore it.

AFAIK, the real fix is something like this in SQL on your iSeries:

 ==>  ALTER TABLE  BAXPRDDTA.F30008 +
             ALTER COLUMN IWLEDG +
             SET DATA TYPE CHARACTER (2) +
             CCSID 37 +
             NOT NULL WITH DEFAULT

I guessed on both length and proper CCSID.

That would re-tag the data in that column with CCSID 37 (normal U.S. English/EBCDIC). After that, there is no longer any "convert 65535" issue. Conversions should be automatic when accessed by VB.net or anything else.

Tom
0
 
Dushan De SilvaTechnology ArchitectCommented:
You can embed your SQL statement into RPG program (SQLLE). Then you can test it.

http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21791137.html


BR Dushan
0
 
tliottaCommented:
It _might_ be possible to do something like:

  select  cast( IWMCU as CHAR CCSID nnnnn,
            cast( IWLEDG as CHAR CCSID nnnnn),
            IWSTLF, IWXSDL
     from BAXPRDDTA.F30008

...but I don't know what the CCSID nnnnn value would be nor how a CAST() is specified in VB.net. I would use CCSID 37, but that's clearly more appropriate on the AS/400 itself. Elsewhere, 1252 might be appropriate or 819 or... ?

Tom
0
 
tliottaCommented:
Hmmm... on further thought, the CAST() might not make sense as I presented it. I'm far too unfamiliar with access inside of VB.net. After thinking, it'd make more sense to CAST() first to CCSID 37 or whatever the default job CCSID is on that AS/400. Once that's done, it might be be re-CAST() into something more useful. Pure guesswork, but it might avoid any database changes.

I don't know what an equivalent function there is to CAST(...CCSID...) in VB.net, but there must be one.

The inner CAST() would inform SQL how to interpret the bit patterns as the come from the database. An outer CAST() would do any conversion to whatever presentation is needed.

Might work, might not. But maybe the concept can be taken by someone else to find resolution.

Tom
0
 
williak7Author Commented:
I cannot make any changes to the db.  Our as400 group would not let that happen.   I am not familiar with as400 programming also not sure of the SQL commands .  I  will try the cast statement and see what I can come up with.

thanks for the input.
0
 
williak7Author Commented:
The cast statement worked for the numeric.  I used: select cast(IWMCU as numeric) from ....

However, I have tried a number of different cast statements for the IWLEDG mixed character field without any luck.  

thanks for your help.  I will close this one and see if I can get the other cast statement to work.
0
 
tliottaCommented:
Too bad you can't convince them to change the DB. It sure seems like it's incorrect and that will cause potential problems for _any_ remote client access. (But I can't actually see the DB, so...?)

Tom
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now