[Solved] Problems with Sql queries

Creating tables and queries

[Solved] Problems with Sql queries

Postby Bhujang » Sat Feb 11, 2012 4:49 pm

I have just started Database Basics, so I dnt know much about this field. My teacher said that Sql Queries are Case Insensitive and he was using Ms-Access and Oracle, he showed us on both by writing some simple queries and I saw that queries were running even if he was changing cases in table name and column names, he also showed us that while creating a table was not differentiating between field names with different cases, for example if I have entered SUBJECT as a field name then the table was not accepting subject as another field name.

But on home I have Openoffice.org so I started the base and went through steps--
1- create table in design view
2-Entered some field names and strange thing was both SUBJECT and subject were accepted as field names in a single table.
3- Saved the table with name SAMPLE
4-Went to created Queries in SQL view and when I tried to run a simple query (select *
from sample) it showed error so I have to use SAMPLE as table name then it ran fine. this was not happening in Oracle in my teachers computer.

I also read on the link http://www.w3schools.com/sql/sql_syntax.asp that SQL is not case sensitive, so if someone can tell me in simple language why this is happening, I will be very thankfull to him.
Last edited by Bhujang on Sat Feb 11, 2012 7:26 pm, edited 2 times in total.
OpenOffice 3.3 on Windows Vista SP2
Bhujang
 
Posts: 6
Joined: Sat Feb 11, 2012 4:21 pm

Case-sensitive object names imposed by Base GUI facilities

Postby DACM » Sat Feb 11, 2012 5:45 pm

Bhujang wrote:My teacher said that Sql Queries are Case Insensitive...

I also read on the link http://www.w3schools.com/sql/sql_syntax.asp that SQL is not case sensitive...

Your teacher is over-simplifying the issue, or simply failing to mention that enclosing the column name in quotes during creation will allow a case-sensitive name -- per the SQL standards. A quick search reveals this is true of Oracle, PostgreSQL, MySQL, HSQLDB and I presume many others.

However, when we use Base as the front-end to our database, case-sensitivity is imposed by Base in many cases. Specifically, the visual Table Designer in Base adds quotes to all object names during table/column creation, thereby enabling case-sensitivity. And, the Base query parser enforces the use of double-quotes around names (Tables, Columns, etc.), thereby enforcing case-sensitivity even all names are UPPERCASE. You can bypass the query parser in Base but it's not always a good idea, since the parser facilitates writable queries and parameter queries.
see: https://forum.openoffice.org/en/forum/viewtopic.php?p=199303#p199303
see also: Learning SQL using H2's all-in-one Driver+Engine+Console
...
Last edited by DACM on Mon May 19, 2014 3:32 am, edited 14 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Problems with Sql queries

Postby eremmel » Sat Feb 11, 2012 5:56 pm

Welcome in the world after school! Databases can use different naming-schema. When using a database or any other application you have always check the rules of that application; the same applies to OOo vs. MsOffice: compatibility has also its limitations. Have fun with exploring OOo!
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1032
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Problems with Sql queries

Postby Bhujang » Sat Feb 11, 2012 6:33 pm

Thanks both of you, but why the link http://www.w3schools.com/sql/sql_syntax.asp says that SQL is not case sensitive? and what should I do so that I can use table name without worrying about cases(upper or lower)?
OpenOffice 3.3 on Windows Vista SP2
Bhujang
 
Posts: 6
Joined: Sat Feb 11, 2012 4:21 pm

Re: Problems with Sql queries

Postby RoryOF » Sat Feb 11, 2012 6:42 pm

The link you cite says
We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.

So their assertion of case insensitivity must be read with respect to those applications.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 28560
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Problems with Sql queries

Postby Bhujang » Sat Feb 11, 2012 6:58 pm

This link was posted by user DACM http://user.services.openoffice.org/en/forum/viewtopic.php?p=199303#p199303 which says that Once you have ALL-CAP names throughout your database, you can then refer to those Tables and Columns in SQL using whatever case-convention you prefer -- without quotes -- again because un-quoted names are converted to ALL-CAPS internally. So if the column name is FIRSTNAME, you can refer to it in a [native] query as FirstName, or FIRSTname, or firstname, or whatever camel-case-convention you like, without using quotes.

So I saved my table with all Capital Letters including Table name, but even then using table name in lowercase in query is giving error. why?
se;e
OpenOffice 3.3 on Windows Vista SP2
Bhujang
 
Posts: 6
Joined: Sat Feb 11, 2012 4:21 pm

Re: Problems with Sql queries

Postby DACM » Sat Feb 11, 2012 7:11 pm

Bhujang wrote:...but why the link http://www.w3schools.com/sql/sql_syntax.asp says that SQL is not case sensitive?

Well perhaps because without utilizing quotes, especially at the point of table/column creation, "SQL is not case sensitive." So if you never use quotes then case-sensitivity is a non-issue.

Bhujang wrote:and what should I do so that I can use table name without worrying about cases(upper or lower)?

A database front-end is another matter. Base is simply a GUI front-end combined with a built-in, back-end, database engine (HSQL). Base actually enforces case-sensitivity in table/column names, for whatever reason, particularly through it's query parser. So if you create your tables/columns with the Base GUI using an ALL-CAP naming-convention, then you can by-pass the Base "parser" in most cases except when you require a writable result-set or parameter query. Simply select 'direct SQL' for Queries or 'Native' when embedding SQL commands (embedded queries for List Content), or utilize 'Tools > SQL...' which always bypasses the Base query parser.

Bhujang wrote:So I saved my table with all Capital Letters including Table name, but even then using table name in lowercase in query is giving error. why?

Because you didn't understand that you can bypass the Base query parser (the operative word is 'native' query), which then passes the SQL directly to the database engine.

This issue bites us all from time-to-time: http://user.services.openoffice.org/en/forum/viewtopic.php?p=220192#p220192
...
Last edited by DACM on Sat Feb 11, 2012 7:32 pm, edited 4 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Problems with Sql queries

Postby Bhujang » Sat Feb 11, 2012 7:19 pm

Thanks DACM after some trouble I have understood.
OpenOffice 3.3 on Windows Vista SP2
Bhujang
 
Posts: 6
Joined: Sat Feb 11, 2012 4:21 pm

Re: Problems with Sql queries

Postby DACM » Sat Feb 11, 2012 7:27 pm

Well several folks added to this discussion...and I wasn't aware of the details until recently, so welcome to the club. ;)
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Solved] Problems with Sql queries

Postby rudolfo » Sat Feb 11, 2012 9:12 pm

Looking at the initial post I can't resist to mention that there is a small file based database SQLite. Its documentation site uses the title SQL As Understood By SQLite indicating that the SQL standard is only too often interpreted differently. If your teacher uses Oracle and the main emphasis is on the SQL syntax OOo Base is not the best tool for learning. SQLite comes with a console database terminal comparable to SQL*Plus from Oracle or the mysql command line client that comes with MySQL.
Although Base has the Tools -> SQL option to directly send Data Definition Language and Data Manipulation commands (update,delete, insert) it can't handle returned results. For queries you have to swap to a query window and run the query there.

If you are working in the SQLite command line tool you are not distracted by case sensitivity or quotes, but you can focus on the SQL logic. I stop my campaign here and mention one of the drawbacks with sqlite: The use of column types is different from other database engines, on their website they talk about "Manifest typing". Particularly if you are dealing with dates and differences between dates quite a lot, this might become a show stopper.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: [Solved] Problems with Sql queries

Postby DACM » Sun Feb 12, 2012 12:28 am

rudolfo wrote:If your teacher uses Oracle and the main emphasis is on the SQL syntax [then] OOo Base is not the best tool for learning...
Although Base has the Tools -> SQL option to directly send Data Definition Language and Data Manipulation commands (update,delete, insert) it can't handle returned results. For queries you have to swap to a query window and run the query there.

I stop my campaign here...sqlite

Very good point. Base is probably inappropriate as an SQL learning tool -- although I'm a Query Builder cripple oftentimes.

Speaking of cute little front-ends for learning SQL (or general database administration):
The H2 Console is built-into the executable h2.jar. So if you have Java and a default web browser installed on virtually any platform, you can simply double-click the h2.jar file and get a nice, graphical, SQL console. All you need is the h2.jar file which you can carry around on a USB flash drive. I just tried it, and I had the Console popup in my browser, with a new database created, and a table with two records, all by clicking on the boilerplate SQL templates/examples -- all in a total of about 30 seconds. I've never seen anything like it -- although now I'm noticing that HSQLDB 2.x has a similar GUI database-manager built-into it's executable as well: hsqldb.jar Very nice! AFAIK, H2 and HSQLDB are the most ANSI-compliant SQL engines available which provides a good learning environment perhaps when combined with H2's rather impressive SQL Grammar documentation. And the H2 Console is cross-database so it can be used with many other databases using JDBC drivers as available.

Here's some other free, cross-platform, cross-database, SQL front-ends:
SQL Workbench/J
SQirreL SQL
SQL Developer
Execute Query
iSQl-Viewer
and the amazing, but not free RazorSQL
among others
...
Last edited by DACM on Fri Feb 13, 2015 9:37 am, edited 2 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Solved] Problems with Sql queries

Postby rudolfo » Sun Feb 12, 2012 3:27 pm

Thanks DACM! The H2 Console looks promising, particularly because it can be configured to use it for other database backends, as well. It's basically really only a doubleclick on the jar file. It is still good to read the tutorial, because then you know that closing the browser doesn't shutdown the H2 database backend, but that this needs to be done on the configuration page with the shutdown button.
But as the backend process occupies only 10 MByte and the tcp port 8082 most user won't recognize it, if they keep it running until they shutdown their computer (or log out).
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest