[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2001
  • Last Modified:

Trim function in OpenOffice Base program

I have a database set up in OpenOffice with only one table called "biblio" with over 5000 records.
Most of the fields have many leading blank spaces. From what I've read, the Update statement listed below should work to clear Leading spaces in the "Title" field for ALL the records in the "biblio" table.  (using Tools/SQL from the OpenOffice menu)

I've used the OpenOffice Base Query tool to generate a SELECT statement to ensure the table & field names are spelled correctly.
When I Execute the Update, it returns "function sequence error"

I'm a complete novice at this - I'm guessing I'm missing selection criteria, or some way to loop through all the records, or something else pretty simple.

Thanks for any clues . . .




UPDATE "biblio" SET "Title" = TRIM(LEADING FROM "Title") 

(also tried putting a space in the statement)

UPDATE "biblio" SET "Title" = TRIM(LEADING ' ' FROM "Title")

also tried

UPDATE "biblio" SET "Title" = TRIM(BOTH FROM "Title")

Open in new window

0
samsterid
Asked:
samsterid
  • 9
  • 7
  • 5
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
Any of your queries seems to be correct if you were running an Oracle statement.
I dont know open office, but I think it would be more similar to ms access.
Here is the documentation that will help you:
http://www.hsqldb.org/doc/guide/ch09.html

And in any case, your query should work if you try like this:

UPDATE "biblio" SET "Title" = LTRIM("Title")
0
 
OP_ZaharinCommented:
- title is the column you would like to trim in biblio table? not sure about OpenOffice, but a standard sql syntax for trimming in oracle would be as follows: ltrim(columnname) for left space trimming, rtrim() for right and trim() for both left and right. so the update syntax is as follows:

left trim:
UPDATE "bibilio" SET "Title" = LTRIM("Title")
OR
UPDATE bibilio SET Title = LTRIM(Title)

trim both spaces leading and end:
UPDATE bibilio SET Title = TRIM(Title)
0
 
OP_ZaharinCommented:
- sorry about that wpcortes, didn't see your posting when i'm replying it...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Walter RitzelSenior Software EngineerCommented:
no problem, man.
0
 
samsteridAuthor Commented:
After trying several kinds of syntax and punctuation, I have concluded the following:
OpenOffice Base native database format (hsqldb) does NOT support any of the 3 TRIM functions.

Then I connected OpenOffice Base to a MYSQL version of the database using JDBC Connector with these results:

UPDATE database.table SET Field= TRIM(Field)  -- works perfectly
UPDATE table SET Field= TRIM(Field)  -- "You have an error in your SQL syntax . . . "
UPDATE "database"."table" SET "Field"= TRIM("Field")  -- "You have an error in your SQL syntax . . . "

So the TRIM functions work, but only if you:
  -- Connect to MYSQL database
  --  skip quotes
  -- AND reference the table with the name of the database.

The matter of the quotes around items is confusing.   Using the built-in Query Designer with the native version of the database, quotes are always incluced, but sometimes you don't need quotes:
SELECT * FROM "database"."table"  -- works fine
SELECT * FROM database.table  -- "You have an error in your SQL syntax . . . "
SELECT * FROM table  -- works fine - that's confusing!

A programmer might be able to get TRIM working with a macro in the native (hsqldb) version, but I'm not a coder.
I'll leave this question open for a few days for other's comments.

Sam





0
 
Walter RitzelSenior Software EngineerCommented:
Let me say that your conclusions around MySQL are not quite correct.
- In mysql SQL syntax, you should not use " to enquote table and column names, you should use `, which is different from '. So your command gives an error in this case because you are writing incorrectly;
- Your second statement give you error because you dont have used a use database statement before.

I'll install the open office base and test the statement with LTRIM to make sure it works or not.
0
 
Walter RitzelSenior Software EngineerCommented:
Sam,

the statement in fact do work. The problem is that the only place I was able to make it work was on Tools --> SQL
There basically any SQL statement will work. But it in fact does not work when you try to sabe a Query. Seems that queries should be of select type.

So, your solution would be create a query like this:

SELECT RTRIM(LTRIM("Table1"."Field1")) FROM "Table1"
0
 
samsteridAuthor Commented:
@wpcortes

But I wasn't using MYSQL directly - I was connected to MYSQL db through OpenOffice Base program - where double quotes around table names is normal.
Try single quotes around a table name in OpenOffice Base - I get an error - do you?

While it may be true that a SELECT statement with the TRIM functions works in Query view, it does not UPDATE the table.   What were you "able to make it work was on Tools --> SQL"??
Tools-->SQL is the only place in the Base program where an UPDATE will work as far as I know, but the TRIM functions only worked there when I was connected to MYSQL database, not the native hsqldb.
0
 
Walter RitzelSenior Software EngineerCommented:
Well, getting back to the HSQLDb statement. I have tried successfully on 'Tools --> SQL". I've executed the
following statement:
UPDATE "Table1"
SET "Table1"."Name" = RTRIM(LTRIM("Table1"."NameWithSpaces"));

I'm sending attached the database where I've tested that.
Please rename the file extension back to .ODB I have changed to .MDB to be able to upload.
NewDatabase.zip
0
 
OP_ZaharinCommented:
- single quotes is used literally for enclose character strings text while double quotes used as object name/identifiers for ie table names or column names.

0
 
Walter RitzelSenior Software EngineerCommented:
And about your error with single quotes: As I have said, you should not use quotes " or ' . You should use this character ` which is not a single quote.
0
 
OP_ZaharinCommented:
- quoting the hsqldb document guide, "HyperSQL data access and data change statements are fully compatible with the latest SQL:2008 Standard". the same document also shows sample on UPDATE statement is the same with or without the tablename reference before a columnname. the normal update statement will work fine:
http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#N1293B 

- wpcortes have also shared this document earlier. trim, ltrim, rtrim also is supported:
http://www.hsqldb.org/doc/guide/ch09.html 
0
 
OP_ZaharinCommented:
- to use the trim function in hsqldb, you need to used
: LTRIM(RTRIM(string))
: TRIM(BOTH FROM string) - use LEADING or TRAILING or BOTH
0
 
samsteridAuthor Commented:
Does anybody know what this `````` character is called?  I just glanced at it above and though it was a single quote - sorry . . .
0
 
samsteridAuthor Commented:
@wcortes (thanks for going to all that trouble to install OpenOffice and testing!)
I did run this command on the database you created:

UPDATE "Table1"
SET "Table1"."Name" = RTRIM(LTRIM("Table1"."NameWithSpaces"));

And that worked great.

But I think there is something wrong with the structure of my table, because it failed when I tried substituting my own table and field names.
UPDATE "biblio"
SET "biblio"."Title" = RTRIM(LTRIM("biblio"."Title"));

-- 1: syntax error, unexpected '.', expecting SQL_EQUAL

I am attaching a short version of my own database, which I built using the wizards in OpenOffice. (used temp .mdb extension . .)

Thanks again - I think we're very close


 books-db.zip
0
 
Walter RitzelSenior Software EngineerCommented:
I'll look at it at the end of my day... Dont have open office at the office :)
0
 
Walter RitzelSenior Software EngineerCommented:
Sam, it did not work. Can you send me the complete database to test?
0
 
samsteridAuthor Commented:
my bad - I did not include the folder with the dbf and dbt files . . .
I am sending the whole thing this time anyway - it compressed very well - 22MB down to under 1MB - must be all that white space . . .

Had to change all 3 file extensions in order to upload:
mdb --> odb
doc --> dbt
pdf --> dbf

Thanks for all your time - Sam

Book-database.zip
0
 
Walter RitzelSenior Software EngineerCommented:
It was hard, but I have found a solution. Your problem was, that altough you were using openoffice base, you did not have created a real openoffice base database - you were opening a dbase file, something that is a database technology almost older than me :) - what explain your problem with the update statement.

So I have converted your dbase file to a real openoffice base and now the update is working. You can chekc your title field.

To convert, I've created a odbc connection to the dbase file (biblio.dbf) and used to import information to Excel. After that I've selected all columns on excel, hit Ctrl-C and then a simple Ctrl-V into the table area of a openoffice opened database.

Here is the link for the process explained: http://sheepdogguides.com/fdb/fdb1imp1.htm
biblio.zip
0
 
samsteridAuthor Commented:
Success!

But I wish I had started with a proper DB . . .
It was a while ago, but I think I started with a template for a book database that I found somewhere online, and it was supposed to be for OpenOffice; then I created a Form for my client to start populating the DB.

So I have no idea how it devolved to dbase format.  Now I have to figure out how to get my customized Form to link to your version of the database, but that's my problem.

wpcortes - Thanks so much again for all that work!  I wish I could award at least 2000 points . . .

Sam
0
 
samsteridAuthor Commented:
wpcortes went the extra mile, plus a few more, to eventually figure out I had started with the wrong database format.  Experts Exchange really delivers when you're desperate for a detailed, competent answer.

Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now