• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
Bartenders:
  • Carey Brown
  • salvin francis
  • Claude Moore

Problem with SQL in an Access database  RSS feed

 
Ranch Hand
Posts: 443
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For years I have been using in my programs statements like "SELECT * FROM user WHERE user_name=mgr", but I just learned that nowadays I have to use "user_name='mgr'". Now I am trying to verify that I also have to use "'" for Date and Number; the following code is giving me the error
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: DATE_CREATED
I am getting the same error if I don't use "'".

Similar code works fine for strings.

 
Saloon Keeper
Posts: 20655
122
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just realized that you're using MS-Access. My condolences. Don't use it for web applications.

Yes, you do have to quote dates. Otherwise SQL will usually take something like 2019-05-08 to be an arithmetic expression with a value of 2006. Which isn't at all what you were expecting. Or, if you've defined the column as a date/time object in your schema, it may just yell at you.

For numbers, you don't quote them unless the number is defined in the database schema as a text value. In which case, you do put quotes around them, because they're really just text digits and not literally a number as far as the database is concerned.
 
Sheriff
Posts: 5931
155
Chrome Eclipse IDE Java Postgres Database Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Whether you use a single quote (') or not depends on the column type.  Everything that is no a true number is surrounded by single quotes.  What I mean by true numbers is that you can have a text column, but if you want to add the value 123 to it, you still need quotes.  Said another way, it's not the data being inserted that you concern, but the type of the column.

See https://www.w3schools.com/sql/sql_where.asp
 
Maybe he went home and went to bed. And took this tiny ad with him:
Create Edit Print & Convert PDF Using Free API with Java
https://coderanch.com/wiki/703735/Create-Convert-PDF-Free-Spire
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!