Solved

PHP script works from command line but not from Apache

Posted on 2011-03-01
4
496 Views
Last Modified: 2012-05-11
I'm trying to query a DB2 database under an AS400 but when I run  the script I get an error

The query is:

SELECT * FROM DIVISA WHERE AÑO=11 AND MES=2 AND DIA=28

Open in new window


The error is:

Warning: odbc_exec(): SQL error: [unixODBC][IBM][Controlador ODBC de iSeries Access][DB2 UDB]SQL0104 - Texto de mensaje de error no disponible. El mensaje no se puede traducir satisfactoriamente., SQL state 37000 in SQLExecDirect in /srv/www/htdocs/tipoCambio.php on line 44 Error en odbc_exec

Open in new window


I know the query has a non english character so I guess it is an encoding problem. I've use mb_convert_encoding function to solve it but it was a no go. I also uncoment the AddDefaultCharset On under httpd.conf and add it to the .htaccess file in /srv/www/htdocs to no avail.

The scripts works ok through command line (php /srv/www/htdocs(tipoCambio.php) and under IIS7.

Database is IBM DB2 and I access it through unixODBC.

Any ideas will be much appreciated,

Pancho
0
Comment
Question by:Panchux
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 35011530
You can test this here:
http://www.laprbass.com/RAY_temp_panchux.php

It appears that you may want to enclose the column names in `backticks` -- the Latin1 Character Set works fine.
<?php // RAY_temp_panchux.php
error_reporting(E_ALL);

// TEST NON-ENGLISH CHARACTERS IN COLUMN NAMES IN MySQL

// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// WANT TO RUN A QUERY LIKE THIS:
// DIVISA WHERE AÑO=11 AND MES=2 AND DIA=28


// CREATING THE TABLE
$sql = "CREATE TEMPORARY TABLE divisa  (
        AÑO INT NOT NULL DEFAULT 0,
        MES INT NOT NULL DEFAULT 0,
        DIA INT NOT NULL DEFAULT 0     )";

if (!$res = mysql_query($sql))
{
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// INSERT SOME DATA
$sql = "INSERT INTO divisa ( AÑO, MES, DIA ) VALUES ( 11, 2, 28 )";
if (!$res = mysql_query($sql))
{
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
$sql = "INSERT INTO divisa ( AÑO, MES, DIA ) VALUES ( 11, 2, 28 )";
if (!$res = mysql_query($sql))
{
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}


// QUERY TO FIND THE DATA WE JUST INSERTED
$sql = "SELECT * FROM divisa WHERE AÑO=11 AND MES=2 AND DIA=28";
if (!$res = mysql_query($sql))
{
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
$num = mysql_num_rows($res);
echo "<br/>$num ROWS FOUND";

// SHOW THE VALUES FOR THIS TABLE
$sql = "SHOW CREATE TABLE divisa";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
while ($show_creates = mysql_fetch_assoc($res))
{
    $my_creates[] = $show_creates["Create Table"];
}
var_dump($my_creates);

Open in new window

0
 
LVL 8

Accepted Solution

by:
Panchux earned 0 total points
ID: 35047742
This was of no help at all.
I created a logical view of the table without the non-english characters and it worked.
You can close this question.

Pancho
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 35048160
Glad you've got a working solution.  Please post your solution and accept it as the answer.  That way it will get into the PAQ and others can benefit from your research.

Thanks and regards, ~Ray
0
 
LVL 8

Author Closing Comment

by:Panchux
ID: 35115165
A logical view was the only solution presented that really worked.
0

Featured Post

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question