php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #58429 Syntax error with more than one Database in SQL
Submitted: 2008-11-25 05:16 UTC Modified: 2009-04-21 12:07 UTC
From: matthias dot djihangiroff at persona dot de Assigned:
Status: Closed Package: PDO_INFORMIX (PECL)
PHP Version: 5.2.5 OS: Open Suse 11 x64
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: matthias dot djihangiroff at persona dot de
New email:
PHP Version: OS:

 

 [2008-11-25 05:16 UTC] matthias dot djihangiroff at persona dot de
Description:
------------
Syntax Error in SQL with more than one Database in one SQL

Reproduce code:
---------------
Select field
 FROM database1:field1 alias1, outer database2:field2 alias2

Expected result:
----------------
SQL Result


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-11-25 05:25 UTC] matthias dot djihangiroff at persona dot de
Sorry, it must read:

FROM database1:table1 alias1, outer database2:table2 alias2
 [2008-11-26 07:21 UTC] abhargav at in dot ibm dot com
Hi,

Can you send me the error message that you are getting when you execute this query?

Regards,
Ambrish Bhargava
 [2008-11-26 08:31 UTC] matthias dot djihangiroff at persona dot de
Sure:

(
    [0] => 42000
    [1] => -201
    [2] => [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (SQLPrepare[-201] at /tmp/pear/download/PDO_INFORMIX-1.2.6/informix_driver.c:131)
)

Thats the content of the PDO Exception
 [2009-03-06 02:27 UTC] abhargav at in dot ibm dot com
Hi,

I tried a very simple sample for this case:

--TEST--
pdo_informix: Dual database test
--SKIPIF--
<?php require_once('skipif.inc'); ?>
--FILE--
<?php
	require_once('fvt.inc');
	class Test extends FVTTest
	{
		public function runTest()
		{
			$this->connect();
			$this->prepareDB();
			
			$sqlselect = "SELECT * FROM testdb1:TEST_15152_1 TAB1, testdb2:TEST_15152_2 TAB2";

			$sth = $this->db->prepare($sqlline);
			$sth->execute();
			while ($row = $sth->fetch()) {
				print_r($row);
			}
		}
	}

	$testcase = new Test();
	$testcase->runTest();
?>

This went through successfully. 

But when I change the query from "SELECT * FROM testdb1:TEST_15152_1 TAB1, testdb2:TEST_15152_2 TAB2" to "SELECT * FROM testdb1:TEST_15152_1 TAB1, OUTER testdb2:TEST_15152_2 TAB2" I got error. The same is true when I tried this on server directly. You are also using OUTER keyword in your case. I suggest you to remove the OUTER keyword and then run your application.

Regards,
Ambrish Bhargava
 [2009-03-06 02:42 UTC] abhargav at in dot ibm dot com
Hi,

Try this standalone sample:

<?php
	abstract class FVTTest {
		public $default_dsn = "informix:testdb1";
		public $default_user = "informix";
		public $default_pass = "password";
		
		public $db = null;
		public $dsn = null;
		public $user = null;
		public $pass = null;

		public function __construct( $_dsn = null , $_user = null , $_pass = null ) {
			if( $_dsn == null ) {
				$this->dsn = getenv('PDOTEST_DSN');
				if ($this->dsn==null) $this->dsn = $this->default_dsn;
				
			} else {
				$this->dsn = $_dsn;
			}

			if( $_user == null ) {
				$this->user = getenv('PDOTEST_USER');
				if ($this->user==null) $this->user = $this->default_user;
			} else {
				$this->user = $_user;
			}

			if( $_pass == null ) {
				$this->pass = getenv('PDOTEST_PASS');
				if ($this->pass==null) $this->pass = $this->default_pass;
			} else {
				$this->pass = $_pass;
			}
		}

		public function connect($autoCommit=true) {
			$this->db = new PDO($this->dsn,$this->user,$this->pass,array( PDO::ATTR_AUTOCOMMIT => $autoCommit ));
			$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->db->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);
			$this->db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
			return $this->db;
		}
	}
	class Test extends FVTTest
	{
		public function runTest()
		{
			$this->connect();
			$this->prepareDB();
			
			$sqlselect = "SELECT * FROM testdb1:TEST_15152_1 TAB1, testdb2:TEST_15152_2 TAB2";

			$sth = $this->db->prepare($sqlline);
			$sth->execute();
			while ($row = $sth->fetch()) {
				print_r($row);
			}
		}
	}

	$testcase = new Test();
	$testcase->runTest();
?>
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sat May 25 11:01:26 2019 UTC