symbol not found in flat namespace '_SQLAllocHandle'

[nggepe]

Please check the FAQ (frequently-asked questions) first. If you have other questions or something to report, please address the following (skipping questions might delay our responses):

PHP version

PHP 8.0.19 (cli) (built: May 13 2022 09:49:28) ( NTS )

PHP SQLSRV or PDO_SQLSRV version
sqlsrv-5.11.0 pdo_sqlsrv-5.11.0

Microsoft ODBC Driver version

unixODBC 2.3.12 DRIVERS............: /opt/homebrew/etc/odbcinst.ini SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources USER DATA SOURCES..: /Users/gilangpratama/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8

SQL Server version

I'm installed using docker with latest version

Client operating system

macOS Ventura 13.3.1

Problem description

I followed the doc instruction for mac until this section

sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install sqlsrv
sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install pdo_sqlsrv

the output is like this

Build process completed successfully
Installing '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/pdo_sqlsrv.so'
install ok: channel://pecl.php.net/pdo_sqlsrv-5.11.0
configuration option "php_ini" is not set to php.ini location
You should add "extension=pdo_sqlsrv.so" to php.ini

I think that is a success install. but when I write this code in php.ini:

extension=sqlsrv.so
extension=pdo_sqlsrv.so

The php extension is not installed on my php -m and it show me warning like this

PHP Warning:  PHP Startup: Unable to load dynamic library 'sqlsrv.so' (tried: /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so (dlopen(/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so, 0x0009): symbol not found in flat namespace '_SQLAllocHandle'), /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so (dlopen(/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so, 0x0009): tried: '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so' (no such file), '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so' (no such file), '/sqlsrv.so.so' (no such file), '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so' (no such file), '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so.so' (no such file))) in Unknown on line 0

Please, help me, I've tried to re-install or rename the extension from extension=sqlsrv.so tobe extension=sqlsrv and it still not working.

[v-makouz]

Does the file exist? /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so I mean? If it exists, then it would suggest that there be some dependency issue or architecture mismatch (ARM vs x86)

[nggepe]

yes, it is exist. here is my screenshots

[v-makouz]

It looks like the PHP driver can't find/load unixODBC. Is this an ARM(M1) Mac or x86? I think I saw a similar issue on ARM Macs when using pyODBC.

[nggepe]

I'm using ARM(M2). Is there any solution to solve this issue?

[v-makouz]

Do you have an ODBC driver installed? Like msodbcsql18 and if so, does it work without pyODBC?

Something like this, assuming ODBC Driver 18 is installed: isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=...;UID=...;PWD=..."

[nggepe]

I followed the doc instruction for mac. And I install the unixODBC 2.3.12. do I have to install pyodbc?

I've tried isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost;UID=sa;PWD=..." but the output is like this

[08001][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0, 1.1, or 3.0 is installed]
[08001][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
[ISQL]ERROR: Could not SQLDriverConnect

I've tried to use the azure data studio and it is connected

am I wrong with isql command line?

[v-makouz]

No pyODBC is a separate thing, but people were having a similar sounding issue there. So it seems like the ODBC driver is failing to find or load OpenSSL. Can you post the rsult of following commands? ls -l /opt/homebrew/opt/openssl/lib/ and ls -l /opt/local/lib/

[nggepe]

No such file or directory, do I have to install the openssl?

gilangpratama@Gilangs-MacBook-Air nodejs-projects % ls -l /opt/homebrew/opt/openssl/lib/
ls: /opt/homebrew/opt/openssl/lib/: No such file or directory

gilangpratama@Gilangs-MacBook-Air nodejs-projects % ls -l /opt/local/lib/
ls: /opt/local/lib/: No such file or directory

[v-makouz]

How was the ODBC Driver 18 (msodbcsql18) installed? Was it via Homebrew? If you run odbcinst -j it should show something like:

DRIVERS............: /path/to/odbcinst.ini
SYSTEM DATA SOURCES: /path/to/odbc.ini

Can you tell me what's in the odbcinst.ini (cat /path/to/odbcinst.ini)?

It's strange that the driver is there, but a compatible OpenSSL isn't, I'll ask around on my end about it as well.

[nggepe]

Yes I installed the unixODBC. here is my cli result

gilangpratama@Gilangs-MacBook-Air nodejs-projects % odbcinst -j
unixODBC 2.3.12
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/gilangpratama/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

gilangpratama@Gilangs-MacBook-Air nodejs-projects % cat /opt/homebrew/etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib
UsageCount=1

[v-makouz]

Curious, given that OpenSSL is a dependency of ODBC Driver 18, so how was it installed without it? Or was it deleted afterwards? Anyway you can try installing it with brew install openssl and see if it fixes that isql command.

[nggepe]

I ran brew install openssl and ran the isql command. here is the output

gilangpratama@Gilangs-MacBook-Air nodejs-projects % brew install openssl
Running `brew update --auto-update`...
Installing from the API is now the default behaviour!
You can save space and time by running:
  brew untap homebrew/core
==> Auto-updated Homebrew!
Updated 6 taps (jandedobbeleer/oh-my-posh, homebrew/cask-versions, homebrew/cask-fonts, homebrew/services, homebrew/core and homebrew/cask).
==> New Formulae
apify-cli                       codelimit                       libjcat                         python-lxml                     risor
bazel-diff                      counts                          libxmlb                         python-markupsafe               roadrunner
bazel-remote                    goread                          mariadb@11.0                    python-mutagen                  terraform-graph-beautifier
cargo-all-features              hyfetch                         meson-python                    python-packaging                toxiproxy
cargo-binutils                  imgdiet                         mgis                            python-pycurl                   udp2raw-multiplatform
cargo-deps                      imgdiff                         orbiton                         python-pyparsing                vunnel
cdxgen                          kor                             python-click                    python-pyproject-hooks
cloud-sql-proxy                 ldid-procursus                  python-flit-core                python-pytz
==> New Casks
hypercal               piphero                simple-web-server      sparkplate             updf                   viso                   whatsapp-legacy

You have 12 outdated formulae and 1 outdated cask installed.

Warning: openssl@3 3.1.2 is already installed and up-to-date.
To reinstall 3.1.2, run:
  brew reinstall openssl@3
gilangpratama@Gilangs-MacBook-Air nodejs-projects % isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=localhost;UID=sa;PWD=..."
[08001][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0, 1.1, or 3.0 is installed]
[08001][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
[ISQL]ERROR: Could not SQLDriverConnect

I think it is really weird. Now, I have 2 openssl packages 👀

gilangpratama@Gilangs-MacBook-Air nodejs-projects % brew list
==> Formulae
autoconf    gcc     gmp     isl     libtool     mpfr        openssl@1.1 postgresql@15   unixodbc
automake    gdbm        go      krb5        lz4     msodbcsql18 openssl@3   python@3.10 xz
ca-certificates gettext     go@1.19     lcov        m4      mssql-tools18   pcre2       readline    zlib
cmake       git     icu4c       libmpc      mpdecimal   oh-my-posh  pkg-config  sqlite      zstd

==> Casks
font-fira-code  wine-stable

what should I do now?

[v-makouz]

I suspect this may be an architecture mismatch (ARM ODBC Driver but x86 OpenSSL, or something like that). Can you check a few things?

The location of x86 OpenSSL

ls -l /usr/local/opt/openssl/lib/

Which arch the ODBC driver is

ls -l /opt/homebrew/lib/libmsodbcsql.18.dylib
lipo -archs /opt/homebrew/lib/libmsodbcsql.18.dylib

And which arch the PHP driver is

ls -l /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
lipo -archs /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so

[nggepe]

sure

The location of x86 OpenSSL

gilangpratama@Gilangs-MacBook-Air ~ % ls -l /usr/local/opt/openssl/lib/
ls: /usr/local/opt/openssl/lib/: No such file or directory

Which arch the ODBC driver is

gilangpratama@Gilangs-MacBook-Air ~ % ls -l /opt/homebrew/lib/libmsodbcsql.18.dylib
lrwxr-xr-x  1 gilangpratama  admin  56 Aug 25 08:41 /opt/homebrew/lib/libmsodbcsql.18.dylib -> ../Cellar/msodbcsql18/18.3.1.1/lib/libmsodbcsql.18.dylib

gilangpratama@Gilangs-MacBook-Air ~ % lipo -archs /opt/homebrew/lib/libmsodbcsql.18.dylib
arm64

And which arch the PHP driver is

gilangpratama@Gilangs-MacBook-Air ~ % ls -l /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
-rw-r--r--  1 root  admin  354984 Aug 25 13:48 /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so

gilangpratama@Gilangs-MacBook-Air ~ % lipo -archs /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
x86_64

why the the sqlsrv.so to be x86_64 ?

[v-makouz]

I'll have to look into it, I'm not too familiar with how pecl works, but it looks like it grabbed wrong .so files

[v-makouz]

Is there a chance you could run the pecl install commands again with a -v option and save the output (might need to uninstall the current PHP driver before)? It is supposed to build the driver on the machine from source, so I'm surprised it would be building an x86 version on an ARM...

[nggepe]

can you guide me how to install commands with a -v?

I just following this tutorial

https://learn.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver16#installing-on-macos

[v-makouz]

I'm looking into it, see if I can figure out why builds the wrong one

[nggepe]

hi, is there any update on it?

[nggepe]

I tried to reinstall, it is success but, there is a warning that maybe useful for you to debug this lib.

/private/var/folders/zz/zyxvpxvq6csfxvn_n0000000000000/T/pear/temp/sqlsrv/conn.cpp:160:84: warning: format specifies type 'long' but the argument has type 'zend_long' (aka 'long long') [-Wformat]
        snprintf(temp_str, MAX_CONN_VALSTRING_LEN, "%s={%ld};", option->odbc_name, Z_LVAL_P(value));
                                                        ~~~                        ^~~~~~~~~~~~~~~
                                                        %lld
/Applications/XAMPP/xamppfiles/include/php/Zend/zend_types.h:796:28: note: expanded from macro 'Z_LVAL_P'
#define Z_LVAL_P(zval_p)                        Z_LVAL(*(zval_p))
                                                ^~~~~~~~~~~~~~~~~
/Applications/XAMPP/xamppfiles/include/php/Zend/zend_types.h:795:25: note: expanded from macro 'Z_LVAL'
#define Z_LVAL(zval)  

[v-makouz]

I take it even after the reainstall sqlsrv.so is still x86?

[nggepe]

Yes, it is still x86

gilangpratama@Gilangs-MacBook-Air ~ % lipo -archs /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
x86_64

[teleporterTJ]

Hey guys, uninstall the current version of pyodbc and re install from the source pip install --no-binary :all: pyodbc worked for me

[nggepe]

Hey guys, uninstall the current version of pyodbc and re install from the source

pip install --no-binary :all: pyodbc

worked for me

What python/pip version do you use? I'll try that

[baskoroadiw]

Yes, it is still x86

gilangpratama@Gilangs-MacBook-Air ~ % lipo -archs /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
x86_64

Don't use XAMPP. Switch to MAMP and follow these instructions https://gist.github.com/queval-j/040128a4f307d0765fb08020dd47a2b8

[nggepe]

Yes, it is still x86

gilangpratama@Gilangs-MacBook-Air ~ % lipo -archs /Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20200930/sqlsrv.so
x86_64

Don't use XAMPP. Switch to MAMP and follow these instructions https://gist.github.com/queval-j/040128a4f307d0765fb08020dd47a2b8

thanks, I'll try that

[nggepe]

Hi @baskoroadiw thanks for your recommendation. Now I'm using MAMP with php8.2 and it is work well with this extension. I think I should close this issue. Thanks for your support @v-makouz , @baskoroadiw @teleporterTJ