Description

This test ensures the correctness of the Connect agent SQLReader against the following DBMS
1. SQLite
2. Postgres
3. MySQL
4. SQL-Server
5. MS-Access
6. SQLAlchemy

Contributor: Vaibhavnath Jha, March 2023

  On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set
  to use the internal Python installation in sysdir/GMSPython.

Small Model of Type : GAMS

Category : GAMS Test library

Main file : casqlr.gms

$title 'Test Connect agent SQLReader' (CASQLR,SEQ=909)

$onText
This test ensures the correctness of the Connect agent SQLReader against the following DBMS
1. SQLite
2. Postgres
3. MySQL
4. SQL-Server
5. MS-Access
6. SQLAlchemy

Contributor: Vaibhavnath Jha, March 2023
$offText

* On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set
* to use the internal Python installation in sysdir/GMSPython.
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%

$onEcho > testScript.sql
DROP TABLE IF EXISTS scalarTable;
DROP TABLE IF EXISTS parameterTable;
DROP TABLE IF EXISTS deciTable;
DROP TABLE IF EXISTS setTable;
DROP TABLE IF EXISTS distanceTableOne;
DROP TABLE IF EXISTS distanceTableTwo;

CREATE TABLE scalarTable 
(
    value_col INTEGER PRIMARY KEY
);

INSERT INTO scalarTable(value_col) VALUES(653);

CREATE TABLE parameterTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION,
    value_col2 DOUBLE PRECISION
);

INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i3', 0.453, 0.532);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i2', 0.638, 0.478);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i1', 0.987, 0.804);

CREATE TABLE deciTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION
);

INSERT INTO deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO deciTable (label, value_col1) VALUES('i1', 123.32);

CREATE TABLE setTable
(
    id INTEGER PRIMARY KEY,
    label1 TEXT,
    label2 TEXT,
    expText1 TEXT,
    expText2 TEXT
);

INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(6, 'i2', 'j3', NULL, 'RandomText6');

CREATE TABLE distanceTableOne
(
    id INTEGER PRIMARY KEY,
    source TEXT,
    newyork DOUBLE PRECISION,
    chicago DOUBLE PRECISION,
    topeka DOUBLE PRECISION
);

INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(2, 'san-diego', 2.5, 1.8, 1.4);

CREATE TABLE distanceTableTwo
(
    id INTEGER PRIMARY KEY,
    i_place TEXT,
    j_place TEXT,
    miles DOUBLE PRECISION
);

INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(6, 'san-diego', 'topeka', 1.4);
$offEcho

* The following SQL Script creates 5 tables in MS-SQL(SQL Server).
* Two similar(almost) scripts are created because SQL-Server uses T-SQL queries.

$onEcho > SQLServerScript.sql
IF OBJECT_ID('dbo.scalarTable', 'U') IS NOT NULL DROP TABLE dbo.scalarTable;
IF OBJECT_ID('dbo.parameterTable', 'U') IS NOT NULL DROP TABLE dbo.parameterTable; 
IF OBJECT_ID('dbo.deciTable', 'U') IS NOT NULL DROP TABLE dbo.deciTable;
IF OBJECT_ID('dbo.setTable', 'U') IS NOT NULL DROP TABLE setTable; 
IF OBJECT_ID('dbo.distanceTableOne', 'U') IS NOT NULL DROP TABLE dbo.distanceTableOne; 
IF OBJECT_ID('dbo.distanceTableTwo', 'U') IS NOT NULL DROP TABLE dbo.distanceTableTwo; 

CREATE TABLE dbo.scalarTable 
(
    value_col INTEGER PRIMARY KEY
);

INSERT INTO dbo.scalarTable(value_col) VALUES(653);


CREATE TABLE dbo.parameterTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION,
    value_col2 DOUBLE PRECISION
);

INSERT INTO dbo.parameterTable VALUES('i3', 0.453, 0.532);
INSERT INTO dbo.parameterTable VALUES('i2', 0.638, 0.478);
INSERT INTO dbo.parameterTable VALUES('i1', 0.987, 0.804);

CREATE TABLE dbo.deciTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION
);

INSERT INTO dbo.deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i1', 123.32);

CREATE TABLE dbo.setTable
(
    id INTEGER PRIMARY KEY,
    label1 TEXT,
    label2 TEXT,
    expText1 TEXT,
    expText2 TEXT
);

INSERT INTO dbo.setTable VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO dbo.setTable VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO dbo.setTable VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO dbo.setTable VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO dbo.setTable VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO dbo.setTable VALUES(6, 'i2', 'j3', NULL, 'RandomText6');


CREATE TABLE dbo.distanceTableOne
(
    id INTEGER PRIMARY KEY,
    source TEXT,
    newyork DOUBLE PRECISION,
    chicago DOUBLE PRECISION,
    topeka DOUBLE PRECISION
);

INSERT INTO dbo.distanceTableOne VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO dbo.distanceTableOne VALUES(2, 'san-diego', 2.5, 1.8, 1.4);


CREATE TABLE dbo.distanceTableTwo
(
    id INTEGER PRIMARY KEY,
    i_place TEXT,
    j_place TEXT,
    miles DOUBLE PRECISION
);

INSERT INTO dbo.distanceTableTwo VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO dbo.distanceTableTwo VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(6, 'san-diego', 'topeka', 1.4);
$offEcho


* Executing SQL Scripts
$onEmbeddedCode Python:
import sqlite3, psycopg2, pymysql, pymssql

with open('testScript.sql', 'r') as sql_file:
    sql_script = sql_file.read()
    sql_script_stripped = sql_script.split(";")
    sql_script_stripped.pop()

with sqlite3.connect(**{'database': 'readtest.db'}) as conn:
    cur = conn.cursor()
    cur.executescript(sql_script)
    conn.commit()
    cur.close()
    
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(sql_script)
    conn.commit()
    cur.close()
    
with pymysql.connect(**{'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    for ele in sql_script_stripped:
        cur.execute(ele+ ";")
    conn.commit()
    cur.close()

with open('SQLServerScript.sql', 'r') as sql_file:
    sql_script = sql_file.read()
    
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(sql_script)
    conn.commit()
    cur.close()
$offEmbeddedCode


$onEchoV > readTest.gms  
$log %CONNECTIONTYPE% test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test name missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: scalarTable
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% reading scalar
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["scalarTable"].records.values.tolist()
        expected = [[653.0]]
        if data != expected:
            raise Exception("Unexpected data scalarTable.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1 FROM parameterTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i3', 0.453],
                    ['i2', 0.638],
                    ['i1', 0.987]]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: deciTable
        query: "SELECT label, value_col1 FROM deciTable;"
        dTypeMap: {'value_col1': 'int64'}
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["deciTable"].records.values.tolist()
        expected = [['i3', 121],
                    ['i2', 122],
                    ['i1', 123]]
        if data != expected:
            raise Exception("Unexpected data deciTable.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading set with SQLAlchemy
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    symbols:
      - name: setTable
        query: "SELECT label1, label2, expText1 FROM setTable;"
        type: set
        valueColumns: "lastCol"
        valueSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['i1', 'j1', 'Text1'],
                    ['i1', 'j2', 'Text2'],
                    ['i1', 'j3', 'Text3'],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', 'Text5'],
                    ['i2', 'j3', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.")     
$offEmbeddedCode


$log %CONNECTIONTYPE% reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: ""
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: ""
        indexSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['4', 'i2', 'j1', '', 'RandomText4', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
                    ['6', 'i2', 'j3', '', 'RandomText6', ''],]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableOne
        query: "SELECT source, newyork FROM distanceTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [['seattle', 2.5],
                    ['san-diego', 2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableOne
        query: "SELECT newyork FROM distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [[2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableTwo
        query: "SELECT i_place, j_place, miles FROM distanceTableTwo;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo"].records.values.tolist()
        expected = [['seattle', 'new-york', 2.5],
                    ['seattle', 'chicago', 1.7],
                    ['seattle', 'topeka', 1.8],
                    ['san-diego', 'new-york', 2.5],
                    ['san-diego', 'chicago', 1.8],
                    ['san-diego', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableTwo.") 
$offEmbeddedCode
$offEcho


$onEchoV.access > readAccessTest.gms
$call rm -f testdb.accdb
$call rm -f distanceTableOne.csv
$call rm -f distanceTableTwo.csv
$call rm -f parameterTable.csv
$call rm -f deciTable.csv
$call rm -f scalarTable.csv
$call rm -f setTable.csv

$onEcho > distanceTableOne.csv
id,source,newyork,chicago,topeka
1,seattle,2.5,1.7,1.8
2,san-diego,2.5,1.8,1.4
$offEcho

$onEcho > distanceTableTwo.csv
id,i_place,j_place,miles
1,seattle,new-york,2.5
2,seattle,chicago,1.7
3,seattle,topeka,1.8
4,san-diego,new-york,2.5
5,san-diego,chicago,1.8
6,san-diego,topeka,1.4
$offEcho

$onEcho > parameterTable.csv
label,value_col1,value_col2
i3,0.453,0.532
i2,0.638,0.478
i1,0.987,0.804
$offEcho

$onEcho > deciTable.csv
label,value_col1
i3,121.12
i2,122.22
i1,123.32
$offEcho

$onEcho > scalarTable.csv
value_col
653
$offEcho

$onEcho > setTable.csv
id,label1,label2,expText1,expText2
1,i1,j1,Text1,RandomText1
2,i1,j2,Text2,RandomText2
3,i1,j3,Text3,RandomText3
4,i2,j1,,RandomText4
5,i2,j2,Text5,RandomText5
6,i2,j3,,RandomText6
$offEcho
 

$onEmbeddedCode Python:
import win32com.client as win32
import pyodbc
Access = win32.Dispatch("Access.Application")
#Create a new .accdb file, this takes care of dropping old existing tables
Access.NewCurrentDataBase(r"%system.fp%testdb.accdb") 
Access.CloseCurrentDataBase()
Access.Quit()
del Access

with pyodbc.connect(**{'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': r'%system.fp%testdb.accdb'}) as conn:
    cur = conn.cursor()
    csvs = ['distanceTableOne', 'distanceTableTwo', 'parameterTable', 'deciTable', 'scalarTable', 'setTable']
    for file in csvs:
        cur.execute(r"SELECT * INTO [{0}] FROM [text;HDR=Yes;FMT=Delimited(,);Database=%system.fp%].{0}.csv;".format(file))
    conn.commit()
    cur.close()
$offEmbeddedCode


$log test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
    connectionType: access
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test wrong connection type (default: sqlite)
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: wrongpyodbc
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test name missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: scalarTable
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log reading scalar
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["scalarTable"].records.values.tolist()
        expected = [[653.0]]
        if data != expected:
            raise Exception("Unexpected data scalarTable.")
$offEmbeddedCode


$log reading parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1 FROM parameterTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i3', 0.453],
                    ['i2', 0.638],
                    ['i1', 0.987]]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode

$log reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: deciTable
        query: "SELECT label, value_col1 FROM deciTable;"
        dTypeMap: {'value_col1': 'int64'}
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["deciTable"].records.values.tolist()
        expected = [['i3', 121],
                    ['i2', 122],
                    ['i1', 123]]
        if data != expected:
            raise Exception("Unexpected data deciTable.")
$offEmbeddedCode


$log reading set with explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT label1, label2, expText1 FROM setTable;"
        type: set
        valueColumns: "lastCol"
        valueSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['i1', 'j1', 'Text1'],
                    ['i1', 'j2', 'Text2'],
                    ['i1', 'j3', 'Text3'],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', 'Text5'],
                    ['i2', 'j3', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.")     
$offEmbeddedCode


$log reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: ""
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: ""
        indexSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['4', 'i2', 'j1', '', 'RandomText4', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
                    ['6', 'i2', 'j3', '', 'RandomText6', ''],]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableOne
        query: "SELECT source, newyork FROM distanceTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [['seattle', 2.5],
                    ['san-diego', 2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableOne
        query: "SELECT newyork FROM distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [[2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableTwo
        query: "SELECT i_place, j_place, miles FROM distanceTableTwo;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo"].records.values.tolist()
        expected = [['seattle', 'new-york', 2.5],
                    ['seattle', 'chicago', 1.7],
                    ['seattle', 'topeka', 1.8],
                    ['san-diego', 'new-york', 2.5],
                    ['san-diego', 'chicago', 1.8],
                    ['san-diego', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableTwo.") 
$offEmbeddedCode
$offEcho.access

$onecho > connect.txt
eolonly 1
--CONNECTIONTYPE sqlite
--CONNECTIONDICT {'database': 'readtest.db'}
--CONNECTIONDICTSQLALCHEMY {'drivername': 'sqlite', 'database': 'readtest.db'}
$offEcho
$log testing sqlite
$call.checkErrorLevel gams readTest.gms lo=%gams.lo% pf=connect.txt

$onecho > connect.txt
eolonly 1
--CONNECTIONTYPE mysql
--CONNECTIONDICT {'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal.gams.com', 'database': 'testdb', 'drivername': 'mysql+pymysql'}
$offEcho
$log testing mysql
$call.checkErrorLevel gams readTest.gms lo=%gams.lo% pf=connect.txt 

$onecho > connect.txt
eolonly 1
--CONNECTIONTYPE postgres
--CONNECTIONDICT {'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal.gams.com', 'database': 'testdb', 'drivername': 'postgresql+psycopg2'}
$offEcho
$log testing postgres
$call.checkErrorLevel gams readTest.gms lo=%gams.lo% pf=connect.txt

$onecho > connect.txt
eolonly 1
--CONNECTIONTYPE sqlserver
--CONNECTIONDICT {'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%', 'port': 23000, 'host': 'internal.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal.gams.com', 'database': 'testdb', 'drivername': 'mssql+pymssql'}
$offEcho
$log testing sqlserver
$call.checkErrorLevel gams readTest.gms lo=%gams.lo% pf=connect.txt

$log testing access
$if set RUNACCESS $call.checkErrorLevel gams readAccessTest.gms lo=%gams.lo%
*$ifE sameas('%system.FileSys%','MSNT')and(not(sameas('%system.computername%','KERMIT'))) $call.checkErrorLevel gams readAccessTest.gms lo=%gams.lo%