Establishing ADO connection to AS/400 without intervening password dialog
Tony M
using OLE DB from within Visual Basic or an Active Server Page. I can
connect, but every time I try to use use the Open method of the ADO
Connection object, the standard AS/400 user name and password dialog
appears.
I've tried to provide the user ID and password within a connection
string, and also as parameters to the Open method, but they seem to be
ignored. I have tried both DSN and DSN-less connections, with the
same results. Extensive Googling hasn't brought me any closer to a
solution.
My VB code (with system specifics changed to protect the innocent) is
as follows:
With gAS400AdoConnection
.CursorLocation = adUseServer
.ConnectionString = "Provider=MSDASQL.1;" & _
"Driver={Client Access ODBC Driver (32-bit)};" & _
"System=MYSYS;" & _
"Initial Catalog=MYLIB;" & _
"Persist Security Info=True;" & _
"User ID=MYID;" & _
"Password=MYPWD;"
.Open
End With
My Client Access version is V4R5M0, and in VB I am using ADO 2.5. I
am aware of third party ODBC/OLE DB drivers from HiT, but I don't
think my client is willing to go with third party software just yet.
Is there something that must be done on the 400 side of things for
something like this to work? Are there certain settings in the ODBC
data source that can help?
Any help would be most appreciated.
Tony
Jorge Fuentes
There are always more than 3 ways skin a cat, but lately we connect to
the AS400 using ADO and a data connector created with ASNA's Datagate
( either Datagate Components or Datagate for SQL ).
Currently we create custom Data Provider(s) or data connector(s), and
then we use them like this in our ASP.NET page(s):
AS400Connection _conn3 = new AS400Connection("Departments");
AS400DataAdapter _da3 = new AS400DataAdapter();
_da3.ReceiveCommand = new AS400Command("*All", _conn3);
_da3.Fill(ds,"Departments");
_conn3.Close();
_conn3.Dispose();
The password and User Id are setup in the AVR object. We bind the data
to the ASP page and the rest is "magic"
We are in Canada too, send me an email and I will forward more
information ** to all who are interested **
Thanks
Jorge Fuentes
in...@pgmrs.com
www.pgmrs.com
"Tony M" <tmok...@yahoo.ca> wrote in message
news:u2etpvc202je80j8u...@4ax.com...
Chuck Ackerman
We also use ASNA's Datagate (been using it for 2 years) but find it
cumbersome and slower than our needs require. Plus, everytime a file
changes we need to recreate a .DLL via Visual Basic. We've recently tested
HiT's .net product and found it easier to use in a .NET environment and the
tests we used had results approximately 12 times faster than Datagate.
chuck
Opinions expressed are not necessarily those of my employer.
"Jorge Fuentes" <in...@pgmrs.com> wrote in message
news:jiCnb.58007$Pt3.1...@weber.videotron.net...
George Kinney
> using OLE DB from within Visual Basic or an Active Server Page. I can
> connect, but every time I try to use use the Open method of the ADO
> Connection object, the standard AS/400 user name and password dialog
> appears.
>
> I've tried to provide the user ID and password within a connection
> string, and also as parameters to the Open method, but they seem to be
> ignored. I have tried both DSN and DSN-less connections, with the
> same results. Extensive Googling hasn't brought me any closer to a
> solution.
I might be completely off here, but anyways:
Check your login options for the system in question in OperationsNavigator.
The login settings are user-specific, so you need to make sure the
user-profile
that your code is executing under has these set correctly.
Using Apache, its simply a matter of creating a user, setting the login
defaults for
the AS/400 connection, then setting the Apache service to run under that
user
account. I imagine it is similar for other servers, but I wouldn't know.
I'm pretty sure that the initial connection obeys whatever is set-up in
OpsNav,
irregardless of what you put in a DSN.
Jorge Fuentes
If you find yourself changing your file layouts too often, give that job
to a competent database type.
I agree with you that Hit has a good "middleware" product, and it is
fast. But you have to use Visual Basic and ASP toolkits and this could
be cumbersome also.
What I was talking about here is RPG (actually Visual RPG) and it is not
only "middleware", but a programming language. For more information go
to the respective websites.
Thanks
JF
"Chuck Ackerman" <So...@NoSpam.com> wrote in message
news:j_Cnb.169$mA5...@newssvr14.news.prodigy.com...
Shane Mingins
VBA/VB programmer but wrote this for Excel using similar code from an ASP
Crash Course Book I had (played with ASP once).
This may help.
Excel ----
Sub test()
Dim cnDB
Set cnDB = CreateObject("ADODB.Connection")
Dim MYSYSTEM As String
' the name of our system/as400 is BOB
MYSYSTEM = "BOB"
cnDB.Open "provider=IBMDA400;data source=" + MYSYSTEM + ";", "username",
"password"
Dim rsQuery
Set rsQuery = CreateObject("ADODB.Recordset")
' here the important thing is that TESTLIB is a Library on the as400
rsQuery.Open "SELECT NAME FROM TESTLIB.PRODUCT", cnDB
Do While Not rsQuery.EOF
MyMSG = MsgBox(rsQuery("Name"))
rsQuery.MoveNext
Loop
rsQuery.Close
cnDB.Close
End Sub
--
shanem...@yahoo.com.clothes
remove clothes before replying
"It is not the strongest of the species that survive, nor the most
intelligent, but the one most responsive to change." --- Charles Darwin
"Tony M" <tmok...@yahoo.ca> wrote in message
news:u2etpvc202je80j8u...@4ax.com...
Tony M
<shanem...@yahoo.com.clothes> wrote:
Thank you, thank you, thank you! I had assumed that the Data Source
parameter referred to an ODBC DSN name, not the actual AS/400 system
name. It worked both from ASP and VB.
Tony
Chuck Ackerman
First, let me say that our entire public facing web site is written in C#
using Visual Studio .NET. Visual Basic has no role in it. The HiT software
fits into the .NET like a glove. If you'd like to get a taste of this go
visit our site at www.lampsplus.com.
Second, I find the comment about the database layout a little slap. Since
you know nothing about our environment, I find that offhand comment a little
strange. It's the fast pace at which our business is moving that causes our
database to be enhanced. We've made recent partnerships with Amazon,
Google, EasyAsk among many others. These partnerships require extensive
additions/modifications to our database. Unlike many shops we don't have a
static database. I currently have 15 programmers on my staff and am looking
for more. We're very busy and adding data elements to a database file is
just a natural part of a growing company.
chuck
Opinions expressed are not necessarily those of my employer.
"Jorge Fuentes" <in...@pgmrs.com> wrote in message
news:l9Enb.46882$He4.1...@wagner.videotron.net...
Mark
There are a number of approaches...
Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
'ODBC with DSN
cnn.Open "DSN=dsn;UID=user;PWD=password"
cnn.Open "DSN=dsn", "user", "password"
'ODBC DSNless
cnn.Open "DRIVER=iSeries Access ODBC driver;SYSTEM=system;UID=user;PWD=password"
cnn.Open "DRIVER=iSeries Access ODBC driver;SYSTEM=system", "user", "password"
'OLEDB
cnn.Open "Provider=IBMDA400;Data Source=system;UID=user;PWD=password"
cnn.Open "Provider=IBMDA400;Data Source=system", "user", "password"
'ODBC without user
cnn.Open "DSN=dsn;prompt=2"
... in this last example, if you don't include the prompt=2, at the first log-in you may
well get this error (this will depend on a multitude of other settings in Navigator and/or
the DSN):
[IBM][iSeries Access ODBC Driver]Communications link failure. comm rc=8015 - CWBSY1006 -
User ID is invalid, Password length = 0, Prompt Mode = Never, System IP Address =
xxx.xxx.xxx.xxx
<RANT> This default behaviour is extraordinary and finding the documentation on this was a
real PITA. IBM seem to have decided that their ODBC driver would most likely be used
unattended and should error in preference to prompting (Prompt Mode = Never). It doesn't
seem like rocket-science to me that if either the user or password are blank then it
should prompt... but there you go. In a less stressed moment I did find the information
that the Password length = 0 amusing.
You should also be careful of your version. My examples are taken from V5R2. I know the
'rules' have changed since V4R5. For example, at some point the marketing droids at IBM
discarded 'Client Access' in favour of 'iSeries Access'... and consequentially drove a
steam-roller through several existing Windoze configuration set ups. I am sure everything
works a lot better now its called iSeries ;) </RANT>
There is a lot of confusing (if not simply misleading) snippets about how IBM's ODBC
driver connects (or doesn't connect, as the case may be). I think these are a couple of
the more useful ones:
Connection String keywords
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaik678.htm
ADO and MSDASQL OLEDB Prompt Mode Behavior
http://www-1.ibm.com/support/docview.wss?uid=nas101236e66afc3e6af86256a3a005a5289
Mark.