Free Trial

General Discussions

Discuss any topics that are not product-specific here.

Input tool in a macro for dynamic usage - OCI problem with user/password

Bargeton
7 - Meteor

Hello,

 

I've created a macro with just an "input tool" to connect to a dabatase and execute 1 sql query.

 

My macro take 2 inputs

 

1)

the connection string with the |||sql statement

example:

odbc:Driver={PostgreSQL UNICODE};Server=xxxxxxxxx;Port=1234;Database=dbname;Uid=login;Pwd=password|||SELECT * FROM my_table

oci:login/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=dbname)))|||SELECT * FROM my_table

 

This parameter of the macro update the "File" parameter with option "update the value (by default)"

 

2)

the connection type:

  • 23 for ODBC
  • 17 for OCI

 

 

For ODBC, with postgresql, it working well.

 

For OCI, so for Oracle, there is a bug like this (I've translated from french to english.. not sure of the real english message) :

 

Error: ODBC_SQL (127): ODBC_Input (7): Record #1: Tool #6: Folder "C:\Users\me\Projects\oci:login\password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=dbname)))" not valid : The specified path does not exist (3).

 

Where "C:\Users\me\Projects\" is the path of my workflow in my laptop.

 

 

What I suspect is that :

  • with oci, the login and password is put with "login/password"; there is a slash character.
  • I suspect that Alteryx view a slash in the connection string and try to translate it as a full path on my laptop...
  • And we can see in the error message that the "login/password" become "login\password"... so translated as a windows path...

 

Note that when I use an input tool, and manually put the string

oci:login/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=1234))(CONNECT_DATA=(SERVICE_NAME=dbname)))|||SELECT * FROM my_table

it working well.

It's the usage inside the macro which make it not working with the slash to path problem.

 

 

Regards,

 

Alex

0 REPLIES 0
Labels
Top Solution Authors