Writing to a Snowflake Database

Snowflake is a modern cloud based data warehousing company, see Wikipedia. Its databases can be accessed via ODBC, and a corresponding ODBC driver is available for download on their website.

Creating schema’s without specifying the widths of columns may lead to you encountering the following phrase in an AIMMS error message: Streaming value for bind variable not supported. In this article, a running example is used to:

1. Reproduction : Illustrate how the error message is reproduced.

2. Remedy : How the schema can be adapted to avoid this error message from recurring.

Reproduction

First we create a table without being explicit about the column widths:

create table "DEMO_DB"."PUBLIC" . "HowTo521" ( "NAME" VARCHAR, "AGE" INT ) ;

Looking at the table created in Swowflake’s web interface:

We observe that the created table has the following design:

The default width for VARCHAR, 16777216 is used for this table.

Using the following declarations in the AIMMS model:

DeclarationSection HowTo521 {
    Set s_names {
        Index: i_name;
        Definition: data { jane, joe, jill };
    }
    Parameter p_age {
        IndexDomain: i_name;
        Definition: data { jane : 22, joe : 63, jill : 19 };
    }
    DatabaseTable db_unknownPeopleWithAge {
        DataSource: sp_connectionString;
        TableName: "HowTo521";
        Owner: "PUBLIC";
        Mapping: {
            "NAME" -->i_name,
            "AGE"   -->p_age
        }
    }
}

And running the following procedure:

Procedure pr_writeHT521 {
    Body: {
        write to table db_unknownPeopleWithAge ;
    }
}

We encounter the following error message:

Error writing to database table "db_unknownPeopleWithAge": ODBC[21] : HY000 [Snowflake][Snowflake] (21)
Streaming value for bind variable not supported: 2.

Thusfar the reproduction of the error message. Let’s continue with a potential remedy.

Remedy

The remedy chosen here is to explicitly specify the width of the column “NAME”.

At the time of writing this article, changing the column width seemed only possible by first dropping the column and then recreating it with an explicit width as follows.

First drop the column name:

alter table "DEMO_DB"."PUBLIC" . "HowTo521" drop column "NAME"  ;

Then create it with a specific width:

alter table "DEMO_DB"."PUBLIC" . "HowTo521" add column "NAME" VARCHAR(128) ;

Checking the design of the altered table:

Viewing the data after executing the AIMMS procedure pr_writeHT521 again:

We see that the remedy worked; specifying a specific width turned out to be sufficient. The default of used by Snowflake of 16777216 turns out to be too much.

See also

AIMMS provides a Snowflake Library to facilitate its use within AIMMS. See the documentation here.