Table Handler library for Apple Silicon M3 ARM64?

It has been three years since M1 came out. Is the table handler for ARM64 will be available soon? I saw only x64 for Intel available. Any suggestion for alternative solution?

1 Like

Hi @Nisai_Wanakule,

We have just updated the plugins (amplxl, eodbc, amplcsv, etc.) to universal binaries (for both Intel and Apple Silicon) and they are now available for download from https://portal.ampl.com/. We are gradually updating binaries to universal since even for solvers, where having native binaries has a small performance benefit, there are still many not available natively for Apple Silicon.

All Intel binaries have been working without issues on M1/M2/M3 machines for the last 3 years thanks to Rosetta 2 (you can install it with: softwareupdate --install-rosetta) and the performance difference is usually minimal. You may still need Rosetta depending on the solvers you are using.

Please let us know if you encounter any issue using the new universal binaries.

Filipe, Thanks for your response. It looks like the link to ‘ampltabl’ downloads are broken, I can’t find them anywhere on AMPL website anymore.

Yes, I have ‘rosetta’ install on my MacBook Pro. I grabbed an old ampltabl.dll from google site dated back in Jan 2014. When I tried to load it gave me error as follows:

ampl.eval(‘load ampltabl.dll;’)
Error executing “load” command:
Cannot load library /Applications/AMPL/ampltabl.dll:
dlopen(/Applications/AMPL/ampltabl.dll, 0x0002): tried: ‘/Applications/AMPL/ampltabl.dll’ (mach-o file, but is an incompatible architecture (have ‘x86_64’, need ‘arm64’)), ‘/System/Volumes/Preboot/Cryptexes/OS/Applications/AMPL/ampltabl.dll’ (no such file), ‘/Applications/AMPL/ampltabl.dll’ (mach-o file, but is an incompatible architecture (have ‘x86_64’, need 'arm64’))

I tried to compile it using the source from the old netlib. On Linux it went well and got the ampltable_64.dll but not for the Mac.

I probably have to wait until I can get hold of the dylib from AMPL website.

Hi @Nisai_Wanakule,

The new table handlers are amplxl, eodbc, amplcsv, which are available in amplxl.dll (load amplxl.dll;), eodbc.dll (load eodbc.dll;), and amplcsv.dll (load amplcsv.dll;). These are available as universal binaries and you can read about the new table handlers at https://plugins.ampl.com/. We are currently not distributing the legacy table handlers for macOS (ampltabl.dll).

What is the database you want to connect to? Is there something the new table handlers cannot do?

Hi Filipe,

I have been using AMPL for many years on Windows. I have never had any proble pulling data from MSSQL using ODBC via old ampltabl library.

Right now, I am trying to get data into AMPL from Azure Sql Edge running on M3 MBP. I can verify to connect and get data using Azure Data Studio. Here is the errors that I got from using ‘eodbc’ on old MacMini (Intel 64 bit) using connection string and DSN:

ampl:
ampl: option version;
option version 'AMPL Version 20231031 (Darwin-21.6.0, 64-bit)
Licensed to AMPL Community Edition License for nwanakule@gmail.com.
Temporary license expires 20240406.
Using license file “/Applications/AMPL/ampl.lic”.
';
ampl:

ampl: reset;
ampl: load eodbc.dll;
set NAME;
param RID {NAME};
option SQL_SELECT ‘SELECT NAME,RID FROM lhs_table(18) where SequenceNumber=11’;
param cs symbolic;
let cs := sprintf(‘Driver=/usr/local/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib;’
& ‘Server=NISAIS-MBP,1433;’
& ‘Database=SystemReliability_V3;’
& ‘TrustServerCertificate=Yes;’
& ‘USER=SA;’
& ‘PASSWORD=%s;’,$SA_PASSWORD);
table LHS_Table IN “eodbc” (cs) (“SQL=” & $SQL_SELECT): [Name], RID;
read table LHS_Table;
ampl: ampl: ampl? Error executing “read_table” command:

Error reading table LHS_Table with table handler eodbc:
The driver reported the following error SQLDriverConnect(DATASOURCE)
IM004:1:0:[iODBC][Driver Manager]Driver’s SQLAllocEnv() failed

ampl: reset;
load eodbc.dll;
set NAME;
param RID {NAME};
option SQL_SELECT ‘SELECT NAME,RID FROM lhs_table(18) where SequenceNumber=11’;
param cs symbolic;
let cs := sprintf(‘dsn=mssql-SystemReliability_V3;uid=SA;pwd=%s;TrustServerCertificate=Yes;’,$SA_PASSWORD);
table LHS_Table IN “eodbc” (cs) (“SQL=” & $SQL_SELECT): [Name], RID;
read table LHS_Table;
Error executing “read_table” command:

Error reading table LHS_Table with table handler eodbc:
The driver reported the following error SQLDriverConnect(DATASOURCE)
IM002:1:0:[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

I can verified that I can get data using Powershell using both sqlclient and odbc:

PS /Users/wanakule> $PSVersionTable

Name Value


PSVersion 7.4.0
PSEdition Core
GitCommitId 7.4.0
OS Darwin 23.1.0 Darwin Kernel Version 23.1.0: Mon Oct 9 21:27:27 PDT 2023; root:xnu-10002.41.9~6/…
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

PS /Users/wanakule> $cs = ‘Data Source=NISAIS-MBP,1433;Initial Catalog=SystemReliability_V3;User ID=SA;Password=’+$env:SA_PASSWORD+‘;Connection Timeout=90’
PS /Users/wanakule> $query = ‘SELECT NAME,RID FROM lhs_table(18) where SequenceNumber=11’
PS /Users/wanakule> $da = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter($query,$cs)
PS /Users/wanakule> $ds = New-Object -TypeName System.Data.DataSet(‘lhs_tuple’)
PS /Users/wanakule> $da.Fill($ds);
3
PS /Users/wanakule> $ds.Tables.Item(0) |Format-Table

NAME RID


Demand 194
Rainfall 31
Fluoride 252

PS /Users/wanakule> $ds.Dispose()
PS /Users/wanakule> $da.Dispose()
PS /Users/wanakule>
PS /Users/wanakule>
PS /Users/wanakule> $cs = ‘driver=/usr/local/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib;Database=SystemReliability_V3;’ +

‘hostname=NISAIS-MBP;port=1433;protocol=TCPIP;’ +
‘uid=SA;pwd=’+$env:SA_PASSWORD+‘;’
PS /Users/wanakule>
PS /Users/wanakule> $cs = ‘dsn=mssql-SystemReliability_V3;uid=SA;pwd=’+$env:SA_PASSWORD+‘;TrustServerCertificate=Yes;’
PS /Users/wanakule> $query = ‘SELECT NAME,RID FROM lhs_table(18) where SequenceNumber=11’
PS /Users/wanakule> $da = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter($query,$cs)
PS /Users/wanakule> $ds = New-Object -TypeName System.Data.DataSet(‘lhs_tuple’)
PS /Users/wanakule> $da.Fill($ds);
3
PS /Users/wanakule> $ds.Tables.Item(0) |Format-Table

NAME RID


Demand 194
Rainfall 31
Fluoride 252

PS /Users/wanakule> $ds.Dispose()
PS /Users/wanakule> $da.Dispose()
PS /Users/wanakule>

Same errors occurred when trying to pull data into AMPL on M3 MBP. I got data successfully on MATLAB odbc with DSN:

conn = odbc(‘mssql-SystemReliability_V3’,‘SA’,getenv(‘SA_PASSWORD’));
fetch(conn,‘SELECT NAME,RID FROM lhs_table(18) where SequenceNumber=11’)

ans =

3×2 table

NAME RID


{‘Demand’ } 194
{‘Rainfall’} 31
{‘Fluoride’} 252

Any help would be appreciated.
Nisai

63_2.jpg

1 Like

Hi @Nisai_Wanakule,

Thank you for reporting this issue. We have just passed this along to the developer in charge of the plugins. We are also working on compiling ampltabl natively for Apple Silicon.

Hi Filipe,

Thanks for your quick response.

Dose the AMPLInstaller.pkg is also available in Apple Silicon? I must have missed it if it does unless the website automatically detect the architecture when user click downlaod.

Nisai

Hi @Nisai_Wanakule,

The AMPLInstaller.pkg installer is an universal installer that works for Intel and Apple Silicon. You can see the binaries that are available natively for Apple Silicon at the moment with:

ampl: shell "file /Applications/AMPL/* | grep arm64 | grep -v '(for'";
/Applications/AMPL/ampl:                      Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/amplcsv.dll:               Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64:Mach-O 64-bit dynamically linked shared library arm64]
/Applications/AMPL/amplxl.dll:                Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64]
/Applications/AMPL/copt:                      Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/cplex:                     Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64:Mach-O 64-bit executable arm64]
/Applications/AMPL/eodbc.dll:                 Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64:Mach-O 64-bit dynamically linked shared library arm64]
/Applications/AMPL/gcg:                       Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/gurobi:                    Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/gurobiasl:                 Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64:Mach-O 64-bit executable arm64]
/Applications/AMPL/highs:                     Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/leasefingerprint:          Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64:Mach-O 64-bit executable arm64]
/Applications/AMPL/libcopt.dylib:             Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64]
/Applications/AMPL/libcplex2211.dylib:        Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64]
/Applications/AMPL/libgurobi110.dylib:        Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit dynamically linked shared library x86_64] [arm64]
/Applications/AMPL/scip:                      Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]
/Applications/AMPL/x-ampl:                    Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [arm64]

The other solvers that are still not available natively for Apple Silicon should run without issues with Rosetta. Universal binaries contain precompiled binaries for Intel and another for Apple Silicon, what Rosetta does is to generate the Apple Silicon binary from the Intel binary when it is not included, and it is so good at it that the performance is pretty much the same.

Hi @Nisai_Wanakule ,
Can you please post the result of the command
odbcinst -j
and the content of the corresponding “odbcinst.ini” file?
Thank you!

Hi Nicolau,

Here they are:

❯ 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/wanakule/.odbc.ini
SQLULEN Size…: 8
SQLLEN Size…: 8
SQLSETPOSIROW Size.: 8
❯ ls -l /opt/homebrew/etc
total 24
drwxr-xr-x 2 wanakule admin 64 Dec 30 00:53 ODBCDataSources
drwxr-xr-x 6 wanakule admin 192 Dec 30 00:54 bash_completion.d
drwxr-xr-x 3 wanakule admin 96 Dec 30 00:53 ca-certificates
-rw-r–r-- 1 wanakule admin 35 Dec 4 19:11 gitconfig
-rw-r–r–@ 1 wanakule admin 1509 Jan 9 19:46 odbc.ini
-rw-r–r–@ 1 wanakule admin 337 Jan 9 19:46 odbcinst.ini
-rw-r–r-- 1 wanakule admin 0 Jan 9 11:23 odbcinst.ini.default
drwxr-xr-x 10 wanakule admin 320 Dec 11 09:27 openssl@3
❯ cat /opt/homebrew/etc/odbc.ini
[mssql-URM]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=URM
TrustServerCertificate=Yes

[mssql-TS]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=TS
TrustServerCertificate=Yes

[mssql-SWRE]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=SWRE
TrustServerCertificate=Yes

[mssql-SystemReliability_V3]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=SystemReliability_V3
TrustServerCertificate=Yes

[mssql-OROP_Data]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=OROP_Data
TrustServerCertificate=Yes

[mssql-INTB_403]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=INTB_403
TrustServerCertificate=Yes

[mssql-FlowSystemSchematic]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib
Server=localhost,1433
Database=FlowSystemSchematic
TrustServerCertificate=Yes

❯ cat /opt/homebrew/etc/odbcinst.ini
[ODBC]
Trace=yes
TraceFile=/tmp/msodbcsql.log

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.dylib
UsageCount=2

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=2

❯ ls -l /opt/homebrew/lib/libmsodbcsql.17.dylib
lrwxr-xr-x 1 wanakule admin 57 Dec 31 22:41 /opt/homebrew/lib/libmsodbcsql.17.dylib → …/Cellar/msodbcsql17/17.10.5.1/lib/libmsodbcsql.17.dylib

  ~/Developer/Docker/gcc-compiler/native-mac/unixODBC-2.3.12 ──────────────────────────────────────── base  ─╮

By the way I recompiled ampltabl to ampltabl_64.dylib and it works OK now. Can’t wait for eodbc which seem to work better with write out.

Thanks.

Nisai

I got ampltabl_64.dylib that I commpiled from clang.

/Applications/AMPL/ampltabl_64.dylib: Mach-O 64-bit dynamically linked shared library arm64
/Applications/AMPL/tableproxy64: Mach-O 64-bit executable arm64

I tested it and it worked out for me. I will switch to eodbc if it works out later.

ampl: load ampltabl_64.dylib;
set RANVAR;
param LHStuple {RANVAR};
option SQL_SELECT ‘SELECT Name,RID FROM lhs_table(18) where SequenceNumber=11’;
param cs symbolic;
let cs := sprintf(‘dsn=mssql-SystemReliability_V3;uid=SA;pwd=%s;TrustServerCertificate=Yes;’,$SA_PASSWORD);

table LHS_Table IN “eodbc” (cs) (“SQL=” & $SQL_SELECT): [NAME], RID;

table LHS_Table IN “ODBC” (cs) (“SQL=” & $SQL_SELECT):
RANVAR<-[Name], LHStuple~RID;
read table LHS_Table;
display LHStuple;
ampl: ampl: ampl: ampl: ampl: ampl: ampl: ampl? ampl: ampl: LHStuple [*] :=
Demand 194
Fluoride 252
Rainfall 31
;

ampl:

I looked at the error again and it seem that eodbc link to iODBC not unixODBC. I tried to download the eodbc-odbcunix version but it failed with same message:

IM004:1:0:[iODBC][Driver Manager]Driver’s SQLAllocEnv() failed

It seems that both versions use iODBC.

Hi @Nisai_Wanakule ,

Thank you for posting all the information!

eodbc.macos-unixodbc.zip and eodbc.macos-iodbc.zip link to the corresponding ODBC manager library, namely libodbc and libiodbc. This can be verified with the instruction

otool -L ./eodbc.dll

The results are

@rpath/eodbc.dll (compatibility version 0.0.0, current version 0.0.0)
/usr/local/opt/unixodbc/lib/libodbc.2.dylib (compatibility version 3.0.0, current version 3.0.0)
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 904.4.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.60.1)

for eodbc.macos-unixodbc.zip and

@rpath/eodbc.dll (compatibility version 0.0.0, current version 0.0.0)
/usr/local/iODBC.universal/lib/libiodbc.2.dylib (compatibility version 4.0.0, current version 4.30.0)
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 904.4.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.60.1)

for eodbc.macos-iodbc.zip.

From the “odbcinst -j” command we see that you have unixODBC 2.3.12 installed.
However, when you run ampl with eodbc you get an iODBC error

"IM004:1:0:[iODBC][Driver Manager]Driver’s SQLAllocEnv() failed"

Note that the error comes directly from iODBC, not from the AMPL driver.
This suggests that you have both unixODBC and iODBC installed in the machine and that the linking to libodbc is leading to libiodbc.

One option would be to reinstall unixODBC. Hopefully that would update the linking to libodbc (please backup the .ini files and such first).

If you need to keep the configuration as it is now you can compile eodbc with the following instructions (individually or in a bash file):

rm -rf plugins
git clone --recursive https://github.com/ampl/plugins.git
cd plugins
mkdir build
cd build
cmake ..
make eodbc

The library will be in
./build/src/amplp/examples/eodbc/eodbc.dll

Note that during the compilation cmake will try to find the desired links to ODBC. They will be present in the log in

– ODBC_INCLUDE_DIR: /usr/local/include
– ODBC_LIBRARIES: /usr/local/lib/libodbc.dylib

If these values are not the desired ones it’s possible to edit the cmake file of eodbc in
plugins/src/amplp/examples/eodbc/CMakeLists.txt
and confirm, after the compilation, that everything looks as expected with the otool.

Hi Nicolau,

Yes, that is the problem. I recompiled the the eodbc per your instruction and now it works.

I am not familiar with cmake but can get by with gnu make. I am wondering how you direct cmake to install in a certain directory or 'make install’ without really installing it. On old gnu make I can specify by --prefix flag at ./configure step. I don’t want to make install until I known what libraries are installed and where.

BTW drop oedbc.dll in /Application/AMPL will need to disable security check from dlopen on Mac.

Thanks for your help. I will test its performance on the database write.
Nisai

61_2.png

Got error with read a table with tinyint type?

select COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=‘Calendar_WMP’

COLUMN_NAME,DATA_TYPE
DayNumber,int
Date,date
MonthStart,date
WeekStart,date
WeekNumber,smallint
DayOfWeek,tinyint
YearStart,date

Using amplapi on matlab

temp = ‘option SQL_SELECT ("select DayNumber,WeekNumber weekno,DayOfWeek dayofweek from Calendar_WMP " & sprintf(“where DayNumber<=%d order by DayNumber”,ndays));’
ampl.eval(temp)
ampl.getOption(‘SQL_SELECT’)
ampl.eval(‘redeclare table GetWeekNumber IN “eodbc” ($DBIN_DSN) (“SQL=” & $SQL_SELECT): [DayNumber], weekno,dayofweek;’)
ampl.eval(‘read table GetWeekNumber;’)

temp =

‘option SQL_SELECT ("select DayNumber,WeekNumber weekno,DayOfWeek dayofweek from Calendar_WMP " & sprintf(“where DayNumber<=%d order by DayNumber”,ndays));’

ans =

‘select DayNumber,WeekNumber weekno,DayOfWeek dayofweek from Calendar_WMP where DayNumber<=365 order by DayNumber’

Error using HandleException
com.ampl.AMPLException: Error executing “read_table” command:
error reading table GetWeekNumber:
Cannot assign ‘1’ to dayofweek[1].

Error in AMPLBase/eval (line 358)
HandleException(e, ‘AMPLAPI:AMPL:eval’);

Change to smallint and it works.

temp = ‘option SQL_SELECT ("select DayNumber,WeekNumber weekno,CAST(DayOfWeek as smallint) dayofweek from Calendar_WMP " & sprintf(“where DayNumber<=%d order by DayNumber”,ndays));’
ampl.eval(temp)
ampl.getOption(‘SQL_SELECT’)
ampl.eval(‘redeclare table GetWeekNumber IN “eodbc” ($DBIN_DSN) (“SQL=” & $SQL_SELECT): [DayNumber], weekno,dayofweek;’)
ampl.eval(‘read table GetWeekNumber;’)
ampl.display(‘dayofweek’)

temp =

‘option SQL_SELECT ("select DayNumber,WeekNumber weekno,CAST(DayOfWeek as smallint) dayofweek from Calendar_WMP " & sprintf(“where DayNumber<=%d order by DayNumber”,ndays));’

ans =

‘select DayNumber,WeekNumber weekno,CAST(DayOfWeek as smallint) dayofweek from Calendar_WMP where DayNumber<=365 order by DayNumber’

dayofweek [*] :=
1 1 38 3 75 5 112 7 149 2 186 4 223 6 260 1 297 3 334 5
2 2 39 4 76 6 113 1 150 3 187 5 224 7 261 2 298 4 335 6
3 3 40 5 77 7 114 2 151 4 188 6 225 1 262 3 299 5 336 7

Is that my assumption correct?

Nisai

61_2.png

Hi @Nisai_Wanakule ,

Thank you for the report!

Support for SQL_TINYINT and SQL_BIGINT is now available at
https://github.com/ampl/plugins
The distributed binaries will be updated in the following days.

On cmake: I usually skip the install step, but it seems that you can use it in a very similar way to make, see
https://cmake.org/cmake/help/latest/variable/CMAKE_INSTALL_PREFIX.html
The instruction would be
cmake --install . --prefix /my/install/prefix