Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieving Data from DB2 to an ASP page

Posted on 2005-03-07
17
Medium Priority
?
1,134 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
  • 9
  • 6
15 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 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