Fork me on GitHub
#sql
<
2020-10-01
>
dpsutton14:10:06

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/".

dpsutton14:10:09

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

dpsutton14:10:52

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

hiredman15:10:55

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

seancorfield16:10:10

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

dpsutton16:10:16

"\"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/"

dpsutton16:10:24

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

dpsutton16:10:07

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

hiredman16:10:09

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

hiredman16:10:41

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

dpsutton16:10:18

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

seancorfield16:10:49

databases are weird... ¯\(ツ)

dpsutton16:10:50

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