ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sync data as400 to mysql through php

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sync data as400 to mysql through php

    Hello all,

    I'm trying to build a script using odbc connection to as400 that sync data between mysql and as400. I've got this ideia:

    Is there anyway to do a query as400 to return a csv file, that it will be used to load data into mysql table. This is more faster that cicles i think...

    Is it good/bad It's possible ? there are alternatives ?

    Best Regards,
    António

  • #2
    Re: sync data as400 to mysql through php

    withing your connection to the 400.

    select now() , date(now()), time(now())
    From SYSIBM/SYSDUMMY1

    gives.

    NOW ( ) DATE ( NOW ( ) ) TIME ( NOW ( ) )
    2013-02-27-09.42.12.087651 02/27/13 09:42:12
    Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

    Comment


    • #3
      Re: sync data as400 to mysql through php

      I don't like that there are two tables...
      Data will never stay in sync.... did I say NEVER!

      If you are using PHP then just install free zend server on IBMi and access the data directly from the IBMi.
      ==> Is this an option for you?

      Jamie
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment


      • #4
        Re: sync data as400 to mysql through php

        @DeadManWalks i didn't understand your reply

        @jamief Ok, you are right. sync is not the right word. Regarding to your reply, that is not an option unfortunately. The only connection i've got right now is via odbc.

        I've got
        webserver (php/mysql/apache) - machine#1 (on DMZ)

        provider machine (php/apache) - machine#2 (lan) using Driver={Client Access ODBC Driver (32-bit) to connect to machine #3 (and here... i need to send data to mysql on machine#1)

        as400 server - machine#3 (lan)

        This procedure (to send data do machine#1) will occur once a month

        Comment


        • #5
          Re: sync data as400 to mysql through php

          There are several ways to do this:

          1. Use a java driver on the ISeries to update the MYSQL database on machine 1:



          2. Install the ISeries Access drivers or JT400 ODBC driver's on machine 1 and just use PHP to retrieve the data from the ISeries

          3. Have the ISeries create a CSV and FTP it to Machine 1, then have machine 1 use that file to update its database.

          Comment


          • #6
            Re: sync data as400 to mysql through php

            Perhaps the rsync command?

            Everday im digging more and more about iSeries PASE, my ydill with iSeries get better… and also with the Unix world. Once i tested the ...


            Pete

            Comment


            • #7
              Re: sync data as400 to mysql through php

              I fail at life. I saw data and read DATE. my bad.
              Hunting down the future ms. Ex DeadManWalks. *certain restrictions apply

              Comment


              • #8
                Re: sync data as400 to mysql through php

                @Pete Tks for your input,

                I am really a Newbie and i'm a little lost. I Have client access (v5r1) installed on machine #2, that access to machine #3 (wich i don't have permissions to write, only read). I don't know where and how to install rsync.

                @DAG0000 I would like to do option 3. I can create csv file in my Client Access, and i can save it on mapped driver to machine #1 but now how can do this process automatically and once in a month ?

                Tks for all the help.

                Click image for larger version

Name:	transfer.png
Views:	1
Size:	82.4 KB
ID:	126624

                Comment


                • #9
                  Re: sync data as400 to mysql through php

                  @DeadManWalks

                  I saved that transfer and now i can run it on duble click:
                  Click image for larger version

Name:	id1.png
Views:	1
Size:	4.1 KB
ID:	126625

                  Now i can add on windows xp task scheduler one event happening once in a month that will run that file. It will work, i hope

                  What do you guys think about that ?

                  Best Regards
                  António

                  Comment


                  • #10
                    Re: sync data as400 to mysql through php

                    OneGlobal, there's a command called Copy to Import File (CPYTOIMPF) that's designed to convert a database table on DB2 for i into a CSV file (or similar) so that it can be imported into a differnet software package. (Such as MySQL).

                    Zend also provides an implementation of MySQL that runs natively on IBM i and is capable of accessing DB2 tables via the MySQL database interface.

                    IBM also provides an ibm_db2 driver for PHP so PHP scripts can directly access DB2 for i databases (even over a network) to query your data directly.

                    There are many other ways to do this -- so many options it's almost too much to take in. And... if your destination system is another IBM i system (or "as400" as you frustratingly still call it) one wonders why you want to import it into MySQL in the first place. Why not just save/restore it to the destination system?!

                    Comment


                    • #11
                      Re: sync data as400 to mysql through php

                      Originally posted by oneglobal View Post
                      @DAG0000 I would like to do option 3. I can create csv file in my Client Access, and i can save it on mapped driver to machine #1 but now how can do this process automatically and once in a month ?
                      oneglobal,

                      Just write a program on to run on your ISeries that builds a PF with the data you need. Use CPYTOSTMF command to convert it to a csv. Then just send it to the machine you need it on. Just use the scheduler to run it once a month.

                      However I recommend you make it a tab or pipe delimited. I have found csv's can become corrupted if the data already has commas in it.

                      Comment


                      • #12
                        Re: sync data as400 to mysql through php

                        @DAG0000:

                        Shouldn't this:
                        Use CPYTOSTMF command to convert it to a csv.
                        be cpytoimpf?

                        GLS
                        The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

                        Comment


                        • #13
                          Re: sync data as400 to mysql through php

                          Originally posted by GLS400 View Post
                          @DAG0000:

                          Shouldn't this:


                          be cpytoimpf?

                          GLS
                          Yes it should. Need to pay better attention when I post. It doesn't help that those two commands are so nearly identical in thier spelling. I've lost count of the number of times I have confused the two.

                          Comment


                          • #14
                            Re: sync data as400 to mysql through php

                            Actually, CPYTOSTMF would work fine, you can use this command to write System i files directly to network servers if you use the QNTC file system.

                            Pete

                            Comment


                            • #15
                              Re: sync data as400 to mysql through php

                              @pete:

                              cpytostmf only works on flat files .... If you try it on an externally defined file this is the error message you get:
                              (CPDA082)
                              Message . . . . : Object is not a file or is a file with attributes not
                              supported by this command.
                              Cause . . . . . : Object /qsys.lib/qs36f.lib/carrfile.file must be either a
                              source physical file that has three fields or a program described file.
                              Externally described files and source physical files containing fewer or
                              more than three fields are not permitted by this command.

                              Recovery . . . : Specify the name of an existing source physical file with
                              correct attributes, or specify a program described file, then try the
                              command again.
                              Best of Luck
                              GLS
                              The problem with quotes on the internet is that it is hard to verify their authenticity.....Abraham Lincoln

                              Comment

                              Working...
                              X