odbc as/400 connect to mysql

25 pts.
Tags:
AS/400 Client Access
MySQL
ODBC
I am trying to figure out how to get data from the as/400 into mysql
ASKED: April 27, 2009  4:49 PM
UPDATED: May 5, 2009  7:38 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Why..? IBM has all kinds of connection strings out now. And there not ODBC. Anything you can do in Mysql the as/400 can do better and faster. Maybe there might be some reasons to do this but I can’t think of any. If you have the 400 avaialble you can use its data bank. I’m not making fun but I have ran into this more and more as years go by.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • mcl
    Well, why is a question we all ask, but really in this case "what" and "how" are better questions. So what is your data source ("traditional" files in a library in QSYS, data from schemas, something in the IFS?) How are you looking to import the data to MySQL? Are you looking at ODBC or something like a delimited file? Regards Mike
    2,740 pointsBadges:
    report
  • mcl
    Oh, gee, I just re-read the post and you specified ODBC.. OK, is your mySQL running on Windows or Linux? Have you tried searching on Google? There are lots of posts on this - a bunch of people have done it before. Regards' Mike
    2,740 pointsBadges:
    report
  • Sunnyday
    Mysql is running on windows. The data is on the as/400 and the only way I know how to get the data is writing a query using wrkqry and I do know how to to create a data file. The problem is I am creating a shopping cart that uses phpmyadmin and mysql and I need the data from the as/400 in that database. So I guess really my question is how to get the data into phpmyadmin. Thanks Cheryl
    25 pointsBadges:
    report
  • RonKoontz
    Hree is an example in VB I did just to prove you can get the data from the 400. I did this about 2 month ago for a friend of mine. You will need to add some authentication to the 400. Like user name and password to the connection screen. It currently just pops up the default IE user name and password box when trying to open the database on the 400. Protected Sub btnget_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnget.Click Dim iConn As New IBM.Data.DB2.iSeries.iDB2Connection() iConn.ConnectionString = "Data Source=10.1.1.1;" Dim OrderIn As String OrderIn = txtOrder.Text.ToString() Dim iCmd As New IBM.Data.DB2.iSeries.iDB2Command("SELECT your fields here FROM YOUR LIBRAY.YOUR FILE WHERE ORDER =" + ORDERIn) iCmd.Connection = iConn Dim iDA As New IBM.Data.DB2.iSeries.iDB2DataAdapter(iCmd) Dim lds As New Data.DataSet() iDA.Fill(lds) Try iConn.Open() GridView1.DataSource = lds GridView1.DataBind() Catch ex As IBM.Data.DB2.iSeries.iDB2Exception btnget.Text = "ERROR" Finally iConn.Close() End Try End Sub
    1,780 pointsBadges:
    report
  • RonKoontz
    Oops.. you were talking PHP.. I'm sure you can convert the code I just poseted to PHP. Or BIND it in your PHP code. I really would avoid putting the data in mysql if its already on the 400. It only causes issues down the road when something goes wrong. Its harder to debug. At least the data you are looking for is on the 400. Sorry. Thanks Ron
    1,780 pointsBadges:
    report
  • mcl
    Cheryl, So, how often do you need the data? And, are you pulling down data from one AS/400 file or from multiple files using a join? In WRKQRY, you can create a data file by specifying a database file in the output type selection. You will of course need the proper authority to create the file. If you don't want to set up an ODBC connection, there are alternatives. For example, you could use the iSeries Access data transfer to download a file. It allows a SQL selection and will format the downloaded file to a CSV format on a windows box. You can then write a PHP program to read that file and import it into your MySQL database. All of that could be pretty much automated.. For a one-time shot, you could download the file using iSeries Access data transfer as a CSV file and then import the CSV file into your MySQL database using the "import" function in PHPMyAdmin. For help on the import, you could Google "Import CSV file using PHPMyAdmin" - there are a lot of references. PHPMyAdmin is a tool to manually maintain a MySQL database - if you are looking for some way to automate this you should probably be looking at setting up an ODBC connection. Regards Mike
    2,740 pointsBadges:
    report
  • Sunnyday
    Mike, I will need the data to update quanities at least once a day. It will take multiple files so I will need to join them to get the data I need. I understand how to write the query and create the datafile but not sure on the php code to import to mysql. If you could at least give me a good idea of where to start searching I would appreciate it. I tried searching for information to connect the as/400 somehow so the data could be updated automatically but my searching led me to several dead ends. Thanks Cheryl
    25 pointsBadges:
    report
  • RonKoontz
    Just wondered if you can use the VB example and convert it to PHP or call VB code within PHP. I'm not a PHP guy so I have no clue. I'm kind of curiouse though. They you can just update it when you want from the 400 and not have lots of extra code to maintain later. Some day I might need to do a PHP app...
    1,780 pointsBadges:
    report
  • mcl
    Cheryl, Take a look here - for information on an ODBC connection.. But forget that for now. There are probably a bunch of ways to get the data from point a to b.. You could try this one.. Depending on the size of the tables and the complexity of the joins it may be worthwhile building the resulting table on the AS/400 through a scheduled job and then just pulling down that table. Assuming you have the result table on the AS/400, use iSeries Access file transfer to download the file from the AS/400 to your Windows box with PHP on it. You can set up the transfer manually, save the transfer definition and then set up a macro to run it using whatever scheduling mechanism you have for Windows. iSeries access will let you transfer the file as CSV or you could optionally build the the file on the AS/400 with whatever seperator you want and transfer it as TEXT. Once you have the file on the windows box you should be able to handle the data. There is an example here that looks pretty simple that will give you an idea of what to do for that. Have you considered running PHP on your AS/400? (Get rid of that Windows box!!) Zend core comes with V6R1.. Just sayin... Regards Mike
    2,740 pointsBadges:
    report
  • Sunnyday
    [...] odbc as/400 connect to mysql [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following