Search code examples
javasqljdbcfirebirdjaybird

Firebird SQL Query with @ (at) sign - How to run the query in JDBC (Jaybird)?

I have an application that uses Firebird. The application performs a long list of queries e.g. each time you list your items. I want to take out these queries, and run them in my own Java application (so I can manipulate the list, display it, and so on.)

The problem is... there is a debug option in the application where you can see what kind of queries does your application run. Some of the original queries got @ signs. If I run a query with an @ in it, I get an error. If I take out that part of the query, everything runs and works "as expected". No errors, like a charm.

Detailed error message:
Error code: -104
Token unknown - line 8, column 32

We use IntelliJ IDEA which automatically applies escape characters when needed.
Such a part from the original query: 

SELECT  TBL4487."Id" "database.id",
       TBL4487."Code" "database.code",
       TBL4487."Name" "database.name",
       TBL4487."Barcode" "database.barcode",
       TBL4488."Name" "Datagroup",
       TBL4489."Name" "Mey",
       (SELECT FIRST 1   TBL4494."Price" / (CASE
    WHEN (TBL4487."GrossPrices" = @Param4495) THEN 1
    ELSE (TBL4492."Rate" + 100) / 100
END) "productprice.price"
FROM "ProductPrice" TBL4494
WHERE (TBL4494."Product" = TBL4487."Id") AND (TBL4494."PriceCategory" = @Param4497) AND (TBL4494."ValidFrom" <= @Param4498) AND (TBL4494."Currency" = @Param4499) AND (TBL4494."QuantityUnit" = TBL4487."QuantityUnit")
ORDER BY TBL4494."ValidFrom" DESC) "xyz",
       (SELECT FIRST 1   TBL4500."Price" / (CASE
    WHEN (TBL4487."GrossPrices" = @Param4501) THEN 1
    ELSE (TBL4492."Rate" + 100) / 100

The question is.. how could I run this query? How do I replace the @ symbol?

Solution

  • You can't run this query directly with Jaybird. These @ParamXXXX seem to be placeholders in the query for parameters. However Firebird nor Jaybird supports this type of placeholders (they only support ? as placeholder in DSQL).

    To execute this with Jaybird, you will need to replace each instance of the @ParamXXXX either with a ? and set the right value for each placeholder in a PreparedStatement, or with the actual value in the query text itself.

    The Firebird .NET provider does support @....-style placeholders (it translates them to Firebird-style ? placeholders), so you could try to use C#/.NET instead if you don't want to do replacing yourself.

    Full disclosure: I am the developer of Jaybird