Using Sql parameters on Ingres Database error

Question

Exception:

Local variable/parameter ':your-param-name' can only be used within a database procedure.

In MSSQL, the param character is @. In INGRES Database, really is : ? I cannot find official documentation for that...

SELECT * FROM MyIngresTable WHERE MyColumn = :poorParam

C# with Spring.NET AdoTemplate:

IDbParametersBuilder builder = CreateDbParametersBuilder();

builder.Create().Name("poorParam").Type(DbType.Int32).Value(1);

Any help?

Instead of using Spring.NET, try to execute an ADO.NET Command directly. *Can* you create a parameter? Does the driver support parameterized queries? What driver are you using? I remember that IBM hadn't released ADO.NET 2.0 drivers for *years* after .NET 2.0 was released. The driver or database may simply not support parameterized queries. One got the impression that they really wanted people to move to DB2 — Panagiotis Kanavos, Apr 28 '17 at 16:14

score 0 · Answer 1 · answered May 02 '17 at 18:48

Solved!

Just use INTERROGATION without named param, and pass values in order of usage.

        string query = "SELECT * FROM MyIngresTable WHERE MyColumn >= ? and OtherColumn = ?";

        IDbParametersBuilder builder = CreateDbParametersBuilder();

        builder.Create().Type(DbType.Int32).Value(10);
        builder.Create().Type(DbType.String).Size(4).Value("test");

        IList<YourModelType> data = AdoTemplate.QueryWithRowMapper(
            CommandType.Text,
            query,
            new YourRowMapper<YourModelType>(),
            builder.GetParameters()
        );

Another tip about Spring.NET AdoTemplate:

    using Spring.Data.Common;
    using Spring.Data.Generic;

    protected virtual IDbParametersBuilder CreateDbParametersBuilder()
    {
        return new DbParametersBuilder(AdoTemplate.DbProvider);
    }

Thanks also, Panagiotis Kanavos.

Using Sql parameters on Ingres Database error

1 Answers1