Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#526 ORA-01861 when editing table in object view

Snapshot
closed-fixed
Rob Manning
Core (461)
5
2015-01-08
2007-07-05
Anonymous
No

in version 2.5.1 and Snapshot-20070630_1726 I'm always getting an ORA-01861 (Literal does not match format string) error when editing a ORACLE table in the object view.
Editing tables was not a problem in 2.4.1.

Discussion

  • Rob Manning
    Rob Manning
    2007-07-05

    Logged In: YES
    user_id=1287991
    Originator: NO

    I just tried it with some testdata that I have (a bunch of different SQL types) and I was not able to reproduce it. Please post a sample table and data in the form of create/insert statements that can be used to reproduce the problem. We may then be able to see the problem or at least ask better questions.

    Rob

     
  • Logged In: NO

    Hello Rob,

    sorry for not answering this long. I tested a bit myself and found out that this may have something to do with the way, DATE columns are beeing handled internally.
    So that's what I did:

    CREATE TABLE EDIT_TEST
    (
    ID NUMBER(22) PRIMARY KEY not null,
    TEXT VARCHAR2(32) not null,
    NUM NUMBER,
    DESCRIPTION VARCHAR2(255)
    );

    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION) VALUES (41,'012345','22222','aaa');
    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION) VALUES (42,'012345','33333','bbb');
    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION) VALUES (43,'012345','44444','ccc');

    Everything works fine with that - no problems with editing.
    But now try the following:

    DROP TABLE EDIT_TEST;

    CREATE TABLE EDIT_TEST
    (
    ID NUMBER(22) PRIMARY KEY not null,
    TEXT VARCHAR2(32) not null,
    NUM NUMBER,
    DESCRIPTION VARCHAR2(255),
    INSERT_DT DATE not null
    );

    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION,INSERT_DT) VALUES (41,'012345',22222,'aaa',to_date('2007-01-01','YYYY-MM-DD'));
    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION,INSERT_DT) VALUES (42,'012345',33333,'bbb',to_date('2007-01-01','YYYY-MM-DD'));
    INSERT INTO EDIT_TEST (ID,TEXT,NUM,DESCRIPTION,INSERT_DT) VALUES (43,'012345',44444,'ccc',to_date('2007-01-01','YYYY-MM-DD'));

    that's it - no matter which field I try to edit (object view -> right click -> make editable), I always get the ORA-01861 error message.
    I tested this against Oracle 9i (9.2.0.6.0) and Oracle 10g (10.2.0.3.0 - 64bit) with the most recent JDBC driver from Oracle.

    I hope this helps and allows you to reproduce the problem - otherwise I fear I'm running out of ideas ;-)

    Jens

     
  • Rob Manning
    Rob Manning
    2007-08-16

    Logged In: YES
    user_id=1287991
    Originator: NO

    Ok, I'll take a look at it within the next couple of days and see if I can't reproduce it, and at least provide a work-around. Thanks for the sample data.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-08-17

    Logged In: YES
    user_id=1287991
    Originator: NO

    I ran the script you provided and I was getting no errors again when editing. Could you please attach your DTProperties.xml file to this bug report? (You can find it in your <user.home>/.squirrel-sql/ directory). It's the file that stores DataType preferences. I suspect it is this configuration that is keeping me from seeing the problem. I've attached mine if you would like to try it out (backup your existing copy first and then restart SQuirreL after copying the one attached to the same location).

    Rob

     
  • Rob Manning
    Rob Manning
    2007-08-17

    Logged In: YES
    user_id=1287991
    Originator: NO

    File Added: DTproperties.xml

     
  • Rob Manning
    Rob Manning
    2007-08-17

     
    Attachments
  • Logged In: NO

    here's my DTproperties.xml, and you're right. Your's working fine.
    The problem is the 'interpret DATE as TIMESTAMP' option in the datatypes control menu. If I switch this on while using your (previously working) config, the error occurs again.

    Jens

    DTproperties.xml:

    <Beans>
    <Bean Class="net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DTProperties">
    <dataArray Indexed="true">
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTime useJavaDefaultFormat=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTime lenient=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTime localeFormat=3</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString limitReadColumnNames=</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString limitRead=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString limitReadOnSpecificColumns=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString limitReadLength=100</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString useLongInWhere=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeString makeNewlinesVisibleInCell=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeClob readClobsSize=255</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeClob readCompleteClobs=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeClob readClobs=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeClob makeNewlinesVisibleInCell=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeBlob readBlobs=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeBlob readCompleteBlobs=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeBlob readBlobsSize=255</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp whereClauseUsage=2</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp useJavaDefaultFormat=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp lenient=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeTimestamp localeFormat=3</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeOther readSQLOther=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeUnknown readUnknown=false</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeBigDecimal useJavaDefaultFormat=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDate readDateAsTimestamp=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDate useJavaDefaultFormat=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDate lenient=true</string>
    </Bean>
    <Bean Class="net.sourceforge.squirrel_sql.fw.util.beanwrapper.StringWrapper">
    <string>net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.DataTypeDate localeFormat=0</string>
    </Bean>
    </dataArray>
    </Bean>
    </Beans>

     
  • Rob Manning
    Rob Manning
    2007-08-17

    Logged In: YES
    user_id=1287991
    Originator: NO

    Finally - now we are getting somewhere. I'll see if I can't reproduce it using your props file soon.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-08-18

    Jens bug-reproducing props file

     
    Attachments
  • Rob Manning
    Rob Manning
    2007-08-18

    Logged In: YES
    user_id=1287991
    Originator: NO

    File Added: DTproperties.xml

     
  • Rob Manning
    Rob Manning
    2007-08-18

    Logged In: YES
    user_id=1287991
    Originator: NO

    I get the dialog now with the latest version. Hopefully soon I'll post a fix.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-08-18

    Logged In: YES
    user_id=1287991
    Originator: NO

    The problem is that the setting for Timestamp in DTProperties.xml(internally generated WHERE clause - 'use String version of Timestamp') tells SQuirreL to use a string literal for timestamps as in:

    SELECT COUNT(*) FROM EDIT_TEST
    WHERE
    ID=41
    AND TEXT='01234' AND NUM=21222 AND DESCRIPTION='aaaa' AND INSERT_DT='2008-10-01 00:00:00.0';

    If you run this query you will get the "Error: ORA-01861: literal does not match format string" you are seeing.
    I tried this setting also on real TIMESTAMP columns and I get the same behavior. However, if you run the following query it works fine:

    SELECT COUNT(*) FROM EDIT_TEST
    WHERE
    ID=41
    AND TEXT='01234' AND NUM=21222 AND DESCRIPTION='aaaa' AND INSERT_DT={ts '2008-10-01 00:00:00.0'};

    This is the SQL escape syntax for JDBC, which is specified in DataType Controls preference tab as "Use JDBC standard escape format...". I know that we've had issues with date handling in various databases in the past, which is why this configuration for internally generated where clauses is here. So, you can continue to use "interpret DATE as TIMESTAMP" for Oracle, however you should use the setting for timestamps that specifies the JDBC SQL escape format.

    Let me know if that works for you.

    Rob

     
  • Rob Manning
    Rob Manning
    2007-08-18

    • labels: 336290 --> Core
    • milestone: --> snapshot
     
  • Rob Manning
    Rob Manning
    2007-08-19

    • status: open --> closed-fixed
     
  • Rob Manning
    Rob Manning
    2007-08-19

    Logged In: YES
    user_id=1287991
    Originator: NO

    I added a warning message that is displayed when an Oracle session is started if the setting "string literal" is used for timestamps in where clauses. Not much more I can do here since it is a global setting and presumably useful on databases other than Oracle.

    Rob

     
  • Logged In: NO

    using the JDBC date format really seems to do the trick.

    Thanks a lot!

    Jens