Re: Error with DB400 ODBC from VB App.

One thing I noticed is your table reference on one of your examples.
"Select  *  from B611FP/FSO"

If FSO is a table inside your B611FP library, then you need to reference it
with a dot anmd not a slash.
Select  *  from B611FP.FSO

As for the data, since you are using ODBC, did you make sure to tell it to
translate CCSID?
----- Original Message ----- 
From: "afvaiv" <afvaiv@xxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, January 22, 2004 5:50 PM
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
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.