Can't get parameterized table truncation working for Persistent rawExecute of SQL statement.

[Christopher Allen]

I am trying to do parameterized TRUNCATE of tables for my yesod-test tests.

Prelude> runTestDB $ P.rawExecute "TRUNCATE table ? CASCADE" [P.PersistText "teachers"]
06/Jan/2015:12:34:10 -0600 [Debug#SQL] "TRUNCATE table ? CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"'teachers'\"", sqlErrorDetail = "", sqlErrorHint = ""}

Prelude> runTestDB $ P.rawExecute "TRUNCATE table '?' CASCADE" [P.PersistText "teachers"]
06/Jan/2015:12:34:16 -0600 [Debug#SQL] "TRUNCATE table '?' CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"''\"", sqlErrorDetail = "", sqlErrorHint = ""}

Prelude> runTestDB $ P.rawExecute "TRUNCATE table \"?\" CASCADE" [P.PersistText "teachers"]
06/Jan/2015:12:34:23 -0600 [Debug#SQL] "TRUNCATE table \"?\" CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"'teachers'\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

Prelude> runTestDB $ P.rawExecute "TRUNCATE table \"?\" CASCADE;" [P.PersistText "teachers"]
06/Jan/2015:12:34:25 -0600 [Debug#SQL] "TRUNCATE table \"?\" CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"'teachers'\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

Prelude> runTestDB $ P.rawExecute "TRUNCATE table '?' CASCADE;" [P.PersistText "teachers"]
06/Jan/2015:12:34:29 -0600 [Debug#SQL] "TRUNCATE table '?' CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"''\"", sqlErrorDetail = "", sqlErrorHint = ""}

Prelude> runTestDB $ P.rawExecute "TRUNCATE table ? CASCADE;" [P.PersistText "teachers"]
06/Jan/2015:12:34:34 -0600 [Debug#SQL] "TRUNCATE table ? CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
*** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"'teachers'\"", sqlErrorDetail = "", sqlErrorHint = ""}

The most interesting error being:

*** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"'teachers'\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}

runTestDB $ P.rawExecute "TRUNCATE table teachers CASCADE;" []    works fine

I gathered from the documentation that this is a value parameter, should I be using ?? and some kind of value witness to the Teacher entity type instead? I took a few stabs at that and that didn't work either. I'd rather not do it that way anyway as my data is coming to me via text.

Any help is appreciated, thank you.

--- Chris Allen

[Greg Weber]

I am not familiar with parameterized raw SQL. One thing you can do is just concatenate the SQL string instead of using parameters if you have a list of your table names.

tableName [1] could possibly be useful here.

[1] http://hackage.haskell.org/package/persistent-postgresql-2.1.2.1/docs/Database-Persist-Postgresql.html#v:tableName

--
You received this message because you are subscribed to the Google Groups "Yesod Web Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to yesodweb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bardur Arantsson]

On 2015-01-06 19:57, Christopher Allen wrote:
>
>
> I am trying to do parameterized TRUNCATE of tables for my yesod-test tests.
>
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table ? CASCADE" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:10 -0600 [Debug#SQL] "TRUNCATE table ? CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"'teachers'\"", sqlErrorDetail = "", sqlErrorHint = ""}
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table '?' CASCADE" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:16 -0600 [Debug#SQL] "TRUNCATE table '?' CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"''\"", sqlErrorDetail = "", sqlErrorHint = ""}
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table \"?\" CASCADE" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:23 -0600 [Debug#SQL] "TRUNCATE table \"?\" CASCADE" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"'teachers'\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table \"?\" CASCADE;" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:25 -0600 [Debug#SQL] "TRUNCATE table \"?\" CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42P01", sqlExecStatus = FatalError, sqlErrorMsg = "relation \"'teachers'\" does not exist", sqlErrorDetail = "", sqlErrorHint = ""}
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table '?' CASCADE;" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:29 -0600 [Debug#SQL] "TRUNCATE table '?' CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"''\"", sqlErrorDetail = "", sqlErrorHint = ""}
>
> Prelude> runTestDB $ P.rawExecute "TRUNCATE table ? CASCADE;" [P.PersistText "teachers"]
> 06/Jan/2015:12:34:34 -0600 [Debug#SQL] "TRUNCATE table ? CASCADE;" [PersistText "teachers"] @(persistent-2.1.1.3:Database.Persist.Sql.Raw ./Database/Persist/Sql/Raw.hs:64:18)
> *** Exception: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"'teachers'\"", sqlErrorDetail = "", sqlErrorHint = ""}

(I know next to nothing about persistent, but...)

Databases generally don't accept "?" in any old position in a
prepared[0] SQL statement. That includes positions where you would have
a table name.

[0] Which is what you have to use if you're using "?" in the first place.

Regards,

[Christopher Allen]

I side-stepped the problem temporarily by using string concatenation but that is *really* dirty and I'd rather understand how to do a simple parameterized raw SQL statement for when it comes up again anyway.

[Bardur Arantsson]

On 2015-01-06 21:01, Christopher Allen wrote:
> I side-stepped the problem temporarily by using string concatenation but
> that is *really* dirty and I'd rather understand how to do a simple
> parameterized raw SQL statement for when it comes up again anyway.
>

See my post. You can't parameterize this part of the statement. (I'm not
sure if specified in the SQL standard, but I *can* tell you that it
won't work on very many databases -- if any.)

[Christopher Allen]

I did see your post Bardur, but it wasn't clear to me until this email that it means it's supposed to be impossible.

I guess using string concatenation/interpolation to build SQL just gives me the hives so I thought what I was doing was unreasonable. Guess not.

Thanks for helping me understand this, I really appreciate it.

--- Chris

[Bardur Arantsson]

On 2015-01-06 21:17, Christopher Allen wrote:
> I did see your post Bardur, but it wasn't clear to me until this email that
> it means it's supposed to be impossible.
>
> I guess using string concatenation/interpolation to build SQL just gives me
> the hives so I thought what I was doing was unreasonable. Guess not.
>

FWIW, what you were trying to do[1] is eminently reasonable! It's just
that RDBMSes were invented at a time when using plain text as a
communication protocol was thought to be a good idea and apparently
people didn't think it worth it to allow database-agnostic
escaping/quoting in the stream of text...

Of course different databases have different quoting rules and some even
vary them according to the *current configuration* on the DB server.
This is very very stupid and wrong, but it's the reality we have to deal
with :(.

[1] Avoiding the need to escape/quote.

[Maximilian Tagher]

Somewhat related to that, is there a function to get a list of all the defined Entities? In combination with `tableName` that might be good for wiping out all the tables in a SQL database.

[Christopher Allen]

I was going to put this in my Stack Overflow question but I got hijacked again.

Here you go Max:

getAllPublicTables :: MonadIO m => ReaderT SqlBackend m [Text]

getAllPublicTables = do

  tables <- P.rawSql "select relname as table from pg_stat_user_tables where schemaname='public';" []

  return (fmap P.unSingle tables)

[Christopher Allen]

Just try to make certain you don't grant truncation permissions to your db user for anything that shouldn't be.