SQL - State 1000?

[Benjamin H.]

Hallo,
ich bin am Ende.. naja noch nicht ganz....

in einem Java-code will ich ein select-abfrage machen:

select * from " + GlobalConfig.getConfig().getProperty("TABLE_METRICVALUES") + " where ts = #" + this.ts.toString().substring(0, 19) + "#;"

der logger gibt folgendes aus:

7625 [main] (QueryRunner.java) FINEST - prepareStatement ENTRY  sun.jdbc.odbc.JdbcOdbcConnection@ac6a45 select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#;
17625 [main] (QueryRunner.java) FINEST - prepareStatement RETURN sun.jdbc.odbc.JdbcOdbcPreparedStatement@15856a5 ElapsedTime=0
17625 [main] (QueryRunner.java) FINEST - fillStatement ENTRY  sun.jdbc.odbc.JdbcOdbcPreparedStatement@15856a5 null
17625 [main] (QueryRunner.java) FINE  - executing query 'select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#;' by using connection sun.jdbc.odbc.JdbcOdbcConnection@ac6a45 [IsolationLevel=2 (=ReadCommitted) Autocommit=true isClosed=false ReadOnly=false]
17641 [main] (QueryRunner.java) FINEST - Sql exception occured: [Oracle][ODBC][Ora]ORA-00911: invalid character[SQL-State 'S1000'/vendorCode '911']'
17641 [main] (QueryRunner.java) FINER - Exception occured for 'select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#;' Exception: [Oracle][ODBC][Ora]ORA-00911: invalid character SqlState=S1000 SqlErrorCode=911
17656 [main] (QueryRunner.java) FINER - rethrow [Error No: 0001.0001.E0001] [Oracle][ODBC][Ora]ORA-00911: invalid character Query: select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#; Parameters: []
[Oracle][ODBC][Ora]ORA-00911: invalid character Query: select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#; Parameters: []

Hat jemand ne Idee woher das Prob kommen könnte??

Vielen Dank für die Hilfe

 

[SlaterB]

funktioniert es denn normalerweise so ein Datum als String einzugeben?

vielleicht ist ein PreparedStatement sicherer gegen Tippfehler:
String sql = "....?.. ";
PreparedStatement ps = conn.prepareStatement( sql );
ps.setDate (1, new java.sql.Date( datum.getTime() ) );

-------------------------

Fehlermeldungen in google eintippen bringt manchmal was
bei "invalid character SqlState=S1000"

habe ich diese Seite hier gefunden, allerdings schon gelöscht und nur im google-Cache

http://66.249.93.104/search?q=cache...45.htm+invalid+character+SqlState=S1000&hl=en

---------------------------------

USING DATESTAMP LITERAL FORMAT {D'1998-05-11'} WITH ODBC 8.0.4.4.0 GIVES ORA-911


Bug No.: 668518
Date: 11-MAY-1998
Product: Oracle ODBC Driver
Version: 8.0.4.4.0
Fixed in version: 8.0.5
RDBMS version: 8.0.4.0.0
Platform: MS Windows NT
Error: ORA-911
Status: 93 - Closed, Not Verified by Filer

Problem description:
-------------------
The customer is moving from the 2.5.3.1b ODBC driver to use the latest 8.0.4.4
driver with a new Oracle 8.0.4.0.0 RDBMS. When a datestamp literal format
as in "{d'1998-05-11'}" to represent a date (or time) criteria in a select
statement, Oracle's ODBC driver returns ORA-911 "Invalid Character". This
works with the 2.5.3.1b version of the driver against 7.3.4 and against 8.0.4.0
The conversion of the format is not occuring as it did with the previous ODBC
version 2.5.3.1b.
.
Keywords:
--------
ODBC, DATE, FORMAT, ORA-911, 8.0.4.4.0, {d'YYYY-MM-DD'}
.
Testcase location:
-----------------
not applicable
.
Testcase step-by-step instructions:
----------------------------------
To reproduce, go into the ODBC 32bit Test utility on a Windows NT 4.0 client,
connect to any 8.0.4.0.0 RDBMS and execute the following statement:
.
SELECT * FROM DUAL WHERE SYSDATE={d'1998-05-11'}
.
(The where clause should be modified to use the current date of the system)
Data returned should be a blank row. Instead, error ORA-911 is returned with
the following other details:
.
SQLSTATE S1000
Native Error Code 911
Driver Message[Oracle][ODBC][Ora]ORA-911 invalid character
.
This error will also occur using other literal formats:
.
{t'00:00'}
{ts'1998-05-11 00:00:00'}
.
To compare behaviour, use the 2.5.3.1b ODBC driver against the same database
- it will correctly return the blank row without error.
.
Generic/Port-specific findings:
----------------------------------
Tested with latest available version of ODBC: 8.0.4.4.0
.
Rep? Platform Client ODBC Ver. RDBMS Ver.
---- ------------ --------- ----------- ------------
Y P912 P912 8.0.4.4.0 8.0.4.0.0
N P912 P912 2.5.3.1b 8.0.4.0.0
N P912 P912 2.5.3.1b 7.3.4.0.0
.
Available workarounds:
---------------------
The workaround involves changing the WHERE statement to use standard SQL:
.
SELECT * FROM DUAL WHERE SYSDATE = to_date('1998-05-11','YYYY-MM-DD')
.
This works without error through ODBC 8.0.4.4.0, but would force the customer
into changing a large application, with many instances of the date literal
format within it.
.
Related bugs:
------------
None
.
Additional information:
----------------------

 

[foobar]

select * from KENNZAHL_VALUE where ts = #1238-10-17 11:11:00#;

Was machen denn die Hashzeichen da?
Meines Erachtens ist dort der Fehler zu suchen.