Base: Data modification queries? append, update?

Creating tables and queries

Base: Data modification queries? append, update?

Postby JohnTheWysard » Wed Jun 11, 2008 2:07 am

I'm a new user of OpenOffice Base, but I have quite a bit (too much??) familiarity with other dialects of SQL. My current problem is that I'm trying to massage a non-normalized spreadsheet type of list (newspaper subscribers and subscription renewals to be exact) into normalized tables.

In Access or SQL/Server I'd do this by creating Group By queries or DISTINCT queries, and changing them into a MakeTable or Append query. I am sure there is a way to do this in OOBase, but I haven't found it! Any suggestions?

I'm running a freshly installed instance of OO 2.4.1 on WindowsXP.

I'm looking forward to participating in this group; as I get more familiarity with the way things are done in this programming environment, I hope I'll be able to help others.

John W. Vinson aka John the Wysard (no false modesty there!)
OOo 2.4.X on Ms Windows XP
User avatar
JohnTheWysard
 
Posts: 3
Joined: Tue Jun 10, 2008 10:36 pm
Location: Parma, Idaho United States

Re: Base: Data modification queries? append, update?

Postby QuazzieEvil » Wed Jun 11, 2008 4:14 am

Base does not yet have Append, Update, or Delete query objects--as in MS Access. This must be done with SQL commands in Base. If you need to perform complex updates/deletes/inserts, you may want to use Basic macros to execute the SQL commands. See http://www.geocties.com/rbenitez22 docs on Basic programming and Base. You can also execute SQL with the SQL dialog (Tools | SQL..) .
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Base: Data modification queries? append, update?

Postby DrewJensen » Wed Jun 11, 2008 4:19 am

Well, if you are looking for a nice GUI way to do that and not found it - it is because it is not there.

You need to do this using standard SQL commands.

These command would be entered into the SQL window ( Tools>SQL ). Which is friggin modal...pet peeve.

So - you can use the Query Designer to create you select statement and then switch to SQL virew - copy the SQL statement to the clipboard. Open the SQL window, paste it in and fix it up as needed then execute it.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
Volunteer
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Base: Data modification queries? append, update?

Postby JohnTheWysard » Thu Jun 12, 2008 1:43 am

DrewJensen wrote:so - you can use the Query Designer to create you select statement and then switch to SQL virew - copy the SQL statement to the clipboard. Open the SQL window, paste it in and fix it up as needed then execute it.


Well, that would be fine... I'm quite comfortable writing SQL. But it seems that my syntax differs from OOBase's syntax! The ANSI syntax

INSERT INTO tablename(field, field, field) SELECT field, field, field FROM table

and assorted variants does not work. Is there a SQL language definition document somewhere?

Thanks for the help!

John W. Vinson
User avatar
JohnTheWysard
 
Posts: 3
Joined: Tue Jun 10, 2008 10:36 pm
Location: Parma, Idaho United States

Re: Base: Data modification queries? append, update?

Postby JohnTheWysard » Thu Jun 12, 2008 1:45 am

QuazzieEvil wrote:Base does not yet have Append, Update, or Delete query objects--as in MS Access. This must be done with SQL commands in Base. If you need to perform complex updates/deletes/inserts, you may want to use Basic macros to execute the SQL commands. See http://www.geocties.com/rbenitez22 docs on Basic programming and Base. You can also execute SQL with the SQL dialog (Tools | SQL..) .


hmmm... getting a Yahoo "page not found" error on that...

I'm willing to use Basic programming if necessary but surely this can be done with a SQL query! Is there a SQL language syntax document somewhere?

John W. Vinson
User avatar
JohnTheWysard
 
Posts: 3
Joined: Tue Jun 10, 2008 10:36 pm
Location: Parma, Idaho United States

Re: Base: Data modification queries? append, update?

Postby Sliderule » Thu Jun 12, 2008 2:07 am

John:

You asked:
John wrote:Well, that would be fine... I'm quite comfortable writing SQL. But it seems that my syntax differs from OOBase's syntax! The ANSI syntax

INSERT INTO tablename(field, field, field) SELECT field, field, field FROM table

and assorted variants does not work. Is there a SQL language definition document somewhere?


Please look at the link below, and, especially at the Suggestions part by Sliderule ( AKA that is yours truly ):

http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=5627

I would be willing to GUESS ( but cannot say for certain ) that your SQL issue is probably explained at:

http://www.hsqldb.org/doc/guide/ch09.html#expression-section

. . . so . . . if you place your Table and Field names between double quotes . . . this will 'resolve' your issues.

Try something like this and of course, change tablename and field to the appropriate name . . . that ARE case sensitive to the exact name as defined in your table definition:

Code: Select all   Expand viewCollapse view
INSERT INTO "tablename" ("field", "field", "field") SELECT "field", "field", "field" FROM "table";
INSERT INTO "MyTable" ("Field_01", "Field_02", "Field_03") SELECT "Field_01", "Field_02", "Field_02" FROM "Next_Table";


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.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Base: Data modification queries? append, update?

Postby DrewJensen » Thu Jun 12, 2008 2:09 am

Base is really just a front end to any database engine - it includes an embedded version of HSQLdb if you want the data in the actual Base file however.

The user Manual for that can be found at their site, http://hsqldb.org

There are few things on the OO.o wiki also, but go to the source to start with...if your example SQL is just that now problem but is that is what you used then yes it failed.

HSQLdb has the following quoting rules:
ALL Identifiers must be double quoted if they use mixed-case or all lower case names. ( that includes your column and table aliases by the way )

The SQL window helps you here with a 'feature' - if casts everything in the statement to upper case that is not double quoted.
Meanwhile the GUI designers helps you with a 'feature' - .it double quotes everything for you...

SO - say you create a table in the SQL window: create table_1 ( id Identity, fname varchar(40) );
Now you enter a statement later in the SQL window: select * from table_1
Everything works, because everything is in upper case in the database...

Now you use the GUI table designer to create the table like: table_2( idx identity, lname varchar(100))

You open the SQL window and enter: select * from table_2 - error table not found. Cause it really is named "table_2"

Cool go to the GUI query tool, open it in sql view and enter: select * from table_1 - error because there is no "table_1" it is TABLE_1

LOL - it really is true, but really isn't that big a deal after a day or two...

 Edit: Once again I am too slow to beat sliderule to the answer... *!&^& 
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
Volunteer
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 7 guests