2. PHPRunner General questions
  3. [SOLVED]  AS400 SQL Problem
This topic is locked
[SOLVED]

 AS400 SQL Problem

 		8/14/2007 2:39:47 PM
PHPRunner General questions
F
 frocco author

PHPR 4.1 generates this error using this sql

This works fine in java.
Thanks
Frank
Error description:

odbc_exec() [function.odbc-exec]: SQL error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 20 was not valid. Valid tokens: + - AS <IDENTIFIER>., SQL state 37000 in SQLExecDirect
Error File:

C:\Inetpub\wwwroot\emp\include\dbconnection.php
SQL Query:

SELECT top 20 BSYDTAA.BSYPEMP."EMHSP#" AS HSP, BSYDTAA.BSYPEMP.EMYLNM, BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4, BSYDTAC.BPRPPCP.PCLDDS, BSYDTAA.BSYPEMP."EMEMP#" AS EMEMP, BSYDTAA.BSYPEMP."EMWPH#" AS WPHONE, BSYDTAA.BSYPEMP.EMEADD, BSYDTAA.BSYPEMP.EMOTSH FROM BSYDTAA.BSYPEMP, BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP."EMHSP#" = BSYDTAC.BPRPPCP."PCHSP#" AND BSYDTAA.BSYPEMP.EMDEP4 = BSYDTAC.BPRPPCP.PCDEP4 AND (BSYDTAA.BSYPEMP."EMHSP#" IN (1,5)) AND (BSYDTAA.BSYPEMP.EMDOT=0) ORDER BY EMYLNM
F
 		frocco author 8/15/2007

Anyone know how I can get arround this?
Thanks
Frank

PHPR 4.1 generates this error using this sql

This works fine in java.
Thanks
Frank
Error description:

odbc_exec() [function.odbc-exec]: SQL error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 20 was not valid. Valid tokens: + - AS <IDENTIFIER>., SQL state 37000 in SQLExecDirect
Error File:

C:\Inetpub\wwwroot\emp\include\dbconnection.php
SQL Query:

SELECT top 20 BSYDTAA.BSYPEMP."EMHSP#" AS HSP, BSYDTAA.BSYPEMP.EMYLNM, BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4, BSYDTAC.BPRPPCP.PCLDDS, BSYDTAA.BSYPEMP."EMEMP#" AS EMEMP, BSYDTAA.BSYPEMP."EMWPH#" AS WPHONE, BSYDTAA.BSYPEMP.EMEADD, BSYDTAA.BSYPEMP.EMOTSH FROM BSYDTAA.BSYPEMP, BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP."EMHSP#" = BSYDTAC.BPRPPCP."PCHSP#" AND BSYDTAA.BSYPEMP.EMDEP4 = BSYDTAC.BPRPPCP.PCDEP4 AND (BSYDTAA.BSYPEMP."EMHSP#" IN (1,5)) AND (BSYDTAA.BSYPEMP.EMDOT=0) ORDER BY EMYLNM
Admin 8/15/2007

Frank,
I would suggest to use ASPRunnerPro for this purpose. PHPRunner ODBC connection works with MS Access and SQL Server only.
F
 		frocco author 8/16/2007

Hi Sergey,
thank you, I do not have a copy of ASPRunnerPro.
Regards,
Frank

Frank,

I would suggest to use ASPRunnerPro for this purpose. PHPRunner ODBC connection works with MS Access and SQL Server only.
Admin 8/16/2007

Why don't you download a trial version and give it a try?
A
 		axidavid 8/20/2007

hi,
i'm using phprunner with as400 (with odbc)
you just have to modify a couple of php file to make it work :
look for "addtop" in include/commonfunctions.php : it adds a "top 20" clause on the select

modify it like that
function AddTop($strSQL, $n)

{

return $strSQL;

}
the only drawback is you have to make this modification everytime you run the build command in phprunner
hope this help
David
A
 		axidavid 8/20/2007

Why don't you download a trial version and give it a try?


hi Serguei
is the as400 db2 integration in the plan for future release of phpRunner ????
A
 		axidavid 8/20/2007

Why don't you download a trial version and give it a try?


hi Serguei
is the as400 db2 integration in the plan for future release of phpRunner ????
F
 		frocco author 8/20/2007

Thanks,
I am finding in 4.1 build (288) there are more places that need to be changed.

ajax lookup in the search and replacing the default wrappers [ and ]
Regards,
Frank

hi,

i'm using phprunner with as400 (with odbc)
you just have to modify a couple of php file to make it work :
look for "addtop" in include/commonfunctions.php : it adds a "top 20" clause on the select

modify it like that
function AddTop($strSQL, $n)

{

return $strSQL;

}
the only drawback is you have to make this modification everytime you run the build command in phprunner
hope this help
David
J
 		Jane 8/21/2007

Frank,
I wasn't able to reproduce this issue.
Please give me more detailed description on how to reproduce it.
F
 		frocco author 8/21/2007

Hi Jane,
Regenerated with build 295
In generated file dbcommon.php

$strLeftWrapper="[";

$strRightWrapper="]";
Ajax search wraps the FROM Database like this:
[BSYDTAA.BSYPEMP]

This causes an error because the [] is not needed for AS400 SQL
Also:
odbc_connect() [function.odbc-connect]: SQL error: [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user on server SEH400 is not correct, Password length = 4, Prompt Mode = Never, System IP Address = 10.xx.xx.xxx, SQL state 28000 in SQLConnect
It seems the username and/or password is not being passed.

I can edit the function db_connect() in dbconnection.php and hard code the uid and pwd and this message goes away.
Also:
odbc_exec() [function.odbc-exec]: SQL error: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token 20 was not valid. Valid tokens: + - AS <IDENTIFIER>., SQL state 37000 in SQLExecDirect
SELECT top 20 BSYDTAA.BSYPEMP."EMHSP#" AS HSP,

top 20 is incorrect for AS400 queries. Should be FETCH FIRST 20 ROWS ONLY
HTH
Frank

Frank,

I wasn't able to reproduce this issue.
Please give me more detailed description on how to reproduce it.