Unable to get insert statements to work

[drmmr763]

Hi All

I'm trying to use this package to do inserts to snowflake from laravel but I'm running into some issues getting the right syntax to work.

This statement works via Snowflake SQL:

insert into VARIA (pdl_person)
   select PARSE_JSON('{"test": "value"}');

I tried to create a similar eloquent style insert:

DB::connection('pdl_dev')
                ->table('varia')
               ->insert(
                   array('pdl_person' => DB::connection('pdl_dev')->raw('SELECT PARSE_JSON(\'{"test": "value"}\')')),
               );

At first I was getting this error:

Too few arguments to function Illuminate\Database\Query\Expression::getValue(), 0 passed in /var/www/html/vendor/yoramdelangen/laravel-pdo-odbc/src/Flavours/Snowflake/Concerns/GrammarHelper.php on line 58 and exactly 1 expected

It appeared to me there was a bug in GrammarHelper.php:

public function getValue($expression)
    {
        return $expression instanceof Expression ? $expression->getValue() : $expression;
    }

The $expression->getValue method expects a parameter, I hacked the library and added it:

public function getValue($expression)
    {
        return $expression instanceof Expression ? $expression->getValue(new Query()) : $expression;
    }

That got me to another error:

Invalid expression [PARSE_JSON('{"test": "value"}')] in VALUES clause (Connection: pdl_dev, SQL: insert into VARIA (PDL_PERSON) values (PARSE_JSON('{"test": "value"}')))

I can see that this compiled statement doesn't exactly match what snowflake expects for a json parse:

INSERT INTO table1 (ID, varchar1, variant1)
    SELECT 4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }');

https://docs.snowflake.com/en/sql-reference/sql/insert

It apparently doesn't want to have the entire select / parse wrapped in parenthesis.

Has anyone successfully inserted into snowflake with this library? I would love to see some working example code.

[yoramdelangen]

There is PR with support for Raw queries. Which was not in the package yet. This should be fixed in a near future version. Keep you posted