UCanAccess / Discussion / Help: MS Access unable to delete using date as criteia

Lon Parisi - 2020-06-16

Hello,

I've been experiencing a strange problem. When I execute the following command:
delete from dislocations where market_date < #2020-06-15# in MS Access SQL it works fine. When I do the exact same thing/command in Java, I get a very unexpected error. Here is my java code:
private void clearOldDislocations() {
try {
String sql = "delete from dislocations where market_date < #" + msafmt.format(this.endDate) + "#";
System.out.println(sql);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

Here is the error I get: delete from dislocations where market_date < #2020-06-15#

java.lang.NumberFormatException
at java.math.BigDecimal.<init>(Unknown Source)
at java.math.BigDecimal.<init>(Unknown Source)
at java.math.BigDecimal.<init>(Unknown Source)
at net.ucanaccess.commands.IndexSelector$ColumnMatcher.matches(IndexSelector.java:75)
at net.ucanaccess.commands.AbstractCursorCommand.currentRowMatches(AbstractCursorCommand.java:32)
at net.ucanaccess.commands.CompositeCommand.persist(CompositeCommand.java:86)
at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:323)
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
at com.baml.voldislocation.DislocationScreener.clearOldDislocations(DislocationScreener.java:80)
at com.baml.voldislocation.DislocationScreener.runData(DislocationScreener.java:63)
at com.baml.voldislocation.DislocationScreener.main(DislocationScreener.java:36)
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 null
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:212)
at com.baml.voldislocation.DislocationScreener.clearOldDislocations(DislocationScreener.java:80)
at com.baml.voldislocation.DislocationScreener.runData(DislocationScreener.java:63)
at com.baml.voldislocation.DislocationScreener.main(DislocationScreener.java:36)</init></init></init>

I have no idea why there is an issue. There are no blanks. In addition, I copied the table's structure and renamed the old one to see if there was the possibility the table was corrupt in some way.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

Lon Parisi - 2020-06-23

So I have figured out why this error is happening. Somehow, there was a -1#IND value in one of the columns of my MS Access table. Because of that, UCanAccess (or one of the other dependencies products) could not delete the data EVEN THOUGH the column used in the delete criteria did not have the -1#IND value. I removed/corrected that record and everything is now working fine.

If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
- Marco Amadei - 2020-06-23
  
  Ok, what type of data the column was?
  
  Il Mar 23 Giu 2020, 13:36 Lon Parisi lonparisi@users.sourceforge.net ha
  scritto:
  
  So I have figured out why this error is happening. Somehow, there was a
  -1#IND value in one of the columns of my MS Access table. Because of that,
  UCanAccess (or one of the other dependencies products) could not delete the
  data EVEN THOUGH the column used in the delete criteria did not have the
  -1#IND value. I removed/corrected that record and everything is now working
  fine.
  
  MS Access unable to delete using date as criteia
  https://sourceforge.net/p/ucanaccess/discussion/help/thread/33b05b6989/?limit=25#096d
  
  Sent from sourceforge.net because amadei.mar@gmail.com is subscribed to
  https://sourceforge.net/p/ucanaccess/discussion/help/
  
  To unsubscribe from further messages, a project admin can change settings
  at https://sourceforge.net/p/ucanaccess/admin/discussion/forums. Or, if
  this is a mailing list, you can unsubscribe from the mailing list.
  
  alternate
  
  If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
  - Lon Parisi - 2020-06-23
    
    It was a number (single precision)
    
    From: Marco Amadei [mailto:jamadei@users.sourceforge.net]
    Sent: Tuesday, June 23, 2020 12:11 PM
    To: [ucanaccess:discussion] help@discussion.ucanaccess.p.re.sourceforge.net
    Subject: [ucanaccess:discussion] Re: MS Access unable to delete using date as criteia
    
    Ok, what type of data the column was?
    
    Il Mar 23 Giu 2020, 13:36 Lon Parisi lonparisi@users.sourceforge.netlonparisi@users.sourceforge.net ha
    scritto:
    
    So I have figured out why this error is happening. Somehow, there was a
    -1#IND value in one of the columns of my MS Access table. Because of that,
    UCanAccess (or one of the other dependencies products) could not delete the
    data EVEN THOUGH the column used in the delete criteria did not have the
    -1#IND value. I removed/corrected that record and everything is now working
    fine.
    
    MS Access unable to delete using date as criteia
    https://sourceforge.net/p/ucanaccess/discussion/help/thread/33b05b6989/?limit=25#096d https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_p_ucanaccess_discussion_help_thread_33b05b6989_-3Flimit-3D25-23096d&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=pSvzxRvhBr7Lz3eJhfrkIIqZVsSC2dNESp5wTtM5gNk&e=
    
    Sent from sourceforge.net because amadei.mar@gmail.comamadei.mar@gmail.com is subscribed to
    https://sourceforge.net/p/ucanaccess/discussion/help/https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_p_ucanaccess_discussion_help_&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=QI7LqDhaRjHWdHvNEbeVbRLMaB7ety9Ee1htYBjZjJI&e=
    
    To unsubscribe from further messages, a project admin can change settings
    at https://sourceforge.net/p/ucanaccess/admin/discussion/forums.https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_p_ucanaccess_admin_discussion_forums.&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=H1Q8g1szpv0sXIlqTWsXdMLPO0mOwiFw_viUIHg27_A&e= Or, if
    this is a mailing list, you can unsubscribe from the mailing list.
    
    MS Access unable to delete using date as criteiahttps://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_p_ucanaccess_discussion_help_thread_33b05b6989_-3Flimit-3D25-23096d_47ba&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=E71mzuxmCrrbtlu_IvEug8ajFMUT29Qkh6pdf2jUl_k&e=
    
    Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/ucanaccess/discussion/help/https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_p_ucanaccess_discussion_help_&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=QI7LqDhaRjHWdHvNEbeVbRLMaB7ety9Ee1htYBjZjJI&e=
    
    To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/https://urldefense.proofpoint.com/v2/url?u=https-3A__sourceforge.net_auth_subscriptions_&d=DwMCAg&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=phhalDfiWCdyelQWLprnKlI_Z5esIy7motX9g4lmW0c&m=vyz6qLK5AuIhtUQ0BOE66s2nMCvWIzGWgg0vmvDYTPc&s=Q1GXsmSSSnBEyl5KTx6SIhLViggEEYgqvrOurrwOI7k&e=
    
    This material was prepared by Americas Equity Linked Sales and Trading Personnel of Merrill Lynch and is subject to the terms available at the following link: http://www.ml.com/index.asp?id=7695_71373_71377_71380&type=200.01 . Click here http://www.ml.com/email_terms/ for important additional terms relating to this e-mail. Merrill Lynch does not provide tax, accounting or regulatory advice.
    Any tax statements contained herein were not intended or written to be used, and cannot be used for the purpose of avoiding U.S., federal, state or local tax penalties. Please consult your advisor as to any tax, accounting or legal statements made herein.
    
    alternate
    
    If you would like to refer to this comment somewhere else in this project, copy and paste the following link:

I can reproduce the issue with the attached .accdb file.

screenshot

PS C:\Users\Public\Downloads\UCanAccess> ./console.bat
java version "1.8.0_241"
Java(TM) SE Runtime Environment (build 1.8.0_241-b07)
Java HotSpot(TM) 64-Bit Server VM (build 25.241-b07, mixed mode)

Please, enter the full path to the access file (.mdb or .accdb): C:\Users\Gord\Desktop\infinity.accdb
Loaded Tables:

Loaded Queries:

Loaded Procedures:

Loaded Indexes:
Primary Key  on Table1 Columns: (ID)

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0-SNAPSHOT null
        at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at java.sql.DriverManager.getConnection(Unknown Source)
        at net.ucanaccess.console.Main.main(Main.java:151)
Caused by: java.lang.NumberFormatException
        at java.math.BigDecimal.<init>(Unknown Source)
        at java.math.BigDecimal.<init>(Unknown Source)
        at java.math.BigDecimal.<init>(Unknown Source)
        at net.ucanaccess.converters.LoadJet$TablesLoader.value(LoadJet.java:1126)
        at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:859)
        at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:837)
        at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:1029)
        at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1077)
        at net.ucanaccess.converters.LoadJet$TablesLoader.access$3200(LoadJet.java:264)
        at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1579)
        at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:218)
        ... 3 more
UCAExc:::5.0.0-SNAPSHOT null
Press any key to continue . . .

Last edit: Gord Thompson 2020-07-26

infinity.accdb

MS Access unable to delete using date as criteia

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

MS Access unable to delete using date as criteia

MS Access unable to delete using date as criteia

A pure Java JDBC driver for Microsoft Access database files

Forums

Help

MS Access unable to delete using date as criteia document.SUBSCRIPTION_OPTIONS = { "thing": "topic", "subscribed": false, "url": "subscribe", "icon": { "css": "fa fa-envelope-o" } };

MS Access unable to delete using date as criteia