Phonebook: partial results for MSSQL queries

[gsanchietti]

Sample scripts accessing MSSQL doesn't work anymore after upstream release of freetds-1.1.11.

Steps to reproduce

• Create a test script which access a MSSQL server and uses column aliases (eg. https://github.com/nethesis/nethserver-phonebook-mysql/blob/master/root/usr/share/phonebooks/samples/business_nts.php
  )
• Update from freetds-0.95.81 to freetds-1.1.11

Expected behavior

Data from aliased column must be accessible from PHP script.

Actual behavior

Data from aliased column are not accessible from PHP script.

Components

• freetds-1.1.11-1.el7.x86_64
• nethserver-phonebook-mysql-2.1.2-1.ns7.noarch

See also

Upstream issue: https://bugs.php.net/bug.php?id=75534

[gsanchietti]

Workaround

There is no a global configuration which magically can fix all existing queries (tried also changing TDS_Version inside ODBC config, see also this and this).

Each query must be manually fixed.

Given the following query, field1 is not accessible from PHP script:

select an_descr1 as field1 from ANAGRA

Tested workarounds:

• add +'' before the as command:

  select an_descr1+'' as field1 from ANAGRA
  

• declare an unused variable:

  declare @ignore tinyint; select an_descr1 as field1 from ANAGRA
  

• add a cast:

  select cast(an_descr1 as varchar(255)) as field1 from ANAGRA
  

Otherwise, remove the column rename and access directly the original field name:

select an_descr1 from ANAGRA

This is a PHP test script:

<?php
$dsn="yourdsn";
$user="youruser";
$pass="yourpass";
$handle = odbc_connect($dsn,$user,$pass);
if (!$handle) {
    die(odbc_errormsg());
}
$query = "select an_descr1 as field1 from ANAGRA";
$result = odbc_exec($handle,$query);
while ($row = odbc_fetch_array($result)){
    print_r($row);
}

[gsanchietti]

This is a semi-automatic workaround, it creates also a backup file inside /usr/share/phonebooks/:

export FILE=<filename>; cp /usr/share/phonebooks/scripts/$FILE /usr/share/phonebooks/scripts/$FILE.old; chmod a-x /usr/share/phonebooks/scripts/$FILE.old; sed -i 's/\$query="/\$query="DECLARE @ignore TINYINT;/g' /usr/share/phonebooks/scripts/$FILE
/usr/share/phonebooks/phonebook

Substitute <filename> with a script filename, like business_nts.php. Example:

export FILE=business_nts.php; cp /usr/share/phonebooks/scripts/$FILE /usr/share/phonebooks/scripts/$FILE.old; chmod a-x /usr/share/phonebooks/scripts/$FILE.old; sed -i 's/\$query="/\$query="DECLARE @ignore TINYINT;/g' /usr/share/phonebooks/scripts/$FILE
/usr/share/phonebooks/phonebook

In this case, to verify what is changed:

diff -u /usr/share/phonebooks/scripts/business_nts.php.old /usr/share/phonebooks/scripts/business_nts.php

When the workaround has been applied, lunch again /usr/share/phonebooks/phonebook to force the synchronization.

[gsanchietti]

I strongly suggest to create new sample scripts with improved syntax.
Also avoid to load all results in RAM and loop on them twice!

[Stell0]

New sample scripts for Business and Gamma.
Now PHP PDO is used for ODBC queries too. results are no more loaded in RAM, "cast" to varchar to avoid errors should now be useless and performance are improved

[nethbot]

in 7.6.1810/nethesis-testing:

• nethserver-phonebook-mysql-2.1.2-1.1.g408fdeb.ns7.noarch.rpm x86_64

[nethbot]

in 7.7.1908/nethesis-updates:

• nethserver-phonebook-mysql-2.1.3-1.ns7.noarch.rpm x86_64