Retrieving Data from DB2 to an ASP page

I've read here that OLE DB Provider for DB2 (from Microsoft) is ideal to retrieve data for a classic ASP page. I don't seem to have this provider when I browse through my library.
Where can I get it? The last I found was I have to get Microsoft's Host Integration Server to be able to get this OLE DB provider for db2.

I do see an IBM AS400 OLE DB Provider in my library. But when I try to use this driver to import data into my SQL Server - it prompts me for a Data Source Name or Location(Location is greyed out). But if I create a data source then I'm using an ODBC for this which defeats the purpose of using an OLE DB in the first place....am I thinking right or do I need to think on different lines here?
pan_sqlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hendridmCommented:
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;
0
pan_sqlAuthor Commented:
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?
0
pan_sqlAuthor Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

pan_sqlAuthor Commented:
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?
0
pan_sqlAuthor Commented:
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
0
hendridmCommented:
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.
0
pan_sqlAuthor Commented:
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.

0
hendridmCommented:
How about 'Data Source=ASPLib'?
0
pan_sqlAuthor Commented:
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.
0
hendridmCommented:
Did you try 'ASC' instead of 'ASCENDING'?

I think I have a URL at work I can post tomorrow.
0
pan_sqlAuthor Commented:
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)
0
hendridmCommented:
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.
0
pan_sqlAuthor Commented:
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?
0
hendridmCommented:
Perhaps it has something to do with the recordset cursor???
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortype.asp

ex: rs.CursorType = adOpenDynamic
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pan_sqlAuthor Commented:
it worked ! Thanks !!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.