• 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
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

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.

 
Bartender
Posts: 20745
124
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.
 
Marshal
Posts: 5989
156
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
 
Today you are you, that is turer than true. There is no one alive who is youer than you! - Seuss. Tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!