By Keith Chisarik - 9/12/2006
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.
|
By Keith Chisarik - 9/12/2006
*wishes for self edit privs again*
Also, the best lead I have would implement an OLEDB solution, I think you guys told me that would work fine with Strataframe, and that I would just lose some of the strong typing benefits and other benefits of a provider written specifically for the data source?
|
By StrataFrame Team - 9/12/2006
Actually, most of the benefits of the .NET providers written specifically for a database are not used by StrataFrame itself... we have to rely on what's available in the classes in the System.Data.Common namespace to keep SF provider independent. However, you will get better performance finding a DB2 provider instead of using OLEDB. I'm pretty sure DataDirect has a good DB2 provider, as does IBM itself.
|
By Keith Chisarik - 10/12/2006
I have a prototype working for my AS400 data access project, I now want to convert the project to use Strataframe.
How do I take what I have to create the connection....
Dim oConn As OleDb.OleDbConnection
oConn = New OleDb.OleDbConnection("Provider=IBMDA400; Data Source=192.168.42.100; User ID=xxxxxxx; Password=yyyyyyy;")
oConn.Open()
and make it into something like this the framework requires...
'-- Visual Fox Pro
'DataLayer.DataSources.Add(New VfpDataSourceItem("", "myconnectionstring"))
as I dont see a datasource item for OLE.
I realize I cant use the ConnectionManager with OLE.
Thanks.
|
By StrataFrame Team - 10/12/2006
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.
|
By Keith Chisarik - 10/12/2006
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.
|
By StrataFrame Team - 10/12/2006
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.
|
By Keith Chisarik - 10/12/2006
Awesome, you guys are the best.
|
By Keith Chisarik - 10/15/2006
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
|
By Keith Chisarik - 10/15/2006
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.
|
By StrataFrame Team - 10/16/2006
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.
|
By Keith Chisarik - 10/16/2006
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.
|
By StrataFrame Team - 10/16/2006
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.
|
By Keith Chisarik - 10/16/2006
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.
|
By Keith Chisarik - 10/16/2006
not sure why the first image doesnt show, here it is again.
|
By Trent L. Taylor - 10/16/2006
Just post the images as attachments. I don't know why they aren't showing up.
|
By Keith Chisarik - 10/16/2006
they did 15 minute ago 
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.
|
By Trent L. Taylor - 10/16/2006
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.
|
By Keith Chisarik - 10/16/2006
DataDIrect had me try DDTek.DB2..........
|
By Trent L. Taylor - 10/16/2006
Well, that is much closer to a provider name since it is in COM class name or program ID format.
|
By StrataFrame Team - 10/16/2006
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.
|
By Keith Chisarik - 10/16/2006
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.
|
By Trent L. Taylor - 10/16/2006
how do I implement SF with their ADO object? If I cant use the BO Mapper doesnt everything fall down?
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.
|
By StrataFrame Team - 10/16/2006
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.
|
By Keith Chisarik - 10/27/2006
Any luck with this? Getting the BO Mapper to "see" the DB2 "tables" with the OLE Provider?
|
By Keith Chisarik - 10/30/2006
any word/timetable? Thanks.
|
By Trent L. Taylor - 10/30/2006
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.
|
By Keith Chisarik - 10/30/2006
Ok thanks, I can take that directly to my "higher up" and get some breathing room
|