How is a connection to a database with an ODBC driver set?

QuestionsHow is a connection to a database with an ODBC driver set?
Matt asked 2 years ago

I have the snowflake ODBC driver configured and working with my PHP install. Normal pages and forms can connect and function using it by calling the DSN to pull in the database, warehouse, roles etc. Would you be able to provide an example of how it should be set for the grid please?
I have tried with the below unsuccessfully:
(account info replaced with generics in caps)

$db_conf = array();
$db_conf[“type”] = “odbc”;
$db_conf[“server”] = “Driver={snowflakedb};Server=ACCOUNT.east-us-2.privatelink.snowflakecomputing.com:443;Database=INVENTORY;”;
$db_conf[“user”] = “USER”;
$db_conf[“password”] = “PASS”;
$db_conf[“database”] = “INVENTORY”;

5 Answers
Abu Ghufran Staff answered 2 years ago

Please try this setting:

$db_conf = array();
$db_conf["type"] = "pdo";
$db_conf["server"] = "odbc:Driver=........"; 
$db_conf["user"] = 'USER'; // username
$db_conf["password"] = 'PASS'; // password
$db_conf["database"] = 'INVENTORY'; // database
_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Abu Ghufran Staff answered 2 years ago

Another option is to setup PDO for snowflakedb.

https://github.com/snowflakedb/pdo_snowflake

And this PDO connection sample code: https://www.gridphp.com/demo/demos/loading/db-layer-pdo.phps

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Abu Ghufran Staff replied 2 years ago

PS: I’ve not tested it and answer is based on research.

Matt answered 2 years ago

I tried with changing this:

$db_conf["type"] = "pdo";
$db_conf["server"] = "odbc:Driver=........"; 

I still get this error though:
“Please check your database connection configuration. Connection attempt failed: SQLSTATE[01000] SQLDriverConnect: 0 [unixODBC][Driver Manager]Can’t open lib ‘snowflakedb’ : file not found”

snowflakedb is the name of my DSN which has the DB, warehouse, role defined. I don’t know if a DSN needs to be called or referenced in a different manner for this grid code?

Abu Ghufran Staff answered 2 years ago

Please test your PHP connectivity with Snowflakedb in an empty php file (without datagrid), then share that code here and I’ll update you back the datagrid connection settings.

_________________________
Abu Ghufran - Dev Team
Grid 4 PHP Framework
 
Matt answered 2 years ago

Perfect, the configuration example you provided below works! The grid loads with the data from Snowflake.
Thank you so much Abu!

Sharing here for the benefit of others:

$db_conf = array();
$db_conf["type"] = "odbc";
$db_conf["server"] = "snowflakedb";
$db_conf["user"] = "$user"; //username from ENV
$db_conf["password"] = "$password"; //password from ENV
$db_conf["database"] = ""; //keep it blank

Notes:
User and pass are set as system environment variables.
Retrieved like the following:
$user = getenv(“user”);

Server in my case is defined in the snowflake odbc.ini in the /etc directory. It has the server name, port, warehouse, role and other necessary info. The DSN name is snowflakedb so that is what is listed for the server.
$db_conf[“server”] = “snowflakedb”;

Your Answer

20 + 10 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?