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:
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
