Using persisted recordset

[R. Choate]

I finally was able to save my recordset from AS400 to ADTG file (records.dat). There were 6 fields, one of which is numeric, and
that numeric field was a roadblock to saving the recordset. Somebody came up with digits(mynumericfield) as a solution, and it
worked. Now, I can see that records that have values of less than 5 digits (in my problem field), have leading zeros. For instance,
CUST = 945, is now CUST = 00945. I believe the table definition stated that my numeric field should be 5 digits. Anyway, now when I
try to re-open my new recordset and find a specific record, using the FIND method, I can't get FIND to work unless I use leading
zeros in my comparison. The field has values that vary from 3 digits to 5 digits. I need to be able to find the correct record,
where my CUST# = 1066 and my CHG field = "DF". I have tested the code below, successfully, by adding a leading zero to 1066 and
setting my FIND for only that string(ignoring CHG). CHG is a 2-character string field. Here is my current code, which fails at the
rst.Find line:

Sub CheckCust()
Dim X As String
Dim F As String
Dim J As String
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

rst.Open "H:\Subdirectory\myrecordset.dat", , adOpenStatic, adLockBatchOptimistic

X = "1066"

F = "DF"

rst.Find ("CUST = " & X & " And Chg = " & F)

J = rst!DESC

rst.Close
Set rst = Nothing

End Sub

 

[Brian Camire]

You might try enclosing in single quotes the literal value you're comparing
Chg against in the Find call, as in:

rst.Find ("CUST = " & X & " And Chg = '" & F & "'")

I finally was able to save my recordset from AS400 to ADTG file

(records.dat). There were 6 fields, one of which is numeric, and

that numeric field was a roadblock to saving the recordset. Somebody came

up with digits(mynumericfield) as a solution, and it

worked. Now, I can see that records that have values of less than 5 digits

(in my problem field), have leading zeros. For instance,

CUST = 945, is now CUST = 00945. I believe the table definition stated

that my numeric field should be 5 digits. Anyway, now when I

try to re-open my new recordset and find a specific record, using the FIND

method, I can't get FIND to work unless I use leading

zeros in my comparison. The field has values that vary from 3 digits to 5

digits. I need to be able to find the correct record,

where my CUST# = 1066 and my CHG field = "DF". I have tested the code

below, successfully, by adding a leading zero to 1066 and

setting my FIND for only that string(ignoring CHG). CHG is a 2-character

string field. Here is my current code, which fails at the

 

[Ron Weiner]

If rst.Find ("CUST = " & X & " And Chg = " & F) works

Try rst.Find ("CUST = " & Right("00000" & X, 5) & " And Chg = " & F)

This will insure that X is alwaus a 5 character string with leading zeros.
I am surprised the at the find works though. I would hav thought that you
would need to surround the valuse with single quotes.

Like rst.Find ("CUST = '" & Right("00000" & X, 5) & "' And Chg = '" & F
& "'")

Ron W

I finally was able to save my recordset from AS400 to ADTG file

(records.dat). There were 6 fields, one of which is numeric, and

that numeric field was a roadblock to saving the recordset. Somebody came

up with digits(mynumericfield) as a solution, and it

worked. Now, I can see that records that have values of less than 5 digits

(in my problem field), have leading zeros. For instance,

CUST = 945, is now CUST = 00945. I believe the table definition stated

that my numeric field should be 5 digits. Anyway, now when I

try to re-open my new recordset and find a specific record, using the FIND

method, I can't get FIND to work unless I use leading

zeros in my comparison. The field has values that vary from 3 digits to 5

digits. I need to be able to find the correct record,

where my CUST# = 1066 and my CHG field = "DF". I have tested the code

below, successfully, by adding a leading zero to 1066 and

setting my FIND for only that string(ignoring CHG). CHG is a 2-character

string field. Here is my current code, which fails at the

 

[Stephen Howe]

I finally was able to save my recordset from AS400 to ADTG file

(records.dat). There were 6 fields, one of which is numeric, and

that numeric field was a roadblock to saving the recordset. Somebody came

up with digits(mynumericfield) as a solution, and it

worked.

Yes, but the likely reason it "worked" is that digits() converted the
numeric field to a textual numeric field. So the field in the recordset is
now just text. Not numeric at all.

Personally I would not be happy with that.
I would want a proper numeric field not a textual field.
So the solution that he has come up with is okay but it has its limitations.
I would be looking for a better solution.

Now, I can see that records that have values of less than 5 digits (in my problem
field), have leading zeros. For instance,
CUST = 945, is now CUST = 00945. I believe the table definition stated

that my numeric field should be 5 digits. Anyway, now when I

try to re-open my new recordset and find a specific record, using the FIND

method, I can't get FIND to work unless I use leading

zeros in my comparison.

Right. Because you are not comparing numerically but as strings. Naturally
"345" is not the same as "00345" as a string that is. So FIND won't work
without leading zeros. And you might find problems with SORT if the textual
field has a decimal point in it.

I find your experience with the AS400 provider strange. I find it difficult
to believe that is is so non-standard that it does not understand ISO SQL92,
a previous SQL standard.

In your case, I would make some small experiments with the AS400 provider.
In particular, you should be able to do

"SELECT field1 FROM table1"

for a recordset and you should be able to debug print the type of the field.
If I was you, I would interested to find out what AS400 data types map to
what VB data types.

I would also see if the AS400 provider understands the SQL I mentioned.

I would also say you are trying to run before you can even walk. Until you
understand what the AS400 provider can or cannot do for you (by running
simple experiments, reading the documentation) and what its limitations are
(IBM documentation on it and the database it retrieves from), it is
pointless bombarding the newsgroup with more complex tasks. You seem to be
unwilling to do that.

Microsoft has some ADO example code that illustrates various methods and
properties of Connection,Recordset and Command objects. It should be
possible to get the same examples working on AS400 with very little
modification.

Stephen Howe

 

[R. Choate]

Look, I appreciate helpful suggestions from anyone. Lets not get into what I'm willing to do and accuse me "bombarding the
newsgroups". I do a LOT of research, including reading electronic reams of technical information from IBM about AS400, and from KB
articles, prior NG postings, tech websites, and lots of stuff you wouldn't imagine. Just because you believe my code sucks or
doesn't meet your standards, doesn't mean that I don't know what I'm doing, or that I'm running before I can crawl. I am a
professional consultant, and I am a CPA, and I generally do know what the hell I'm doing. I am not an AS400 programming expert, nor
do I care to be. I have one project that called for getting certain data from an AS400, which is every bit as difficult and onerous
as I claimed it is. If you don't believe me, you should spend some time with one. I tried tons of solutions before I started my
posts, and nobody had any complaints. If you would knock it off with the condescending attitude and just follow the posts, you might
learn something that you may use someday, even if you don't approve of my "sloppy" SQL. By the way, because of the AS400 data
provider, you could not have written a better SQL that would have returned the same data from AS400, and I guarantee it.

Finally, the only thing I did wrong with my "FIND" code, was I failed to realize that ADO doesn't support "FIND" with multiple
criteria. I had to switch to using "filter". Just a slight modification of my code. By the way, I didn't see you posting that
helpful bit of information. I thought you were the expert? Now, my code works perfectly, and I won't bother you with my
"bombardment" on this topic anymore. AS400 is an interesting topic because so many people use it. Now, I am in a position to help
other programmers on several AS400 and ADO related issues, which will probably save somebody a hell of a lot of time.

If, somehow, you did not intend to be as condescending as you sounded in your post, then I will apologize for reading it
incorrectly.
--
RMC,CPA


"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message

I finally was able to save my recordset from AS400 to ADTG file

(records.dat). There were 6 fields, one of which is numeric, and

that numeric field was a roadblock to saving the recordset. Somebody came

up with digits(mynumericfield) as a solution, and it

worked.

Yes, but the likely reason it "worked" is that digits() converted the
numeric field to a textual numeric field. So the field in the recordset is
now just text. Not numeric at all.

Personally I would not be happy with that.
I would want a proper numeric field not a textual field.
So the solution that he has come up with is okay but it has its limitations.
I would be looking for a better solution.

Now, I can see that records that have values of less than 5 digits (in my problem
field), have leading zeros. For instance,
CUST = 945, is now CUST = 00945. I believe the table definition stated

that my numeric field should be 5 digits. Anyway, now when I

try to re-open my new recordset and find a specific record, using the FIND

method, I can't get FIND to work unless I use leading

zeros in my comparison.

Right. Because you are not comparing numerically but as strings. Naturally
"345" is not the same as "00345" as a string that is. So FIND won't work
without leading zeros. And you might find problems with SORT if the textual
field has a decimal point in it.

I find your experience with the AS400 provider strange. I find it difficult
to believe that is is so non-standard that it does not understand ISO SQL92,
a previous SQL standard.

In your case, I would make some small experiments with the AS400 provider.
In particular, you should be able to do

"SELECT field1 FROM table1"

for a recordset and you should be able to debug print the type of the field.
If I was you, I would interested to find out what AS400 data types map to
what VB data types.

I would also see if the AS400 provider understands the SQL I mentioned.

I would also say you are trying to run before you can even walk. Until you
understand what the AS400 provider can or cannot do for you (by running
simple experiments, reading the documentation) and what its limitations are
(IBM documentation on it and the database it retrieves from), it is
pointless bombarding the newsgroup with more complex tasks. You seem to be
unwilling to do that.

Microsoft has some ADO example code that illustrates various methods and
properties of Connection,Recordset and Command objects. It should be
possible to get the same examples working on AS400 with very little
modification.

Stephen Howe

 

[R. Choate]

I would also have you know that I did lots of experiments with my code before posting anything, contrary to your accusation. I have
stated in my posts that I had narrowed the problem down to the numeric field. How do you think I eliminated everything else? I ran
simple code, of course. Until you know more about AS400 than you do, don't preach to me under the guise of help, about what you
think looks wrong, or about what you don't think I did before posting.
--
RMC,CPA


"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message

I finally was able to save my recordset from AS400 to ADTG file

(records.dat). There were 6 fields, one of which is numeric, and

that numeric field was a roadblock to saving the recordset. Somebody came

up with digits(mynumericfield) as a solution, and it

worked.

Yes, but the likely reason it "worked" is that digits() converted the
numeric field to a textual numeric field. So the field in the recordset is
now just text. Not numeric at all.

Personally I would not be happy with that.
I would want a proper numeric field not a textual field.
So the solution that he has come up with is okay but it has its limitations.
I would be looking for a better solution.

Now, I can see that records that have values of less than 5 digits (in my problem
field), have leading zeros. For instance,
CUST = 945, is now CUST = 00945. I believe the table definition stated

that my numeric field should be 5 digits. Anyway, now when I

try to re-open my new recordset and find a specific record, using the FIND

method, I can't get FIND to work unless I use leading

zeros in my comparison.

Right. Because you are not comparing numerically but as strings. Naturally
"345" is not the same as "00345" as a string that is. So FIND won't work
without leading zeros. And you might find problems with SORT if the textual
field has a decimal point in it.

I find your experience with the AS400 provider strange. I find it difficult
to believe that is is so non-standard that it does not understand ISO SQL92,
a previous SQL standard.

In your case, I would make some small experiments with the AS400 provider.
In particular, you should be able to do

"SELECT field1 FROM table1"

for a recordset and you should be able to debug print the type of the field.
If I was you, I would interested to find out what AS400 data types map to
what VB data types.

I would also see if the AS400 provider understands the SQL I mentioned.

I would also say you are trying to run before you can even walk. Until you
understand what the AS400 provider can or cannot do for you (by running
simple experiments, reading the documentation) and what its limitations are
(IBM documentation on it and the database it retrieves from), it is
pointless bombarding the newsgroup with more complex tasks. You seem to be
unwilling to do that.

Microsoft has some ADO example code that illustrates various methods and
properties of Connection,Recordset and Command objects. It should be
possible to get the same examples working on AS400 with very little
modification.

Stephen Howe

 

[Stephen Howe]

If, somehow, you did not intend to be as condescending as you sounded in
your post, then I will apologize for reading it

incorrectly.

Sorry not my intention to be condescending. I will leave you to work
everything out.

Stephen Howe

 

[Stephen Howe]

I would also have you know that I did lots of experiments with my code
before posting anything, contrary to your accusation.

I have stated in my posts that I had narrowed the problem down to the

numeric field. How do you think I eliminated everything else?

Then why did you post a complex example to the newsgroup?
If you have "narrowed the problem" down, why post something unnarrowed?

I ran simple code, of course.

There is no "of course". We don't know that. We are not mind readers. None
of the code you posted was simple.
I can only judge on the basis of what you posted. If what you posted is
unrepresentative, how am I or anyone else supposed to judge what you have or
have not tried?

And you are right. With so many different combinations of MDAC version
(2.1,2.5,2.6,2.7,2.8), OLE DB providers, languages
(VB,C,C++,Delphi,JavaScript,VBScript), back-end databases; it is impossible
to be an expert with all combinations.

If you have tried some experiments on numeric fields with the AS400, then
why not post what you learned? I did not see anything.

I am no expert on IBMDA400 but I can hunt for documentation and read it

The first table confirms what you see in terms of Defined Size being -1.
It shows the relationship between th database types and ADO types and
Variant types
http://www-1.ibm.com/servers/eserver/iseries/access/3tier/datatypes.htm

And I see examples here:
http://www-1.ibm.com/servers/eserver/iseries/access/toolkit/adooledb.htm
The VC++ and VB samples are strange. Direct manipulation of variants. Have
not see anything on numeric fields

But no PDF file on the provider. I can find the DB2 provider help file but
not the iSeries provider help file.
The only page I find is
http://www-1.ibm.com/servers/eserver/iseries/access/oledb/
which does not have any links to what the provider can/cannot do.

Stephen Howe

 

[R. Choate]

Well, I'll thank you for the time you spend hunting down those links. One thing you might notice from the IBM site is that IBM is a
little bit like Microsoft in that they are somewhat arrogant in their manner. They don't care if their products don't "play well
with others". You know, IBM is big brother and they are going to do their thing, and they don't care what anyone thinks about it. I
think that is why you saw such strange, non-conforming samples. They also have very out-of-date technical info on many items related
to what I was working on, and I'm talking about hundreds of pages of it. If you didn't already know from your other experience that
what you are reading is not true, they would really lead you astray with it. I spent so many hours reading that stuff and trying
samples, that I should be an expert now. Unfortunately, I'm not. Not only that, but I only was able to finish the project after
getting help from the NGs. I was hesitant to post any code at all, because I didn't want to confuse people with extra unneeded info
(since I knew where the problem was), but I posted it anyway because I think most people in the NGs who are truly knowledgeable,
aren't comfortable offering anything if they don't see the code.

Frankly, I think the code I posted is very simple, but that isn't what I meant. I ran the code without the problem field, and it
worked fine. This confirmed to me that the numeric was the problem. I also know that there is a problem with saving numeric data to
an ADTG file because of the decimal,etc. The documentation says it can't be done. That is why I wanted to convert it to string. Once
I got that done, it was all down hill. I only posted the specific problems that I needed help with. Most of the groups I posted to
are full of people who are very familiar with code exactly like what I posted, with the exception of the AS400 specific stuff. The
connection and recordset items are all basically boilerplate code and it's simple. That's why they created ADO, because it is
easier.

The bottom line is that I read all the documentation I could, as well as everything else I could find that would help. Ultimately,
it was a guy in an AS400 group who gave me the solution which got me over the hump. I would have possibly never gotten it from
reading IBM crap. Now, everything appears to be working well, and I understand what to do with the "numeric text" so that it works
for me. I read your other post and I appreciate that you didn't intend to be talking down to anybody. I also appreciate the effort
you gave to come up with those links, some of which link in circles, as you might have noticed. Now you know some of what I had to
deal with, just to fix 2 or 3 lines of stupid code.
--
RMC,CPA

I would also have you know that I did lots of experiments with my code

before posting anything, contrary to your accusation.

I have stated in my posts that I had narrowed the problem down to the

numeric field. How do you think I eliminated everything else?

Then why did you post a complex example to the newsgroup?
If you have "narrowed the problem" down, why post something unnarrowed?

I ran simple code, of course.

There is no "of course". We don't know that. We are not mind readers. None
of the code you posted was simple.
I can only judge on the basis of what you posted. If what you posted is
unrepresentative, how am I or anyone else supposed to judge what you have or
have not tried?

And you are right. With so many different combinations of MDAC version
(2.1,2.5,2.6,2.7,2.8), OLE DB providers, languages
(VB,C,C++,Delphi,JavaScript,VBScript), back-end databases; it is impossible
to be an expert with all combinations.

If you have tried some experiments on numeric fields with the AS400, then
why not post what you learned? I did not see anything.

I am no expert on IBMDA400 but I can hunt for documentation and read it

The first table confirms what you see in terms of Defined Size being -1.
It shows the relationship between th database types and ADO types and
Variant types
http://www-1.ibm.com/servers/eserver/iseries/access/3tier/datatypes.htm

And I see examples here:
http://www-1.ibm.com/servers/eserver/iseries/access/toolkit/adooledb.htm
The VC++ and VB samples are strange. Direct manipulation of variants. Have
not see anything on numeric fields

But no PDF file on the provider. I can find the DB2 provider help file but
not the iSeries provider help file.
The only page I find is
http://www-1.ibm.com/servers/eserver/iseries/access/oledb/
which does not have any links to what the provider can/cannot do.

Stephen Howe

 

[Stephen Howe]

I think that is why you saw such strange, non-conforming samples.

I think IBM's documentated examples suck. I download the VB and VC++ samples
and looked at them. I was a little shocked at the poor quality. I am
reasonably certain that they could, if they wanted to, write better quality
ADO code. Instead they are teaching programmers bad habits. I was looking
for RecordSet examples but did not see much. I wanted to see how each data
type was handled but drew a blank.

I also saw a few dates on the web pages with years like 1996, 1998 which did
not give me much confidence that the material was up-to-date.

I also found a Adobe Acrobat PDF file about the IBM DB2 provider. This was
very nicely documented. I learnt from that that the only server-sided cursor
implemented was forward-only and read-only. If you wanted an updatable
recordset, it had to be client-sided. I could not find an equivalent PDF
file about the IBMDA400 provider.

The same is true for Microsoft's VC++ ADO examples. Microsoft created these
very nice Smart Pointers for handling much of the COM-related pointers. For
Connection, Command and Recordset objects they are a joy to use.
Unfortunately they are not used in all the VC++ ADO examples. I see manual
AddRef's and Release's when the Smart Pointer would have been the better
option. As a result, their examples are not "exception-proof" in C++. With a
small bit of effort, the examples could be reasonably bomb-proof. That
means, newbie ADO and C++ programmers will be writing less robust code due
to this.

I complained to Microsoft about this in 1999 and got nice reply from the ADO
documentation team saying that they will be updating the documentation. They
still haven't 4 years later. I won't hold my breath.

I was hesitant to post any code at all, because I didn't want to confuse

people with extra unneeded info

(since I knew where the problem was), but I posted it anyway because I

think most people in the NGs who are truly knowledgeable,...

That depends on newsgroup. For some yes because the range of knowledge is
small. For ADO, it is problem because the number of combinations to cover is
too great. So I can say something about various versions of MDAC, VB and
VC++, SQLOLEDB provider or SQL Server 7.0/2000 due to experience. But for
others, it is harder. I have nothing to say about using Delphi with ADO or
using the Sybase provider. So the advice would be to mention as much detail
as possible about your problem (and error messages word verbatim), hoping
that the scraps of information will be enough ring some bells in someones
mind.

worked fine. This confirmed to me that the numeric was the problem. I also

know that there is a problem with saving numeric data to

an ADTG file because of the decimal,etc. The documentation says it can't

be done.

Yes. I saw a comment that it can be manipulated in VB but I saw no details
on how you do this (or a URL link). I find it unbelieveable that numeric
types cannot be directly used.

Stephen Howe