ODBC Timeout Error

[EoinMcAndrew]

Hi All,

I am using an ODBC Connection to run a SQL query against Snowflake.

I am using the object from the Digital Exchange.

When I open the connection there is a timeout being set:

However, this doesn't seem to be carrying through to the execution of the query:

------------------------------
Eoin McAndrew
Europe/London
------------------------------

[charliekovacs]

Hi Eoin,

In the calculation stage, does it need an equals sign between Timeout and 1000?  i.e. Timeout=1000;

------------------------------
Charlie Kovacs
Sr. Digital Exchange Engineer
Blue Prism
Austin, USA
------------------------------

[EoinMcAndrew]

Thanks Charlie, I have tried that but no luck

------------------------------
Eoin McAndrew
Europe/London
------------------------------

[charliekovacs]

Hi Eoin,

I am not well-versed in Snowflake, but I have a couple of ideas.

1. Does it work if you use query_timeout instead of timeout in the connection string? The Snowflake docs seem to suggest that may be the preferred keyword for an ODBC connection. https://docs.snowflake.com/en/developer-guide/odbc/odbc-parameters (Connection Parameters > Additional Connection Parameters)

2. It appears the timeout can be set in two places for Snowflake - for a single session or for the overall warehouse. It may be worth checking what the timeout is for the warehouse, because Snowflake apparently will use whichever value is lower.

I suspect the solution ultimately lies with Snowflake. I reviewed the ODBC object in Blue Prism again, and I am not seeing any indication that it truncates or overrides the timeout or any values within the connection string.

Cheers

------------------------------
Charlie Kovacs
Sr. Digital Exchange Engineer
Blue Prism
Austin, USA
------------------------------

[EoinMcAndrew]

Hi Charlie, 
So in relation to query 1 it has not made a difference.

In relation to query 2 Snowflake uses a 15 minute timeout in our company as the default.

I have discovered if I reduce the amount of columns I get results but if I query more then 5 I get a different error to the one here? (This has been following some changes we made)

The error now is 

ERROR [HY000] [Snowflake][Snowflake] (25) 
  Result download worker error: Worker error: [Snowflake][Snowflake] (4) 
  REST request for URL https://sfc-ie-ds1-7-customer-stage.s3.eu-west-1.amazonaws.com/I've removed rest of URL for security reasons failed: CURLerror (curl_easy_perform() failed) - code=35 msg='SSL connect error' osCode=9 osMsg='Bad file descriptor'.

------------------------------
Eoin McAndrew
Europe/London
------------------------------

[charliekovacs]

Hi Eoin,

The ODBC object in Blue Prism is very lightweight in the sense that it only forwards a connection string or query to a database system, and it returns either result sets or error messages generated by the underlying database system.

A few things I could recommend checking:
1. Validate your connection string is correct for Snowflake - the screenshot in the first post looks more like a connection string for a SQLite system.
2. Validate the Snowflake ODBC driver you installed is a) 32-bit and b) configured correctly. Do you have a database team or DBA that could verify this or validate ODBC connectivity outside of Blue Prism?

------------------------------
Charlie Kovacs
Sr. Digital Exchange Engineer
Blue Prism
Austin, USA
------------------------------

[EoinMcAndrew]

I'm a bit confused here Charlie, as the smaller query works. If it was a connection issue wouldn't it be a case that none of them would work?

------------------------------
Eoin McAndrew
Europe/London
------------------------------

[charliekovacs]

I would agree with that same idea, but the larger query returning an "SSL connect error" message leads me to believe there is a connection issue even if it is intermittent or varies from query to query. Maybe it is outside of the ODBC driver and connection string. It could be the larger query's URL is getting blocked somewhere in your network.

The reason I question the connection string is there are SQLite keywords mixed into it. I think validating the connection string and ODBC driver settings would be worthwhile to at least rule them out.

------------------------------
Charlie Kovacs
Sr. Digital Exchange Engineer
Blue Prism
Austin, USA
------------------------------

[ewilson]

@EoinMcAndrew 

FYI - We've just uploaded a new version of the Data - ODBC VBO which includes a new optional input parameter on both the Execute and ExecuteNonQuery actions which allows you to set the timeout of the underlying .NET objects that are used to run the query. This may help with your timeout issue. The default timeout in .NET is 30 seconds, so you could try increasing it to say 60 seconds.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[EoinMcAndrew]

Thanks @ewilson , I have deployed this but I keep getting an error with the execute of object reference not set to an instance of the object ?

------------------------------
Eoin McAndrew
N/A
Europe/London
------------------------------