Retrieving Data from DB2 to an ASP page

Have you tried connecting to DB2 from your classic ASP pages?  What connection string are you using?

You should be able to use the IBM AS400 OLE DB Provider to connect.
http://www.connectionstrings.com/ (See IBM DB2)

Examples:
Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50000;uid=myUserName;pwd=myPwd;
or
Driver={Client Access ODBC Driver (32-bit)}; DATA SOURCE=hostname;System=systemname;UID=myUserName;PWD=myPwd;

I've been asked to use OLE DB provider and avoid using ODBC.
Currently based on the library of providers I see I have this: IBM AS400 OLE DB Provider

As per your connection string:
Provider=IBMDADB2;Database=sample;HOSTNAME=db2host;PROTOCOL=TCPIP;PORT=50000;uid=myUserName;pwd=myPwd;
My question hence is: HOSTNAME? Isn't this the same as DSN? Then in that case I would be creating a DSN on the webserver pointing to the DB2 on a seperate maching. But in creating a DSN - I'm using ODBC, which I've been asked to avoid in the first place. - OR does HOSTNAME mean the DB2 Machine Name?

Error Type:
ADODB.Connection (0x800A0E7A)
Provider cannot be found. It may not be properly installed.
/super/TEST/IBM.asp, line 10


How do I install the reinstall the provider?

HOSTNAME was given to me by the AS400 Programmer as the serial number of the IBM machine.

Changed my connection string to the following

"Provider=IBMDA400.DataSource.1;Password=password;User ID=userid;Data Source=IBMSerialNumber;Transport Product=Client Access;SSL=DEFAULT"

The above I got using tool in VStudio

The above string gives me the following error:

Error Type:
IBMDA400 Session (0x80004005)
CWBSY1007 - Password is invalid
/super/TEST/IBM.asp, line 12

Lines 10 - 12:
set CN = Server.CreateObject("ADODB.Connection")
str = "Provider=IBMDA400.DataSource.1;Password=pwd;User ID=uid;Data Source=mydatasource;Transport Product=Client Access;SSL=DEFAULT"
CN.Open str

is there any other way I should put in my password?

Sorry about the last post. my error

it seems it works fine except it returns back a sql error:
Error Type:
IBMDA400 Command (0x80004005)
CWBDB0036 - Server returned SQL error
/super/includedfiles/rsGeneric.asp, line 30

here's how i retrieve the data:

str = "Provider=IBMDA400.DataSource.1;Password=pwd;User ID=uid;Data Source=mydatasource;Transport Product=Client Access;SSL=DEFAULT"
CN.Open str


sql = "SELECT SSITEM1.MDESCR, SSITEM1.MUPCKE WHERE SSITEM1.MNDEPA =1 Order by SSITEM1.MDESCR ASCENDING "

      Set cmd = Server.CreateObject("ADODB.Command")

      Set rs = Server.CreateObject("ADODB.Recordset")

      cmd.CommandText = sql
      Set cmd.ActiveConnection = CN

      rs.Open cmd

Is there something I should be modifying in the above

Hmm, try:
ORDER BY SSITEM1.MDESCR ASC

(noting 'ASC' instead of 'ASCENDING') DB2 is picky with syntax, and I'm not sure if ascendign spelled out is valid.  Make sure MNDEPA is a numeric field and not a text field.  Otherwise, you might have to put single quotes around it.

Thanks -... but I think the sql error lies in my not mentioning the library.

The file and fields I have to access are in ASPLib created by our AS400 Programmer
But if I try to mention "Database=ASPLib" I get error in connection string

I tried using Library.FileName.FieldName but no result.

How about 'Data Source=ASPLib'?

GOT IT !... if I remove the order by clause

Here's the sql which works

SELECT ADPROJ.SSITEM1.MDESCR, ADPROJ.SSITEM1.MUPCKE from ADPROJ.SSITEM1 WHERE ADPROJ.SSITEM1.MNDEPA ='1'

but if I add to the above sql

Order by ADPROJ.SSITEM1.MDESCR ASCENDING

Then in that case I get the sql error....any ideas how to resolve this issue?


Also is there any place I can get a refresher online where I can brush up my sql skills for DB2? If this works then I'm sure I'll be joining tables,  and doing more complex queries.

Did you try 'ASC' instead of 'ASCENDING'?

I think I have a URL at work I can post tomorrow.

Thanks ! yes it works...Looking forward to the URL....

Is there any gui based client side tool in which I can view my db2 database and drag and drop files, inner or outer join them and check the results, perhaps the tool produces sql code when you work on the files in the GUI environment.
Something like Access - which I used to link tables in from the sql server and then to check some things quickly I simply used Access to drag/drop tables and check results out with Access making the SQL Code behind the scenes.

Is there any such a tool for DB2 (besides access - which won't help cause the sql language it produces is for MS SQL)

This is the reference I use for DB2:
https://aurora.vcu.edu/db2help/db2s0/frame3.htm

I'm not aware of a GUI program to work with it.  I use WinSQL Lite to do SQL queries against DB2, but that's not like Access.

Thanks for the link. Awesome!

Today I moved the .asp to a new machine and got the following error:

Provider error '80040e18'

Rowset position cannot be restarted.

/super/TEST/IBM.asp, line 37

line 37 points to : rs.MoveFirst

if I comment out - rs.MoveFirst - it works out fine. Any ideas what could be going wrong?

it worked ! Thanks !!!!!!