Insert stmt not inserting anything

[c00p3r]

So I installed pdo_snowflake oficial driver and this package
It finally works more or less
BUT
I create a table - works
I wanna insert data into that table - FAILS

I'm using laravel query builder like so

$res = DB::connection('snowflake')->table($tableName)->insert($data);

and $res equals true
but when I look at DB table - it's empty

If I copy raw sql and execute it - works
so my sql is CORRECT

My guess it's smtn specific to package
So I wonder if that's related to issue described in docs:

Snowflake also doesn't support streaming bind values. Mainly when using ->prepare('..') statement and following by $stmt->bindValue(...) or $stmt->bindParam(). We added a CustomStatement class to resolve this issue.

My question is how do I go from here? What should I do to make it work?
use CustomStatement class? but how? It's not clear
@yoramdelangen please help me out!

[c00p3r]

In source code I see the opposite
You use custom statement class if driver IS NOT pdo_snoflake

so now I'm even more confused why it's not working for me 😥

[c00p3r]

I narrowed it down to two examples
So everyone could reproduce my issue

My env:
PHP 7.4, docker, pdo_snowflake driver, this package
dockerfile #6 (comment)
database.php config piece

'snowflake' => [
    'driver' => 'snowflake_native',
    'account' => env('SNOWFLAKE_ACCOUNT'),
    'username' => env('SNOWFLAKE_USER'),
    'password' => env('SNOWFLAKE_PASSWORD'),
    'database' => env('SNOWFLAKE_DATABASE'),
    'warehouse' => env('SNOWFLAKE_WAREHOUSE'),
    'schema' => env('SNOWFLAKE_SCHEMA'),
    'options' => [
        PDO::ODBC_ATTR_USE_CURSOR_LIBRARY => PDO::ODBC_SQL_USE_DRIVER
    ]
]

P.S. that 'options' piece has no effect include/exclude on will

1. Create a table with columns
   ID, ACCOUNT, AMOUNT_USD, SUPPLIER, GL_CODE, GL_DESCRIPTION

2. Fill out those vars

$account = env('SNOWFLAKE_ACCOUNT');
$user = env('SNOWFLAKE_USER');
$password = env('SNOWFLAKE_PASSWORD');
$database = env('SNOWFLAKE_DATABASE');
$schema = env('SNOWFLAKE_SCHEMA');
$warehouse = env('SNOWFLAKE_WAREHOUSE');
$tableName = <your_table_name>;
$data = [<any five items here>];

2. Now run those pieces of code using your credentials

---

Here's that same code from vendor/illuminate/database/Connection.php

$conn = DB::connection('snowflake');

$sql = "insert into $database.$schema.$tableName (ACCOUNT, AMOUNT_USD, SUPPLIER, GL_CODE, GL_DESCRIPTION) values (?, ?, ?, ?, ?)";

$statement = $conn->getPdo()->prepare($sql);

// this is same code that line below does
// $conn->bindValues($statement, array_values($data));
foreach (array_values($data) as $key => $value) {
    $statement->bindValue(
        is_string($key) ? $key : $key + 1,
        $value,
        is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
    );
}

$res = $statement->execute(); // $res == true

echo "Result $res\n";

it kinda succeeds but table is empty

And here's almost same code but using bare PDO

$pdo = new PDO("snowflake:account=$account", $user, $password);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// won't work without this
$pdo->query("use warehouse $warehouse");

$sql = "insert into $database.$schema.$tableName (ACCOUNT, AMOUNT_USD, SUPPLIER, GL_CODE, GL_DESCRIPTION) values (?, ?, ?, ?, ?)";

$statement = $pdo->prepare($sql);

foreach (array_values($data) as $key => $value) {
    $statement->bindValue(
        is_string($key) ? $key : $key + 1,
        $value,
        is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR
    );
}

$res = $statement->execute(); // $res == true

echo "Result $res\n";

This code ACTUALLY succeeds resulting in data displayed in the table

So something's wrong about the package here...
pdo_snowflake driver works fine...
@yoramdelangen thoughts?

[c00p3r]

One strange thing I noticed is that
when I run 2nd example - it increments ID
and when I run 1st example - it ALSO INCREMENTS ID
though no data is written to the table
Notice missing IDs 17, 18, 19
those were times when I ran code example 1

WTF? can anyone explain that?

[c00p3r]

P.S. when I look at query log (DB::getQueryLog())
it also shows 1 query executed

[c00p3r]

I'll have to use bare PDO for now 😔
till someone figures out what's going on here...
without 'insert' this package becomes useless to me...

[c00p3r]

one more guess I have why it's not working
maybe smtn's wrong about those options
idk what they mean

not sure though, just guessing...

P.S. DSN generated by the package is valid 100%

[yoramdelangen]

@c00p3r let me see and let me comment on some of your questions.

Please note; that I have recently added the pdo_snowflake and not all parts of the documentation are up-to-date. bindParam/bindValue is only not working (as far as I am aware) for the ODBC driver.

I have been testing it in the same way as you did:

• Creating a table, with a non-incremental id (UUID) ✅
• Creating a table, with incremental id ✅
• Inserting data into the non-incremental id table ✅
• Inserting data into the incremental id table ❌❌ (running the same query in the online editor is working fine)

I'll do some more extensive testing if I can figure out where the problem lies.

[c00p3r]

@yoramdelangen thanks for your reply
I'll share my findings...
so I did another attempt to integrate with Laravel
I removed your package and odbc, pdo-odbc extensions
and I created my own Connection

class Connection extends BaseConnection
{
    public function __construct($pdo, $database = '', $tablePrefix = '', array $config = [])
    {
        $pdo = null;
        $PDO = new PDO("snowflake:account={$config['account']};database={$config['database']};schema={$config['schema']};warehouse={$config['warehouse']};", $config['username'], $config['password']);

        $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        parent::__construct($PDO, $database, $tablePrefix, $config);
    }

that's all I needed to get INSERT operations work using query builder! e.g.

DB::connection('snowflake')->table($table)->insert($data);

next, since I needed CREATE/DROP TABLE operations I had to 'borrow' some classes from your package
mainly Grammar classes, Schema Builder and Processor

---

(btw they have a lot of errors! I use larastan in my project and it throwed a lot of errors at me. You gotta check your project with smtn like larastan)

---

and I implemented couple methods in my Connection class

/**
 * {@inheritdoc}
 */
public function getSchemaBuilder()
{
    if (!$this->schemaGrammar) {
        $this->useDefaultSchemaGrammar();
    }

    return new SchemaBuilder($this);
}

/**
 * {@inheritdoc}
 */
public function getDefaultSchemaGrammar()
{
    $schemaGrammar = $this->getConfig('options.grammar.schema');

    if ($schemaGrammar) {
        return new $schemaGrammar();
    }

    return new SchemaGrammar();
}

/**
 * {@inheritdoc}
 */
public function getDefaultQueryGrammar()
{
    $queryGrammar = $this->getConfig('options.grammar.query');

    if ($queryGrammar) {
        return new $queryGrammar();
    }

    return new QueryGrammar();
}

/**
 * {@inheritdoc}
 */
protected function getDefaultPostProcessor(): Processor
{
    $processor = $this->getConfig('options.processor');
    if ($processor) {
        return new $processor();
    }

    return new Processor();
}

that did it and I managed to do Schema type operations e.g.

Schema::connection('snowflake')->create($table, function (Blueprint $table) {
    $table->increments('id');
    $table->string('foo');
});

Schema::connection('snowflake')->dropIfExists($table);

I hope this info helps you figuring out the issue
and anyone else who stumbles upon this thread
struggling to integrate Snowflake with Laravel as I did
(I have a bad feeling that this is not the end of my struggle 😅 [harold meme here])