Try to update postgresql database using Doobie but no update happening

Question

I'm trying to update table in postgresql database passing dynamic value using doobie functional JDBC while executing sql statement getting below error.Any help will be appreciable.

Code

Working code

sql"""UPDATE layout_lll
        |SET runtime_params = 'testing string'
        |WHERE run_id = '123-ksdjf-oreiwlds-9dadssls-kolb'
        |""".stripMargin.update.quick.unsafeRunSync

Not working code

val abcRunTimeParams="testing string" 
val runID="123-ksdjf-oreiwlds-9dadssls-kolb" 

sql"""UPDATE layout_lll
    |SET runtime_params = '${abcRunTimeParams}'
    |WHERE run_id = '$runID'
    |""".stripMargin.update.quick.unsafeRunSync

Error

Exception in thread "main" org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

What is field type of ```runtime_params```? What is being assigned to ```abcRunTimeParams```? — Adrian Klaver, Aug 04 '20 at 14:40
@AdrianKlaver runtime_params is string type field and I have updated my question as well please take look. — user9318576, Aug 04 '20 at 14:58
I don't use ```Scala``` but as I understand it ```${}``` is used to evaluate expressions. Should you not be using ```$abcRunTimeParams```? Can you 'print' out the formatted ```sql``` to see what is actually being created? — Adrian Klaver, Aug 04 '20 at 15:09
I have used ${} and $abcRunTimeParams both but not luck. The output of sql is UPDATE layout_runs SET runtime_params = 'testing string' WHERE run_id = '123-ksdjf-oreiwlds-9dadssls-kolb' I have updated my question as well please take look. — user9318576, Aug 05 '20 at 04:39

score 0 · Answer 1 · answered Aug 10 '20 at 08:02

Remove the ' quotes - Doobie make sure they aren't needed. Doobie (and virtually any other DB library) uses parametrized queries, like:

UPDATE layout_lll
SET runtime_params = ?
WHERE run_id = ?

where ? will be replaced by parameters passes later on. This:

makes SQL injection impossible
helps spotting errors in SQL syntax

When you want to pass parameter, the ' is part of the value passed, not part of the parametrized query. And Doobie (or JDBC driver) will "add" it for you. The variables you pass there are processed by Doobie, they aren't just pasted there like in normal string interpolation.

TL;DR Try running

val abcRunTimeParams="testing string" 
val runID="123-ksdjf-oreiwlds-9dadssls-kolb" 

sql"""UPDATE layout_lll
    |SET runtime_params = ${abcRunTimeParams}
    |WHERE run_id = $runID
    |""".stripMargin.update.quick.unsafeRunSync

Try to update postgresql database using Doobie but no update happening

1 Answers1