How to insert row with ID+1

[Chris Normand]

 I am a long time FitNesse user, but a brand new DbFit user (and my SQL is pretty weak).

I want to find the largest number in a column, and then insert a new record with that column + 1.

First, I am uncertain how to use the stored value, is "@MAX" correct? 

!|Store Query| SELECT MAX(appl_prop_id) FROM ${PROP_TBL}|MAX|
!|Inspect Query|Select param_nm, param_val, appl_prop_id from ${PROP_TBL} where appl_prop_id=@MAX|
|param_nm|param_val|appl_prop_id|

This errors with:  org.postgresql.util.PSQLException: ERROR: column "max" does not exist.

And of course, the following fails:

!|Insert|${PROP_TBL}|
|param_nm|param_val|appl_prop_id|
|TestProperty|TestValue|@MAX+1|

with:  java.lang.NumberFormatException: For input string: "@MAX+1"

Any suggestions would be greatly appreciated.   I am trying to introduce automated testing using DbFit/FitNesse to a new project, and I have never used a relational database before (long story) :-(

[Chris Normand]

I figured out part of it:

Prepend ">>" to the SYMBOL name to read it from the DB.

!|Query| SELECT MAX(appl_prop_id) FROM ${PROP_TBL}|
|max?|
|>>MAX_VALUE|

Prepend "_:" to the SYMBOL to use in a WHERE clause.

!|Inspect Query|Select* from ${PROP_TBL} where appl_prop_id = _:MAX_VALUE |

Prepend "<<" to the SYMBOL to use it as data in an INSERT or UPDATE.

|update|${PROP_TBL}|

|param_nm=|param_val=|appl_prop_id|

|TestName|TestVale|<<MAX_VALUE|

[Mark Matten]

Cool. That's how I usually approach this too.