sql

All things SQL and JDBC...
dpsutton 2020-10-01T14:24:06.020Z

I have a question about escaping table names if anyone might have some insight. I'm working with oracle db and have a table named "slash_table/". I can call .getColumns on the DatabaseMetaData if I use a table name of "slash_table//" and escape the slash myself. However, it returns a tablename of "slash_table/" unescaped. This seems strange to me.

(-> (jdbc/with-db-metadata [metadata (->spec database)]
        (jdbc/result-set-seq (.getColumns metadata nil "HR" "slash_table//" nil)))
      first
      :table_name)
returns "slash_table/".

dpsutton 2020-10-01T14:25:09.021Z

I would expect that the table name returned would be a valid table name but that doesn't appear to be so

dpsutton 2020-10-01T14:25:52.021500Z

running the above with "slash_table/" as the tablename results in ORA-01424: missing or illegal character following the escape character

2020-10-01T15:42:55.023200Z

Not to further confuse things, but '/' is not the escape character '\' is

seancorfield 2020-10-01T16:07:10.026Z

Did you try "\"slash_table/\"" i.e., SQL-quoting the name? Not sure if JDBC respects that.

dpsutton 2020-10-01T16:13:16.027400Z

"\"slash_table/\"" - missing or illegal character "\"slash_table//\"" no error but not matching the table "\"slash_table\/\"" unsupported escape character \/ "slash_table/" missing or illegal character "slash_table//" -> `"slash_table/"

dpsutton 2020-10-01T16:16:24.027700Z

however, when inserting the quotes work: (jdbc/insert! oracle "hr.\"foo//bar\"" {:column1 "single slash?"})

dpsutton 2020-10-01T16:17:07.028200Z

but there they don't need to be escaped (jdbc/query oracle "select * from hr.\"foo/bar\"") (i have a table foo/bar and foo//bar for testing

2020-10-01T16:29:09.028600Z

the way to escape / would be \\/ which I don't think you tried

2020-10-01T16:29:41.029200Z

you want \/, to get it you need to exscape \ for string literals, hence the \\

dpsutton 2020-10-01T16:44:18.029600Z

ah right. "\"slash_table\\/\"" missing or illegal character

seancorfield 2020-10-01T16:49:49.029700Z

databases are weird... ¯\(ツ)

dpsutton 2020-10-01T16:50:50.030300Z

haha yes they sure are. thank you both for your help so far. i think i'm in for a rough time figuring out when to escape and when not to ...