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

3146 is a non-specific error message, but it is a client error.  That, I
think, is why you wouldn't see an error in the QZDASOINIT job.  3146 has
been related to ODBC driver versions or run-time errors in SQL through the
driver.  Can you turn on tracing in ODBC?  3146 means "something bad
happened".  Tracing might give you a more specific message.  Can VB deal
with 15/5 data?  Maybe your version of the ODBC driver can't.

Also if you scan google for ODBC your error you'll get some good suggestions
as well as a strong idea of how many possibilities there are for the error.

-Jim

-----Original Message-----
From: afvaiv [mailto:afvaiv@xxxxxxxxxx]
Sent: Thursday, January 22, 2004 4:50 PM
To: Midrange Systems Technical Discussion
Subject: Error with DB400 ODBC from VB App.


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