3

I've a problem with escaping colons and dashes in SQL queries when I use dbFit with Fitnesse.

Such statement doesn't work:

!|Query|select to_char(my_birthday,'YYYY-MM-DD HH24:MI:SI') from family|

I need to replace colons and dashes with some other acceptable characters, ex.

!|Query|select to_char(my_birthday,'YYYY_MM_DD HH24_MI_SI') from family|

Do you know how to solve it properly without using the 2nd approach ?

Cheers, foxrafi

user272735
  • 10,473
  • 9
  • 65
  • 96
Rarar
  • 419
  • 2
  • 6
  • 19
  • Are you using the FitSharp or the Java version of DbFit? When you say "such statement doesn't work", what error are you seeing? – benilov Oct 22 '13 at 13:06
  • It turned out the problem is only with colons. When I use a colon in a SQL query I receive such error: Standard Error: java.sql.SQLException: Invalid column index at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) – Rarar Oct 24 '13 at 10:10
  • A sample query that doesn't work looks like that: !|Query|select to_char(current_timestamp,'YYYY-MM-DD HH24:MI_SS') t from dual| – Rarar Oct 24 '13 at 10:14

3 Answers3

3

I think this is what you need. From http://dbfit.github.io/dbfit/docs/reference.html

Avoiding parameter mapping

If you want to prevent DbFit from mapping parameters to bind variables (eg to execute a stored procedure definition that contains the @ symbol in Sql Server), disable bind symbols option before running the query.

|set option|bind symbols|false|

|execute| insert into users (name, username) values ('@hey','uuu')|

|query|select * from users| |name|username| |@hey|uuu|

Remember to re-enable the option after the query is executed. You can use the same trick with the Execute command.

benilov
  • 1,234
  • 11
  • 11
Mike Stockdale
  • 5,256
  • 3
  • 29
  • 33
1

In addition to Mike's answer, you can also solve this by using bind parameters. This is useful if you have to use bind parameters at other places in the same query.

!|DatabaseEnvironment|ORACLE|
|Connect|${HOSTNAME}|${USERNAME}|${PASSWORD}|

!|Query|!-select current_timestamp ts from dual-!|
|ts?|
|>>my_birthday_ts|

|set parameter|my_birthday_ts| <<my_birthday_ts|
#Set bind parameter :MI to string ':MI'
|set parameter|MI|:MI|
# and do it in the same way with :SS.
|set parameter|SS|:SS|

!|Query|!-select to_char(:my_birthday_ts, 'YYYY-MM-DD HH24'|| :MI || :SS) bds from dual-!|
|bds?|
|>>birthday_string|

Note that you have to use !- -! around your query, otherwise FitNesse will expand the concatenation operator to table cells. The main drawback of this manner is that you cannot use ordinary FitNesse variables (${varname}) in the query.

D. Josefsson
  • 1,052
  • 7
  • 21
0
!|DatabaseEnvironment|ORACLE|
|Connect|${HOSTNAME}|${USERNAME}|${PASSWORD}|

!|Query|!-select current_timestamp ts from dual-!|
|ts?|
|>>my_birthday_ts|


!|Query|!-select to_char(:my_birthday_ts, 'YYYY-MM-DD HH24:'||'MI:'||'SS) bds from dual-!|
|bds?|
|>>birthday_string|
kos32
  • 103
  • 1
  • 2
  • 4