| View previous topic :: View next topic |
| Author |
Message |
peg110
Newbie
Joined: 20 Aug 2008
Posts: 3
Location: Pennsylvania
|
 Posted: Wed Aug 20, 2008 5:10 am Post subject: [SOLVED] problem with INSERT statement |
 |
|
Ok, I am new to HSQL , but not to SQL. I have worked with MS SQL Server, Oracle DB, MS Access, and MySQL (and probably a few others). I am now trying to take advantage of OOo's Base.
I have successfully created a table. I have even created a form for easy (manual) entry of data to that table.
I have a lot of data that I need to insert and can easily construct the "Bulk" Insert statements to make this process easier. However, I am not having luck doing this.
I am running OpenOffice.org 2.4.1 (on a very beefy machine with plenty of resources)
A bit about the table (Called TicketApprovers)
id - Int - AutoValue - PK
ticket_id - varchar(50) - Not Null
group - varchar(50) - Not Null
userid -varchar(10) - Not Null
lname - varchar(25) - Null
fname - varchar(25) - Null
mi - varchar(1) - Null
location - varchar(50) - Null
In the query designer (using the Run SQL command directly feature), I ran the following query:
| Code: | INSERT INTO TicketApprovers (id, ticket_id, group, userid, lname, fname, mi, location) VALUES( '', 'CHG0000101230', 'SM_234_SBS', 'AC3D412', 'Public', 'John', 'Q', 'San Mateo CA');
|
When I execute it (F5 or press the "Run Query Button") I get the following error:
Syntax error in SQL expression and then I press the "MORE" button and I find a list of 3 errors. (all say "ERROR") in the description i get the following:
Error 1: (in list)SQL Status: HY000
Error code: 1000
Syntax Error in SQL Expression
Error 2: (in list)SQL Status: HY000
Error code: 1000
Error 3: (in list)SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE
So after doing a little searching I found that apparently I can not use the Query Design to do INSERTS, UPDATES or DELETES, but only SELECTS (a very serious limitation I would think, but none-the-less, I press on). And that I should use Execute SQL statements by going to Tools -> SQL.... from the menu bar.
So I launch it and enter the same query from above in the "Command to execute" and press the Execute button. The status returns 1: Table not found in statement [INSERT INTO TicketApprovers]
I checked the HSQLDB Documentation ( http://hsqldb.org/doc/guide/ch09.html#insert-section ) for the INSERT syntax and I am presented with
INSERT INTO table [( column [,...] )]
{ VALUES(Expression [,...]) | SelectStatement};
This leads me to believe that my syntax is correct, but alas, I cannot get it to work. I am at a loss of what to do next. Any help would be greatly appreciated.
_________________
peg110
Last edited by peg110 on Wed Aug 20, 2008 7:14 am; edited 1 time in total |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Wed Aug 20, 2008 6:14 am Post subject: |
|
|
peg110:
You are almost there. According to HSQL documentation . . . when you send an SQL statement DIRECTLY to the database engine, OpenOffice does not 'parse' it first . . . so . . . it is sent exactly as you wrote it. BUT, and this is the 'catch' . . . the Table names, Field names, and, View names . . . must 'match' exactly -- UPPER CASE / lower case. This is done by wrapping them in "double quotes". There is an 'exception' . . . that is . . . IF you have 'not' put the names in double quotes, it ( the HSQL database engine ) will 'automatically' make them all UPPER CASE.
| HSQL Documentation: http://hsqldb.org/doc/guide/ch09.html#expression-section wrote: | A unquoted identifier (name) starts with a letter and is followed by any number of ASCII letters or digits. When an SQL statement is issued, any lowercase characters in unquoted identifiers are converted to uppercase. Because of this, unquoted names are in fact ALL UPPERCASE when used in SQL statements. An important implication of this is the for accessing columns names via JDBC DatabaseMetaData: the internal form, which is the ALL UPPERCASE must be used if the column name was not quoted in the CREATE TABLE statement.
Quoted identifiers can be used as names (for tables, columns, constraints or indexes). Quoted identifiers start and end with " (one doublequote). A quoted identifier can contain any Unicode character, including space. In a quoted identifier use "" (two doublequotes) to create a " (one doublequote). With quoted identifiers it is possible to create mixed-case table and column names. Example:
CREATE TABLE "Address" ("Nr" INTEGER,"Name" VARCHAR);
SELECT "Nr", "Name" FROM "Address"; |
Bottom line . . . either:- Change all of your INSERT statements, so the names ( both table and field ) are in double quotes:
| Code: | | INSERT INTO "TicketApprovers" ("id", "ticket_id", "group", "userid", "lname", "fname", "mi", "location") VALUES( '', 'CHG0000101230', 'SM_234_SBS', 'AC3D412', 'Public', 'John', 'Q', 'San Mateo CA'); |
First, ALTER your table and field names ( with ALTER SQL statements ) in the database . . . to all UPPER CASE NAMES ( this is how I do it ) . . . so . . . your INSERT, SELECT, UPDATE SQL will work, regardless of how you enter the names, assuming they are 'unquoted'.
For example: | Code: | ALTER TABLE "TicketApprovers" RENAME TO "TICKET_APPROVERS"; --Make Names all Upper Case
ALTER TABLE Ticket_Approvers ALTER COLUMN "id" RENAME TO "ID"; --table already UPPER case
ALTER TABLE Ticket_Approvers ALTER COLUMN "ticket_id" RENAME TO "TICKET_ID"; |
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
peg110
Newbie
Joined: 20 Aug 2008
Posts: 3
Location: Pennsylvania
|
| Posted: Wed Aug 20, 2008 7:14 am Post subject: |
|
|
Actually, I had to make one more alteration to your suggestion.
The query needed to be
| Code: | | INSERT INTO "TicketApprovers" ("id", "ticket_id", "group", "userid", "lname", "fname", "mi", "location") VALUES(NULL, 'CHG0000101230', 'SM_234_SBS', 'AC3D412', 'Public', 'John', 'Q', 'San Mateo CA'); |
Note the NULL in the first item of values. Because this item is an integer AND an auto increment, it errored with the 2 quotes '' because it was looking for a string.
Once that change was made... everything worked well.
I guess I will have to get used to "Quoting" my fields and tables. I don't like have my table names all Upper case. In fact often times I will use camel humped names. Not a big deal however.
Thanks for the help.
_________________
peg110
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
