PHP ODBC not accepting alias on column name with space

Question

Basically, I need to get some data out of a SQL Server 2005, using PHP.

Previously we used the mssql_* functions, however due to various server issues we are only now able to use odbc_* functions.

The table has various columns whose names have spaces in them, and before it is suggested.... no I cannot change them as this is a totally separate piece of software in another language and it would break it, I am just getting stats out of it.

Anywho, I had previously been accessing these columns by putting their names in square brackets, e.g. [column name] and it worked fine under the mssql_* functions, however when I do this:

$sql = "select top 1 [assessment name] as AssessmentName from bksb_Assessments";
$result = odbc_exec($db, $sql);
while($row = odbc_fetch_array($result))
{
    var_dump($row);
}

It prints the results out as:

'assessment name' => string 'Mathematics E3 Diagnostic' (length=25)

So as you can see it is totally ignoring the alias I've given it and still calling it [assessment name].

But if I run the exact same thing in SQL Server Management Studio Express, it works fine and uses the alias.

I've tried various different combinations, such as quoting the alias, quoting the column, different brackets, etc... but no luck so far.

This isn't a huge issue, as I can change what my script uses to look for "assessment name" in the result array instead of the alias, but it's just a bit annoying that I couldn't work out why this was happening...

Cheers! :)

EDIT:

Actually i don't think the square brackets make a difference, just trying to alias any column isn't working with through php odbc, however I can still do something like CAST(whatever) AS 'alias' and that works fine... just not selecting a column as an alias...? :/

Answer 1

This is by no means a perfect solution, and I would love to learn the proper way to deal with this issue, but in the mean time, add

+''

to each column, after the underlying column name, but before the AS alias and you should be able to circumvent this issue.

Answer 2

While attempting to troubleshoot this issue and determine why some of my team's stored procedures exhibited this behavior, I finally discovered why. What's strange is that we were only experiencing this issue with a few stored procedures, while most returned the the aliases as expected.

It appears that declaring at least one variable, whether or not it is being used, will prevent this particular bug from occurring. The variable can be of any type.

If you're just executing a SELECT query without using a stored procedure, use a DECLARE statement to define a variable before or after the statement:

// This works...
DECLARE @ignore TINYINT; SELECT EmployeeID AS employee_id FROM Employee;

// So does this...
SELECT EmployeeID AS employee_id FROM Employee; DECLARE @ignore TINYINT;

Now if you're running into this issue with your stored procedures, you can take the same approach above by defining a variable anywhere in the body.

One other thing I noticed is that if the stored procedure is defined with parameters, if your the parameter gets set or evaluated in something like an if statement, this bug will also not occur. For example,

// This works...
CREATE PROC get_employee(
  @employee_id VARCHAR(16) = NULL
)
AS
  IF (@employee_id IS NOT NULL)
  BEGIN
    SELECT FirstName AS first_name FROM Employee WHERE EmployeeID = @employee_id;
  END
GO

Using SET:

// So does this...
CREATE PROC get_employee(
  @employee_id VARCHAR(16) = NULL,
  @ignore TINYINT
)
AS
  SET @ignore = NULL;

  SELECT FirstName AS first_name FROM Employee WHERE EmployeeID = @employee_id;
GO

I still don't quite understand the cause or nature of the bug; but these workarounds, similar to the type-casting hack above, appear to get around this annoying bug.

It's probably also worth mentioning that we didn't encounter this issue in Ubuntu 14.04 and PHP 5.4. As soon as we upgraded to Ubuntu 18.04 and PHP 7.2, that was when we began experiencing this issue. In both instances, we do have FreeTDS configured to use version 7.1, which is why we're baffled by this particular bug.

Answer 3

@dearsina's approach in fact seems to be one of the few ways to handle this bug.

However, this solution is not applicable to all datatypes. So for me, the only way to cover all needed columns was to CAST() the relevant column (where initially c.State would be of datatype bit):

SELECT
    CAST(c.State AS nvarchar) AS 'customer_state'
FROM
    customers AS c;

as else you might get an error message similar to

The data types bit and varchar are incompatible in the add operator.

Reference on CASTing large data sets: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/643b6eda-fa03-4ad3-85a1-051f02097c7f/how-much-do-cast-statements-affect-performance?forum=transactsql

---

Some further finding on this topic is a recently (November 2017) raised bug report at bugs.php.net claiming that the problem is related to FreeTDS: https://bugs.php.net/bug.php?id=75534. This bug report contains an inofficial (and at least from my end untested) patch:

diff --git a/ext/odbc/php_odbc_includes.h b/ext/odbc/php_odbc_includes.h
index 93e2c96..8451bcd 100644
--- a/ext/odbc/php_odbc_includes.h
+++ b/ext/odbc/php_odbc_includes.h
@@ -292,18 +292,16 @@ void odbc_sql_error(ODBC_SQL_ERROR_PARAMS);

 #define PHP_ODBC_SQLCOLATTRIBUTE SQLColAttribute
 #define PHP_ODBC_SQLALLOCSTMT(hdbc, phstmt) SQLAllocHandle(SQL_HANDLE_STMT, hdbc, phstmt)
-
-#define PHP_ODBC_SQL_DESC_NAME SQL_DESC_NAME
 #else
 #define IS_SQL_LONG(x) (x == SQL_LONGVARBINARY || x == SQL_LONGVARCHAR)

 #define PHP_ODBC_SQLCOLATTRIBUTE SQLColAttributes
 #define PHP_ODBC_SQLALLOCSTMT SQLAllocStmt
-
-#define PHP_ODBC_SQL_DESC_NAME SQL_COLUMN_NAME
 #endif
 #define IS_SQL_BINARY(x) (x == SQL_BINARY || x == SQL_VARBINARY || x == SQL_LONGVARBINARY)

+#define PHP_ODBC_SQL_DESC_NAME SQL_DESC_LABEL
+
 PHP_ODBC_API ZEND_EXTERN_MODULE_GLOBALS(odbc)
 #define ODBCG(v) ZEND_MODULE_GLOBALS_ACCESSOR(odbc, v)