ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> ODBC General Issues >> IIS 7 Gurus
http://www.istechforum.com/YaBB.pl?num=1302721581

Message started by Kelloggs on 04/13/11 at 12:06:21

Title: IIS 7 Gurus
Post by Kelloggs on 04/13/11 at 12:06:21

I have a little problem.  This PHP code works fine on Apache/UBUNTU but not in IIS7/Windows 2008

//SQL Script
$sql = "SELECT MTWO_WIP_SSTART, BKIC_PROD_TYPE
FROM BKICMSTR INNER JOIN WORKORD ON BKICMSTR.BKIC_PROD_CODE = WORKORD.MTWO_WIP_CODE
WHERE MTWO_WIP_PROJ LIKE 'W%' AND MTWO_WIP_STATUS = 'R' ORDER BY MTWO_WIP_SSTART ASC";

//conneccion
$conn=odbc_connect('DBA','','');
if (!$conn)
 {exit("Connection Failed: " . $conn);}
$rs=odbc_exec($conn,$sql);
if (!$rs)
 {exit("Error in SQL");}
?>

I thing is a pervasive issue, because the following works fine to connect to an MS SQL Express 2008 on the same server

$ID = $_REQUEST['ID'];

//--Get Value
$conn=odbc_connect('UMCDB','user_all','nopassuser');
$var_sql="SELECT web_hits FROM web_stats WHERE web_index=$ID";
$rs=odbc_exec($conn,$var_sql);
$new_val=odbc_result($rs,"web_hits")+1;

//--Update Record
//$var_sql="UPDATE web_stats SET web_hits=$new_val WHERE web_index=$ID";
//$rs=odbc_exec($conn,$var_sql);


>:(

Kelloggs

Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/14/11 at 09:58:28

Anyone?

:'(

Help!!!

Title: Re: IIS 7 Gurus
Post by GasGiant on 04/15/11 at 05:36:27

And you did install the PSQL client on the ISS server and configure the DSN so that it passes the Test?

Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/18/11 at 12:11:33


GasGiant wrote:
And you did install the PSQL client on the ISS server and configure the DSN so that it passes the Test?

Yes, and Yes.
It has to do something with PHP, because ASP works fine

thanks

:)

Title: Re: IIS 7 Gurus
Post by GasGiant on 04/18/11 at 12:39:16

So, what part doesn't work? Do you get an error or does it just not update the table?

Do you have odbc_autocommit() set? Cuz I don't see a commit for the UPDATE.

Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/19/11 at 07:45:04

It just does not show any records. There is not error.
about the odbc_autocommit()  I havent includ it on my code
should I? where?

thanks for you help

Kelloggs

Title: Re: IIS 7 Gurus
Post by GasGiant on 04/19/11 at 08:28:29

If you don't set auto-commit to true then you need to specifically call commit.

Normally you would end a page with something like...

odbc_commit($conn);
odbc_close_all();

I like to make sure that auto-commit is off so that, if the code fails somehow, no INSERT or UPDATE is attempted.  So, when I created my connection, I do this...

if(!$conn = odbc_connect('DBA','','')) {exit("Connection Failed: ".odbc_errormsg());}
$start_trans = odbc_autocommit($conn, 0);

Which sets auto-commit to false. That way I never have to guess where auto-commit is on by default in the setup.

Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/19/11 at 10:15:43

Same result. It shows no records.
but I now that php is working fine. the problem is PHP's ODBC.

Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/19/11 at 10:15:49

Same result. It shows no records.
but I know now that php is working fine. the problem is PHP's ODBC.

Title: Re: IIS 7 Gurus
Post by GasGiant on 04/19/11 at 12:21:59

How are you looking at the results? Are you using odbc_fetch_row($rs) in a while statement and printing out the odbc_result($rs, "bkic_prod_type"), etc.?

Even though there are no name collisions, I alias the tables and specify. It keeps me out of trouble and it seems to perform just a little better. Ymmv.

SELECT w.mtwo_wip_sstart, b.bkic_prod_type  
   FROM BKICMSTR b INNER JOIN WORKORD w
       ON b.bkic_prod_code = w.mtwo_wip_code
   WHERE w.mtwo_wip_proj LIKE 'W%'
       AND w.mtwo_wip_status = 'R'
   ORDER BY w.mtwo_wip_sstart

Does this same query work in MS Query (Excel) or CR? Is the DSN named "DBA" working on the web server machine? Does it point to the right company?

If you just run

SELECT * FROM BKICMSTR

does it return rows?


Title: Re: IIS 7 Gurus
Post by Kelloggs on 04/19/11 at 12:35:48

YEAHHHH!!!! It works!!!

That was the problem.

Thank you VERY MUCH!!!!

SELECT w.mtwo_wip_sstart, b.bkic_prod_type  
   FROM BKICMSTR b INNER JOIN WORKORD w  
      ON b.bkic_prod_code = w.mtwo_wip_code
   WHERE w.mtwo_wip_proj LIKE 'W%'  
      AND w.mtwo_wip_status = 'R'  
   ORDER BY w.mtwo_wip_sstart";


;D

Kelloggs

Title: Re: IIS 7 Gurus
Post by GasGiant on 04/21/11 at 05:01:54

Yay! :-)

ISTech Support Forum » Powered by YaBB 2.1!
YaBB © 2000-2005. All Rights Reserved.