[SOLVED] PHP 7 and mssql_connect

[Vieri]

Hi,

I used connect to an SQL server with the PHP functions mssql_*.

For some reason, they got removed, and now I need to update my PHP code somehow.

I've vaguely heard of sqlsrv, but it seems to require an ODBC driver which is not yet available for Linux.

This is my PHP version:

Code: Select all

dev-lang/php-7.0.19::gentoo was built with the following:
USE="acl apache2 berkdb bzip2 calendar cli crypt ctype curl embed fileinfo filter ftp gd gdbm hash iconv ipv6 json kerberos ldap mssql mysql mysqli nls odbc opcache phar posix postgres readline session simplexml soap sockets ssl tokenizer unicode xml xmlreader xmlrpc xmlwriter zip zlib -bcmath -cdb -cgi -cjk -coverage -debug -enchant -exif (-firebird) -flatfile -fpm -gmp -imap -inifile -intl -iodbc -ldap-sasl -libedit (-libressl) -mhash -oci8-instant-client -pcntl -pdo -phpdbg -qdbm -recode (-selinux) -sharedmem -snmp -spell -sqlite -systemd -sysvipc -threads -tidy -truetype -wddx -webp -xpm -xslt" ABI_X86="(64)"

Can anyone please let me know if there's an easy way to connect to an MS SQL server from a Gentoo PHP script like it was before with mssql_*?

Maybe there's a missing Gentoo ebuild for http://pecl.php.net/package/sqlsrv?

Thanks,

Vieri

[kitsunenokenja]

The old-fashioned procedural functions for database calls were deprecated and by the release of version 7, were stripped out entirely as a result. You need to migrate to using PDO class. Also, years ago there used to be some sort of driver included to connect to MSSQL servers but not anymore. You will need to acquire the Linux PHP7 driver separately from the vendor as well as enable the appropriate use flags when compiling PHP to use it.

[Vieri]

That's a bummer.
OSS is always changing, and that's good. However, backward compatibility is not it's strength.

I was wondering if I could find an easier way out by using freetds and unixodbc (then use odbc_* functions in PHP).

FreeTDS seems OK:

Code: Select all

# TDSVER=7.1 tsql -S sqlserver -U my_user -P my_pass -D my_db
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "ISO-8859-1"
Setting my_db as default database in login packet
1>  select  ID from pro_Prof where ( Active = 1 and User = 'me' )
2> go
ID
X111000K
(1 row affected)
1> quit

unixODBC:

Code: Select all

# cat /etc/unixODBC/odbcinst.ini
[FreeTDS]
Description=Freetds
Driver=/usr/lib64/libtdsodbc.so
UsageCount=1
Threading=2

Code: Select all

# cat /etc/unixODBC/odbc.ini
[sqlserver]
Driver = FreeTDS
Description = MS SQL server
Trace = No
Server = sqlserver
Port = 1433
Database =  my_db
TDS_Version = 7.1

Code: Select all

# odbcinst -i -d -f /etc/unixODBC/odbcinst.ini
odbcinst: Driver installed. Usage count increased to 2.
    Target directory is /etc/unixODBC

Code: Select all

# odbcinst -q -d
[FreeTDS]

Code: Select all

# isql -v sqlserver my_user my_pass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

I can run SQL queries from the isql prompt.

However, my PHP script doesn't seem to work:

Code: Select all

# cat test.php
<?php
$con = odbc_connect("sqlserver", "my_user", "my_pass");
if ($con === false) die("ERROR: cannot connect to database.");

$sql= "select  ID from pro_Prof where ( Active = 1 and User = 'me' )";
$rs = odbc_exec ($con, $sql);
if ($rs === false) die("ERROR: cannot query database.");
$numrows = odbc_num_rows($rs);

echo "numrows = ".$numrows."\n";
$row = odbc_fetch_array($rs);
if (!$row) die("ERROR: cannot fetch table data.");
echo "row: ".$row[0]."\n";
odbc_free_result($rs);
odbc_close($con);
?>

The output I see on a browser is:

Code: Select all

numrows = -1
row: 

Nothing in the logs:

Code: Select all

# tail -n 200 /var/log/apache2/php_error_log

I don't understand why apache/PHP can connect to the SQL server (apache user can obviously find the DSN), but it doesn't retreive the expected records.

I'm just one step from getting all of this to work...
How can I test this further?

[Vieri]

Hmm... Never mind. My PHP code was wrong. I was accessing the array by integer, not by name, but the array is associative.
So using

Code: Select all

$row['ID']

works fine.

Still, odbc_num_rows always returns -1, no matter what so I guess I can't count on the result of that call.