C# Query commands SQL with quotes

Question

I would like get data from a Oracle database in c#. The problem is that two columns named "DATE" and "HOUR" which are SQL/Oracle expressions.

This is my query command:

command.CommandText = @"SELECT BOXID, PUMP_BIT, DATE, HOUR, RUN_DURATION, POWER_ONS FROM PUMP_AGG_HOURLY WHERE BOXID = " + tb_BoxIDAktuell.Text + " AND PUMP_BIT = " + BitPumpe1 + " ORDER BY DATE, HOUR";

For testing I included quotes for the columns DATE and HOUR and the SQL statement runs in the Oracle SQL Developer:

SELECT BOXID, PUMP_BIT, "DATE", "HOUR", RUN_DURATION, POWER_ONS FROM PUMP_AGG_HOURLY WHERE BOXID = '4' AND PUMP_BIT = '1' ORDER BY "DATE", "HOUR";

In C# I added double quotes but the statement gives me error

command.CommandText = @"SELECT BOXID, PUMP_BIT, ""DATE"", ""HOUR"", RUN_DURATION, POWER_ONS FROM TPT2000_PUMP_AGG_HOURLY WHERE BOXID = " + tb_BoxIDAktuell.Text + " AND PUMP_BIT = " + BitPumpe1 + " AND DATE BETWEEN to_date('" + Start + "','dd/mm/yyyy hh24:mi:ss') and to_date('" + Ende + "','dd/mm/yyyy hh24:mi:ss') ORDER BY ""DATE"", ""HOUR""";

ORA-00936 missing expression

I think it's because of the ORDER BY DATE, HOUR which are SQL expressions. I tried ORDER BY PUMP_AGG_HOURLY.DATE, PUMP_AGG_HOURLY.HOUR but get the same error.

How can I solve this problem? Thanks

First of all these are no connection strings, but sql commands. — Mivaweb, Jul 03 '15 at 09:53
have you tried putting the column names in square brackets? [column] (and removing the ") — WraithNath, Jul 03 '15 at 09:54
Whenever you have 2 `" " " "` try with this `\"\"DATE\"\", \"\"HOUR\"\"` — Guruprasad J Rao, Jul 03 '15 at 09:56
@GuruprasadRao Givs me the error `ORA-01741: illegal zero-length identifier` — Kᴀτᴢ, Jul 03 '15 at 10:01
Is it an existing database, or can you simply rename the columns? — jarlh, Jul 03 '15 at 10:09
Please don't concatenate strings to inject values but use parameters to supply those. Then quotes in data will not bother you (sql injection!) and you can use real dates (instead of strings that need to be parsed) — Hans Keﬆing, Jul 03 '15 at 10:33
The effect of the `@` end as soon as the literal ends (end quote). You are concatenating a lot of literals - you need to repeat that `@` for each (where needed). — Hans Keﬆing, Jul 03 '15 at 10:36

score 3 · Accepted Answer · answered Jul 03 '15 at 10:58

This syntax worked for me:

using (connection)
{
    OracleCommand command = new OracleCommand();
    command.Connection = connection;
    command.CommandType = CommandType.Text;
    command.BindByName = true;
    command.CommandText =
        "SELECT BOXID, PUMP_BIT, \"DATE\", \"HOUR\", RUN_DURATION, POWER_ONS " +
        "  FROM PUMP_AGG_HOURLY " +
        "  WHERE BOXID = :BoxID AND PUMP_BIT = :BitPumpe " +
        "    AND \"DATE\" BETWEEN to_date(:Date1,'dd/mm/yyyy hh24:mi:ss') " +
        "                     and to_date(:Date2,'dd/mm/yyyy hh24:mi:ss')" +
        "  ORDER BY \"DATE\", \"HOUR\"";
    command.Parameters.Add("BoxID", '1');
    command.Parameters.Add("BitPumpe", '4');
    command.Parameters.Add("Date1", "30/01/2015 01:00:00");
    command.Parameters.Add("Date2", "30/01/2015 18:00:00");
    OracleDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader.GetString(0) + ", " + reader.GetString(1));
        Console.WriteLine(reader.GetDateTime(2));
    }
}

Oracle test table definition and example data:

create table PUMP_AGG_HOURLY (boxid varchar2(2), pump_bit varchar2(2), 
  "DATE" date, "HOUR" varchar2(5), run_duration number(5), power_ons number(5));

insert into PUMP_AGG_HOURLY 
  values ('1', '4', to_date('2015-01-30 03:45', 'yyyy-mm-dd hh24:mi'), null, null, null);

Great! Thanks! Also find out that the `ORDER BY ColumnID` is also working: `command.CommandText = @"SELECT BOXID, PUMP_BIT, ""DATE"", ""HOUR"", RUN_DURATION, POWER_ONS FROM TPT2000_PUMP_AGG_HOURLY WHERE BOXID = " + tb_BoxIDAktuell.Text + " AND PUMP_BIT = " + BitPumpe1 + " AND \"DATE\" BETWEEN to_date('" + Start + "','dd/mm/yyyy hh24:mi:ss') and to_date('" + Ende + "','dd/mm/yyyy hh24:mi:ss') ORDER BY 3, 4";` — Kᴀτᴢ, Jul 03 '15 at 12:23
`ORDER BY 3, 4` is available, but not considered as best practice, because it is not well readable and if you add column in first place in selected columns you can easily forget about `ORDER BY` which should be changed to `ORDER BY 4, 5`. — Ponder Stibbons, Jul 03 '15 at 12:40

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

This has already been answered in here

Short answer is : Oracle appears to use double quotes (", eg "table") and apparently requires the correct case

edited May 23 '17 at 12:14

Community

1
1

answered Jul 03 '15 at 10:13

Mladen Oršolić

1,352
3
23
43

as in the comments above it give the error `ORA-00936 missing expression` – Kᴀτᴢ Jul 03 '15 at 10:20

C# Query commands SQL with quotes

2 Answers2