Retrieving data from AS/400 tables with PHP?

80 pts.
Tags:
AS/400 database
ODBC
PHP
I think I will die! Does anyone know how to get data from a table on AS/400 displayed with PHP through ODBC? I'm starting to go mad. I tried ODBC drivers (iSeries Access client, Client Access). Nothing helps. I have Apache server as localhost on my machine and PHP installed and that works fine. The same DSN connection I built in Windows (on a table on As?400) works perfectly with Excel but with PHP it just refuses to work. I have also Installed iSeries Access for Windows (navigator and the rest). I need a working answer. THANK YOU VERY MUCH!!

Answer Wiki

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

Here’s how I’ve done it.

Try this using the Client Access ODBC Driver.

<i><b><?php
$server=”iSeriesName”; #the name of the iSeries
$user=”USERNAME”; #a valid username that will connect to the DB
$pass=”PASSWORD”; #a password for the username

#Connect to the Database with ODBC – $conn is defined and loaded using the “odbc_connect” PHP #directive.
$conn=odbc_connect(“$server,$user,$pass”); #you may have to remove quotes

#Check Connection
if ($conn == false) {
echo “Not able to connect to database…<br>”;
}

#Query the Database into a result set –

$result=odbc_exec($conn,”SELECT * from library.file where somefield=a value”);

#…do other stuff here…

#don’t forget to close the connection when you’re done

odbc_close($conn);
?></b></i>

Hope this works for you.
RE

It looks to me that you have not added the ODBC Driver or DSN to your server that you are hosting the file from. You will need to first load the ODBC Driver and then setup your System DSN; also make sure to set as read-only so you don’t accidentally write back.

the name of your System DSN is what you put in place of iSeriesName in this line
$server=”iSeriesName”; #the name of the iSeries

Discuss This Question: 14  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
  • Avrelivs
    Thank you for a speedy reply, but this solution DOES NOT work. The error is: "Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect" I'm not sure, but I think I have to declare a connection string somewhere. Something like what Excel gives me upon successful connection to the data on AS400 table: DRIVER={iSeries Access ODBC Driver};PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;LANGUAGEID=ENU;DFTPKGLIB=QGPL;DBQ=QGPL;SYSTEM=10.69.23.4; Along with pass and username. And the SQL goes like this: SELECT * FROM "MYLIB"."DTASRC" (Where MYLIB is library with my data, and DTASRC is the actual table. This is really a pain.
    80 pointsBadges:
    report
  • FresnoBob
    "but this solution DOES NOT work" I beg to differ with you sir. This solution does work. I took this code from an existing PHP script on my system and generously provided it to you for your reference. I have changed the names of system, password, and DB references and simplified syntax for you - since YOU are having the difficulties. If PHP is not working for you, maybe you should try CGI instead. Good luck to you.
    30 pointsBadges:
    report
  • mcl
    The PHP code looks OK to me.. You may want to look at these two sites, if you have not already: Regards Mike
    2,740 pointsBadges:
    report
  • mcl
    I guess the links didn't work... PHP Manual - http://us3.php.net/odbc_connect and iSeries connection strings - http://www.connectionstrings.com/?carrier=as400 Regards Mike
    2,740 pointsBadges:
    report
  • Avrelivs
    Thank you again for your help but it seems like I have insulted you? I'm very sorry for that, but this solution really doesn't work for me. That's why I posted what type of error I get and my second question. Do I have to declare somewhere information about driver that connection is built on? You stated in your answer : "Try this using the Client Access ODBC Driver. " But you didn't include it in the code? Am I right? I posted this question because I don't know how to code, not because I wanted to insult anybody and I was not arguing if your solution works or not. If I knew how I would give answers not waiting someone's mercy to receive ones. :-( I'm now helpless but I hope someone will have patience. Thank you very much, anyway.
    80 pointsBadges:
    report
  • Bunicutza
    I had the same problem. I use Windows XP with apache & PHP. In ODBC Data Source add ODBC DSN (iSeries) in System tab, because the service "apache" runs under "SYSTEM" user and he doesn't know about the user DSN. Once u have add & configured the DSN it should work.
    10 pointsBadges:
    report
  • Belbenj
    Good Day, I'm new to PHP and AS400. I'm developing a PHP project that connects to AS400. BTW, what FresnoBob posted works for me. Thanks FresnoBob!! at first I don't khow what my iSeriesName but I eventually got it.. :D @FresnoBob 1. How can I query a file with multiple members? I tried libraryname.tablename(member) but it doesn't work 2. How can I know if the file exists in the library Thanks in advance!!! Regards, Benj
    50 pointsBadges:
    report
  • mcl
    Multiple members - I think that you may have to create an "alias" to the member. SQL command CREATE ALIAS library/alias_name FOR library/file(member) Regards Mike
    2,740 pointsBadges:
    report
  • Belbenj
    @Mcl Thanks for the reply!! But I'm afraid format : library/file(member) is not working. The format that is accepted is library.file [ period '.' not slash (/) ] and using parentheses is returning an error I tried: CREATE ALIAS library.alias_name FOR library.file(member) --> error because of the parentheses i think Maybe there's command (like NMFMT in unix) that will permit using slashes ( / ) rather than period ( . ) so that it will also recognize the slashes?? Anyway, I'll give it a try again on Monday. I'll keep you posted Thanks again.
    50 pointsBadges:
    report
  • Belbenj
    oopss correction... Maybe there’s command (like NMFMT in unix) that will permit using slashes ( / ) rather than period ( . ) so that it will also recognize the parentheses??
    50 pointsBadges:
    report
  • mcl
    Well... There are different naming formats.. Try this from a green screen command line. STRSQL Then in the SQL screen type CREATE ALIAS and press F4. Fill in the blanks and press ENTER. Regards Mike
    2,740 pointsBadges:
    report
  • Belbenj
    Tried STRSQL and indeed the format is: CREATE ALIAS library/alias_name FOR library/file(member) But for reason unknown to me, and what FresnoBob provided (which works), format accepted is: result=odbc_exec($conn,"SELECT * from library.file where somefield=a value"); Thanks for the help nway.
    50 pointsBadges:
    report
  • graybeard52
    It depends on the naming format specified in the ODBC driver setup. When "SQL Naming" is used, you have to use library.file syntax. When "System naming" is specified you have to use the library/file syntax. Try testing this with the "Run SQL scripts" feature of Navigator.
    3,115 pointsBadges:
    report
  • Belbenj
    Changed it... tested it... AND USING IT!!!!! Thanks a lot GreyBeard and MCL also!!
    50 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