Error with DB400 ODBC from VB App. -- MIDRANGE-L

We are testing a VB application accesing AS/400 DB via ODBC. It's quite a trivial one, so we thought we would not use ADO/OLEDB. But we ran into a problem I will comment. Running V4R5 both in AS/400 and ClientAccess express. Latest cum available applied.

Our files are mainly BPCS files, but since I believe this is most related to how data gets converted from AS/400 to VB, I thought Midrange-L forum is better suited than BPCS-L.

We have a DSN for our access from the PC apps. Using MSAccess to access any of files we are interested in, gives no problems. Also tested getting data from MSWord or MSQuery. They all work fine. The problem comes when accesing from VB:

The query we use is something like : "Select * from B611FP/FSO" We get ODBC error 3146. We look into joblog of QZDASOINIT job servicing our request and shows no errors...

After much modify/try/correct loop, we got to this conclusion:

Some (BPCS) files, e.g. CMF, have fields defined as packed 15/5.

If our select reads "SELECT CFPROD, CFFAC, CFID FROM CMF" it works. If our select reads "SELECT * FROM CMF" it will fail... and... If our select reads "SELECT CFPROD, CFFAC, CFID, CFTLVL FROM CMF" it will also fail...

The only difference is field CFTLVL (and/or CFPLVL, just as well) is packed 15/5...

Same conclussion is drawn testing with other files (IIM, FSO, ...) Asking for "Select * from" any of these files fails. Selecting just a group of different fields, none of them defined as 15/5, it works. Selecting any group of fields, as soon any one of them is 15/5, it fails. For additional investigation, we also tried with files that included fields defined as Packed 13/3, and this always works, no matter how the Select clause is writen. But as mentioned above, MSAccess, MSWord, MSQuery give no problem accessing these files!

Looking into ClientAccess for Windows95 ODBC User's Guide manual, QBKADE00 (sorry, haven't found any other ODBC better source for AS/400 ClientAccess...), Section A.1.5 Data Types, shows a list of all different field types from DB/400 and how they convert to SQL. Nothing seems to be of special concern...

But the we created two Logical files for CMF file above, same fields as in the physical, but the format for fields CFTLVL and CFPLVL (those originally defined as packed 15/5 in the physical) we have redefined as Floating 8/5 (first logical) and as packed 13/3 (second logical). The VB app running "Select * from " any of both new logicals works fine, but still fails if run against the physical...

Well sorry for this lengthy message, but... are we supposed to create logicals over any physical we might be interested in changing fields defined from packed 15/5 to something else ? Is there any such limitation documented somewhere? I seem to remember some format called BigDecimal for Java and/or other purposes, but has it anything to do with our problem?

Creating logicals changing formats for some fields from physicals that have with MANY fields is not an easy job, for as soon as you define the record for the logical, using same record format as the physical, as soon as you add a single field for any reason, e.g. to redefine it, then you have to include ALL fields, one by one, you are interested in... and that's quite a job!

Second question should be, why QZDASOINIT joblog shows no problems but we are getting our VB Query failing?

Any suggestions are welcome. TIA.

--
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxx