?
Solved

Retrieving Data from DB2 to an ASP page

Posted on 2005-03-07
17
Medium Priority
?
1,110 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:pan_sql
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
17 Comments
 
LVL 8

Expert Comment

by:hendridm
ID: 13479869
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
 

Author Comment

by:pan_sql
ID: 13481030
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
 

Author Comment

by:pan_sql
ID: 13481200
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:pan_sql
ID: 13481326
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
 

Author Comment

by:pan_sql
ID: 13481380
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
 
LVL 8

Expert Comment

by:hendridm
ID: 13481526
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
 

Author Comment

by:pan_sql
ID: 13481554
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
 
LVL 8

Expert Comment

by:hendridm
ID: 13481592
How about 'Data Source=ASPLib'?
0
 

Author Comment

by:pan_sql
ID: 13481636
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
 
LVL 8

Expert Comment

by:hendridm
ID: 13482244
Did you try 'ASC' instead of 'ASCENDING'?

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

Author Comment

by:pan_sql
ID: 13486624
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
 
LVL 8

Expert Comment

by:hendridm
ID: 13488182
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
 

Author Comment

by:pan_sql
ID: 13488245
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
 
LVL 8

Accepted Solution

by:
hendridm earned 500 total points
ID: 13488325
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
 

Author Comment

by:pan_sql
ID: 13489111
it worked ! Thanks !!!!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question