I'm using:
Windows Server 2003 width IIS.
PHP 5..2.1
SQL Server 2000

I'm trying to do a simple SQL join using a prepared statement with a
bound parameter as a part of the condition.
The execution of the statement fails with an exception saying that I'm
trying to do a compare on text, ntext or image data types, even though
the tables don't contain these types.

Here are the tables and their content:

CREATE TABLE [table1] (
	[id] [int] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [table2] (
	[id] [int] NOT NULL 
) ON [PRIMARY]

INSERT INTO [table1] VALUES(1)
INSERT INTO [table2] VALUES(1)

And here is the PHP code:

<?php
$dbh = new PDO('odbc:Driver={SQL Server}; Server=127.0.0.1; Uid=userid;
Pwd=password; Database=database;');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare("SELECT t1.id, t2.id FROM table1 t1 INNER JOIN
table2 t2 ON (t1.id = ?) AND (t2.id = 1)");
$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->execute();
?>

Error message:

PHP Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[42000]: Syntax error or access violation: 306 [Microsoft][ODBC
SQL Server Driver][SQL Server]The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator. (SQLExecute[306] at ext\pdo_odbc\odbc_stmt.c:133)' in
D:\Website\pdo.php:8 Stack trace: #0 D:\Website\pdo.php(8):
PDOStatement->execute() #1 {main} thrown in D:\Website\pdo.php on line
8

    Actually you have an error in your JOIN syntax - the PDO error message is misleading in this case.

    When you join using ON then you have to name the COLUMNS in the tables to be used for the join.

      Hm, are you sure about that? I've named the collums, it's t1.id and t2.id
      The query works fine in SQL Query Analyzer.

        ON t1.id = t2.id

        You name the columns in each table that are to be used to join the two tables.

        In your query you say ON t1.id=1 AND t2.id=1 - 1 is not a column so can not be used for a join

          Personally I would use

          INNER JOIN table2 USING(id)

          but then I prefer USING to ON because it does not return redundant columns

            I'm sorry but I don't know what you are talking about.
            Of course "1" is not a column, it's a value.
            You don't necessarily need to use colums, it's completely valid to use hardcoded values for joins.
            And btw, "USING" doesn't exist in SQL Server 2000.

            Anyway, rewriting the query like this fails as well:

            $stmt = $dbh->prepare("SELECT * FROM table1 t1 INNER JOIN table2 t2 ON (t1.id = t2.id) AND (t1.id = ?)");
$stmt->bindValue(1, 1, PDO::PARAM_INT);

            However, I believe the JOIN itself is not the problem. Why is PDO talking about ntext and stuff when I'm only using integers?

              It seems like simple subqueries fails aswell.
              The following statement produces the same 'ntext, text, image' exception:

              $stmt = $dbh->prepare("SELECT id FROM table1 WHERE id IN (SELECT id FROM table2 WHERE id = ?)");
$stmt->bindValue(1, 1, PDO::PARAM_INT);

                OK, did not notice it was sql server.

                Sure the join condition can be anything that would work in a where condition, but that always seemed a bit pointless to me. We all get into coding habits and one of mine is to join on columns, and always join explicitly. I have that habit because non-column joins in OUTER joins can be unpredictable. More than that, I go with what works and pre-empts confusion or errors so columns in the join and values in the where: been working for me for 20 years.

                Being as it is sqlserver and I aint up on t-sql I don't know why one of the columns or values is being interpreted as text or whatever.

                  I'm sure this is a bug, it really must be.
                  The query doesn't have anything to do with ntext, text or image types. It's just a simple subquery!
                  So now what? Should I submit the bug to PECL or what?

                    Write a Reply...