StrataFrame Forum » .NET Forums » General .NET Discussion » Anyone have any practical experience with an AS400 dataprovider that works.

Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 30 Oct 2006 13:56:17 GMT

Our biggest customer uses an AS400 as their database. The very limited programming I have done for them was years ago using Perl and ODBC. Most of the work we do for them currenty is done in RPG but they are now asking for stuff that is better suited to .NET, specifically ASP.NET, for their customers to view inventory on the web.

I have been looking and have some leads but if someone here has some practical expreience with something that works ( a data provider), I would sure appreciate it.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 30 Oct 2006 13:56:17 GMT

Ok thanks, I can take that directly to my "higher up" and get some breathing room :)

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Trent L. Taylor — Mon, 30 Oct 2006 13:20:20 GMT

We do not have a timetable on this at the moment. This may end up requiring a bit of work. We will discuss this at our next development meeting. Just so you know, we (the developers) are not in total control of our schedule...which is the way any successfully run business should be :) . So we have to report to some "higher-ups" who will make the ultimate decision as to when this goes on the schedule.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 30 Oct 2006 13:11:39 GMT

:Whistling:

any word/timetable? Thanks.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Fri, 27 Oct 2006 21:38:33 GMT

Any luck with this? Getting the BO Mapper to "see" the DB2 "tables" with the OLE Provider?

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Mon, 16 Oct 2006 13:49:10 GMT

I think that the IBMDA400 is correct for the OLE DB provider, however, I think that the GetSchema() method of the OleDbConnection object requires different parameters than other OLE DB providers. Where with FoxPro, we use GetSchema("TABLES"), I don't think we can use "TABLES" as a parameter, because the database does not have "TABLES" but files. So, I think you're right on with your use of the DataDirect provider for creating your As400DbDataSourceItem class, but with the BOMapper, I think we're going to have to figure out what constraints we can use with the GetSchema() method to enumerate the database objects.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Trent L. Taylor — Mon, 16 Oct 2006 13:34:22 GMT

[quote]how do I implement SF with their ADO object? If I cant use the BO Mapper doesnt everything fall down?
[/quote]

You are correct. You will need to use the OLE DB provider for the BO Mapper portion. You can use their ADO.NET provider for run-time purposes. That is why you need to learn the OLE DB provider type. The nice thing here is that you will not need to distribute the OLE DB provider in this case, just get it working on your development machine.

In either case, just create a simple text program that creates an OleDbConnection and then call the Open...just keep changing out the provider until it works. This is easier than going in and out of the BO Mapper. I cannot give you the information for the provider since I am not familiar with this data provider, but I do know that the provider is where your problem is.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 13:30:29 GMT

Here is how I tested using their sample code. It worked.

Dim conn As DDTek.DB2.DB2Connection

conn = New DDTek.DB2.DB2Connection("host=192.168.42.xxx; User ID=xxxxxx;Password=xxxxxx;database=xxxxxx)

Try
conn.Open()
MsgBox("good")
Catch ex As DDTek.DB2.DB2Exception
MsgBox(ex.Message)
End Try

conn.Close()

I was under the impression that I could use SF with the OLE string in the mapper as long as it referenced the DB2 provider. If that isnt the case, how do I implement SF with their ADO object? If I cant use the BO Mapper doesnt everything fall down?

I got the OLE to work using Provider=IBMDA400, its just doesnt enumerate the table names correctly, and its slow.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Mon, 16 Oct 2006 13:24:04 GMT

When you use the DataDicrect provider in code, do you create an OleDbCommand object, or is it a Db2Command object from one of their namespaces?

I was under the impression that their provider was not an OLE DB provider, but actually a native ADO.NET provider, with its own classes and everything.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Trent L. Taylor — Mon, 16 Oct 2006 13:22:25 GMT

Well, that is much closer to a provider name since it is in COM class name or program ID format.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 13:19:02 GMT

DataDIrect had me try DDTek.DB2..........

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Trent L. Taylor — Mon, 16 Oct 2006 13:14:43 GMT

Well, I don't think that your provider is correct. I will have to investigate what the provider would be for this type of connection source, but an example is this:

Visual FoxPro = VFPOLEDB.1 OR VFPOLEDB

Microsoft Jet 4.0 OLE DB Provider = Microsoft.Jet.OLEDB.4.0

Notice that the provider will not accept spaces. It is looking for the COM class name in most cases.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 13:10:18 GMT

they did 15 minute ago :blink:

Anyhow I used DbProviderFactories.GetFactoryClasses() to make sure I had the right registered name. It is registered as "DataDirect DB2 Data Provider", which I tried with no luck, same error.

Here are the images again. Thanks.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Trent L. Taylor — Mon, 16 Oct 2006 13:02:29 GMT

Just post the images as attachments. I don't know why they aren't showing up.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 12:19:48 GMT

not sure why the first image doesnt show, here it is again.

[img]http://forum.strataframe.net/Attachment240.aspx[/img]

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 12:17:36 GMT

Finding it tough to give up considering I am finding that the speed of the DataDirect provider is easily 20x the OLE one.

Here are some more details of the errors I am getting trying to use the DataDirect provider in the BO Mapper.

I called their support and they confirmed that the DB2 provider is correctly registered in the GAC and the machine.config, which I tend to believe since everything works using the native .NET tools. Perhaps I am doing something wrong that the shots will illustrate.

DataDirect says they are more than willing to do anything to help, including talk to you guys.

[img]http://http://forum.strataframe.net/Attachment238.aspx[/img]

[img]http://forum.strataframe.net/Attachment239.aspx[/img]

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Mon, 16 Oct 2006 08:53:59 GMT

No problem. We've got an inside contact at IBM that might be able to hook us up... if not, I believe they have a "personal" version of DB2 that you can install for free.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Mon, 16 Oct 2006 08:51:48 GMT

Thanks Ben, I am going to move ahead with my current need without Strataframe as I have a tight timetable but being able to use SF with our AS400 clients is srill very important to us. I appreciate you looking into it. If you need anything from me to help you, please let me know.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Mon, 16 Oct 2006 08:42:36 GMT

I think we're going to have to get a DB2 server setup around here to figure this one out. Basically, the BOMapper uses the GetSchema() method of the OleDbConnection object to enumerate the "databases" and "tables" but appearantly, the collections are different when getting the schema. It's going to take some testing, but I'll keep you posted.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Sun, 15 Oct 2006 22:32:00 GMT

I have things cooking in "raw ADO" using the DataDirect provider as well. Since I can access data from my AS400 using both OLE and ADO now, I guess I just need help making these work with SF.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Sun, 15 Oct 2006 21:15:37 GMT

Next question....

I got the initial work done so at least it doesnt blow up on me using:
DataLayer.DataSources.Add(New AS400DataSourceItem("", "Provider=IBMDA400; Data Source=192.168.42.xxx; User ID=xxx; Password=xxx;"))

I didnt actually change much in the custom datasource item, I figured once I mapped it I could debug.

My problem is this, I dont seem to be able to use the BO Mapper. I have the project set to use the OLE DB connection string and it connects, but the mapper only shows SQL Server, then the IP address of the server in the treeview when I hit "Specify Data Source", no tables. The AS400 uses libraries, then files instead of databases and tables, this may be the issue.

This is the code that works not using Strataframe if that helps any. It is just a simple "proof of concept" that loads a single field into a grid.

Being able to use Strataframe with our AS400 client base is huge for us, I appreciate the ongoing help.

Try
Dim sqlstring As String
sqlstring = "SELECT CSANCS FROM FRUITF.PLT"
'setup our connection to the database
Dim oConn As OleDb.OleDbConnection

oConn = New OleDb.OleDbConnection("Provider=IBMDA400; Data Source=192.168.42.xxx; User ID=xxx; Password=xxx;")
oConn.Open()

'setup the data adapter
Dim myDataAdapter As OleDb.OleDbDataAdapter

myDataAdapter = New OleDb.OleDbDataAdapter()

myDataAdapter.SelectCommand = New OleDb.OleDbCommand(sqlstring, oConn)

Dim myData As DataSet
myData = New DataSet()

myDataAdapter.Fill(myData, "PLT")

Dim dt As DataTable
dt = myData.Tables("PLT")

Me.BindingSource1.DataSource = dt
''binding the table to datagrid
Me.DataGridView1.DataSource = Me.BindingSource1
Me.DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
Me.DataGridView1.Refresh()

'close the connection
oConn.Close()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Thu, 12 Oct 2006 10:27:38 GMT

Awesome, you guys are the best.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Thu, 12 Oct 2006 10:10:46 GMT

If all you want do to is create a read only DB2 item, then it will actually be quite easy. You will probably have it done before the day is out. You'll still want to copy over the SqlDataSourceItem and modify it, but you can take the methods like UpdateRow(), and CreateInsertCommand(), etc. and just throw a NotSupportedException from within them. Then, all you need to do is modify the CreateSelectCommand() method to match what the AS400 is expecting. As far as I can tell, the only thing that needs to be modified up front is to remove the TOP keyword and make it a LIMIT at the end of the command (you'll see where it is when you look at the code). The other methods that need to be changed are one-liners... like CreateBlankDbCommand()... it just returns a new OleDbCommand object, or whatever provider you're using.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

Keith Chisarik — Thu, 12 Oct 2006 10:04:15 GMT

Write AS400DataSourceItem, check.

Tell me how realistic this is to save some up-front work. Initially all we need to do for phase one is export, read, and report data. Could I just convert/modify the SELECT type methods from the AccessDataSourceItem template to create a sort of read-only DbDataSourceItem. All I really want to do do is populate a BO, not update it (yet).

Then later I could layer on the more complicated (I'm sure) INSERT, UPDATE, and concurrency methods?

What do you think? Is that viable at all?

Thanks.

RE: Anyone have any practical experience with an AS400 dataprovider that works.

StrataFrame Team — Thu, 12 Oct 2006 09:34:23 GMT

I would use either the OleDb* objects or some other ADO.NET provider, like the one from DataDirect (http://www.datadirect.com/products/net/index.ssp). Once you have the provider chosen, you will need to create your own DbDataSourceItem, like SqlDataSourceItem. Your best bet is going to be to copy and modify the SqlDataSourceItem class's code file to create your own DbDataSourceItem for DB2. The DbDataSourceItem class creates all of the actual SQL commands from the QueryInfo objects that are supplied by the DataLayer within the business objects. It will take some work, and you will probably want to setup some unit tests to make sure it's all working, but you can use most of what is in the SqlDataSourceItem, because the two are very similar. You can also cut out pieces of the implementation, for example, if you don't want to be able to support stored procedures. The AccessDataSourceItem class uses OleDb* objects, but does not support stored procedures, so it is much smaller than the SqlDataSourceItem or OracleDataSourceItem.