User Tools

Site Tools


interfaces:using_gams_with_postgresql_database_system

Using GAMS with PostgreSQL database system

PostgreSQL is a powerful, open source object-relational database system. You can use GDXVIEWER to transfer data to the database and SQL2GMS to transfer data from the database. However, please read the below sections if you work with postgreSQL database with the GAMS tools GDXVIEWER and SQL2GMS.

GDXVIEWER

GDXVIEWER can, among other things, transfer data from a GDX file to a database. You can open a GDX (GAMS Data eXchange) file in the GDXVIEWER interface by running the following GAMS program:

execute 'gamslib trnsport';
execute 'gams trnsport gdx=testdata.gdx';
execute.asyncNC 'gdxviewer i=testdata.gdx'; 

Here we assume that you have already setup a ODBC (Open Database Connectivity) connection to your postgreSQL database, for example, with PostgreSQL ODBC driver. In GDXVIEWER menu Options –> Configuration–> tab “SQL Database” you can define and test the ODBC connection.

Important! Please specify the SQL double type as follows:

SQL double type:
double precision

e.g.:

After the configuration is done, then, you can in the GDXVIEWER window right click on a symbol and choose menu item Export–>SQL Database . Furthermore, after the connection configuration is done, you can also write to database via a GAMS program, e.g.:

execute 'gamslib trnsport';
execute 'gams trnsport gdx=testdata.gdx';
execute 'gdxviewer i=testdata.gdx sql id=a';

Please remember to define numeric values for the GAMS special values in the GDXVIEWER: menu Options –> Configuration–> tab “Special Values”.

SQL2GMS

SQL2GMS can, among other things, transfer data from a database to a GDX file. You can open the SQL2GMS interface by running the following GAMS program:

execute.asyncNC 'sql2gms';    

Please setup a test the ODBC connection. In case you have written a symbol “a” to the database, for example, as in the GDXVIEWER example, then, a successful transfer of the database symbol “a” to a GDX file can look similar to the below screenshots:

OBSERVE the following. In PostgreSQL unquoted names are case-insensitive and when capital letters are used, for example, in table or column names, then, they are surrounded by double quotes. This means that the SQL query needs to include the double quotes. The SQL query in the above example, i.e.:

SELECT "a"."dim1", "a"."level"  FROM public."a";

demonstrates the use of double quotes. However, for this SQL query the double quotes would not be required.

interfaces/using_gams_with_postgresql_database_system.txt · Last modified: 2017/09/02 17:46 by support