[MDEV-24635] CONNECT Engine - Feature Request: Allow Connection=variable Created: 2021-01-20  Updated: 2022-10-03

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Connect
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Ember Ludwig Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

It would be helpful to be able to use a variable for the Connection value when using the CONNECT Engine. I am trying to create a stored proc to create databases, but each table will reference a different DSN which is already defined. I cannot find a way to do this with a variable. I think I might be able do this by creating a string of sql and executing it, but I would prefer not to do that because of the issues with that and introducing that pattern into our app.

This works:
CREATE TABLE `MY_TEST` (
...
) ENGINE=CONNECT
table_type=ODBC
DEFAULT CHARSET=latin1
option_list='UseDSN=Yes'
CONNECTION='mydsn1';

This does not work:
CREATE TABLE `MY_TEST` (
...
) ENGINE=CONNECT
table_type=ODBC
DEFAULT CHARSET=latin1
option_list='UseDSN=Yes'
CONNECTION=var_dsn_name;

Thank you for your consideration.
Ember

 Comments   
Comment by markus makela [ 2022-10-03 ]

A workaround to this is to use EXECUTE IMMEDIATE:

MariaDB [test]> SET @connect_options='Driver=PostgreSQL Unicode;UID=maxuser;PWD=maxpwd;SERVER=127.0.0.1;PORT=5432';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> EXECUTE IMMEDIATE CONCAT("CREATE TABLE my_connect_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='t2' CONNECTION='", @connect_options, "'");
 
Query OK, 0 rows affected (0.033 sec)

Generated at Thu Feb 08 09:31:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.