OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Select ... INTO fn gets 1000 return code from MySQL 5.x
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Fri Feb 23, 2007 12:01 pm    Post subject: Select ... INTO fn gets 1000 return code from MySQL 5.x Reply with quote

I am having a problem with a simple querry of a database. I am trying to purify a database that has multiple records for each person (up to 20+.) This is my first real time using SQL for productive work.

My config is OO 2.1 and MySQL 5.0.

The statement looks like the folllowing:

SELECT DISTINCT "column name" INTO "fn" FROM "table name"

It comes back with a syntax error that appears generic. The RC of 1000 appears to be coming from MySQL

If I use only

SELECT DISTINCT "column_name" FROM table_name

It works.

I know this is sketchy, but I'm not at my workstation.

Can any one help?
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Fri Feb 23, 2007 12:41 pm    Post subject: Reply with quote

Hi Bob


Can you explain what "fn" is? Is it an other table? If it is then you'll want to do:
Code:

insert into fn (colum_name)
SELECT DISTINCT "column_name" FROM table_name


If not then please post some more info.

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Fri Feb 23, 2007 1:29 pm    Post subject: Reply with quote

>>>Can you explain what "fn" is? Is it an other table? If it is then you'll want to do:

Hi Mark!

fn stands for File Name...ie...any file name

Here's the description (shortened) from the MySQL webpage:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
select_expr, ...
[FROM table_references
[INTO OUTFILE 'file_name' export_options


http://dev.mysql.com/doc/refman/5.0/en/select.html

At it's most simple use it allows an easy method to backup a database:

SELECT * FROM 'Table1' INTO OUTFILE 'file_name'

In my case and configuration, it does not work.

Thanks for the quick comeback.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Fri Feb 23, 2007 2:40 pm    Post subject: Reply with quote

Hi Bob

The problem is that ooBase doesn't recognize that SQL, so you're going to have to do that from your command line, where you need to type:
Code:

mysql -u<user> -p<password> <database

and then:
Code:

SELECT * FROM users INTO OUTFILE '/tmp/user.tmp';

Don't put quotes around the table name.

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Feb 23, 2007 3:19 pm    Post subject: Reply with quote

bmcswain:

Just another thought / method to accomplish task.

From within Base . . . you can also, if you desire, execute SQL commands directly against the MySQL database. Of course, the SQL must be 'valid'.

In order to accomplish this:
  1. open your MySQL database within OpenOffice
  2. on the left, click on the Queries icon under Database
  3. on the right, click on Create Query in SQL View...
  4. click on the icon: SQL with a check mark
  5. enter into box below your SQL statement
  6. Press the execute icon
  7. FYI . . . a statement will be returned indicating No Result Set Was Produced

For example, if you want to create a file with the contents of your data, from a table named, for example, testday ( change for your needs ) , and, the output into a file name of c:/testday.txt ( change for your needs ) the following code would work:
Code:
SELECT *
   FROM testday
   INTO OUTFILE 'c:/testday.txt'

if you wanted your output to be 'delimited' with a comma ( a comma between each field ), use the following:
Code:
SELECT *
   FROM testday
   INTO OUTFILE 'c:/testday.txt' FIELDS TERMINATED BY ','

Another example might be:
Code:
SELECT DISTINCT  *
   FROM testday
   INTO OUTFILE 'c:/testday.txt' FIELDS TERMINATED BY ','


I hope this helps, please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Fri Feb 23, 2007 6:27 pm    Post subject: Reply with quote

Sliderule:

Bamboo or Plastic?

LOL

I still have my Bamboo AND my $3.98 Plastic ... artifacts of an age gone by! (I still remember the "rich kids" in engineering that got the first 4 function TI calculators!)

Yes from inside BASE you can get a SQL free form entry. BASE does some of the work for you and they all you do is put the SELECT ... statement in. That is the method I am using.

Neither the Wizard nor the Design View have a place to specify DISTINCT.

If I ever get it working your "FIELDS TERMINATED BY ','" will come in handy. I believe I'll try that just to force a different path through the code.

Let you know the results.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Fri Feb 23, 2007 6:35 pm    Post subject: Reply with quote

OK here's the SELECT statement and the resulting errors:

SELECT DISTINCT "Name: Last,First,Middle", "Res Addr: Full", "Res Addr: City,State,Zip"
FROM "Precinct 69"
INTO OUTFILE "d:/pct_69_uniq.txt"
FIELDS TERMINATED BY ','

3 errors:

1. SQL Status: HY000
Error code: 1000

Syntax error in SQL expression

2. SQL Status: HY000
Error code: 1000

3. SQL Status: HY000
Error code: 1000

syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

NOTE if I eliminate INTO OUTFIELD and TERMINATED BY..
It runs.... but I don't have the resulting data to continue my work.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Feb 23, 2007 8:19 pm    Post subject: Reply with quote

bmcswain:

While it is hard for me to 'duplicate' what you are doing, since, I do NOT have your database on a machine I can use, but, . . . try this:
Code:
SELECT DISTINCT
      "Name: Last,First,Middle",
      "Res Addr: Full",
      "Res Addr: City,State,Zip"
   FROM "Precinct 69"
   INTO OUTFILE 'd:/pct_69_uniq.txt'
        FIELDS TERMINATED BY ','


The BIG difference is . . . change the 'delimiter' around the file name to contain the output, from DOUBLE QUOTES ( " ), to SINGLE QUOTES ( ' ).

Also, the field names . . . are they really:
  • Name: Last,First,Middle
  • Res Addr: Full
  • Res Addr: City,State,Zip

If so, great.

By the way, my sliderule is 'plastic'.

Sliderule
Back to top
View user's profile Send private message
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Sat Feb 24, 2007 9:13 am    Post subject: Reply with quote

Sliderule.... Plastic was always my favorite.... Bamboo would dry out and stick.... no slidy

Using your format, it still fails with same error messages.

The more I learn the more this looks like a problem or incompatibility between OO 2.1 and MySQL 5.0

Here's the error msg broken apart:

syntax error, <---- somethings out of order, misspelled, misplaced commas, etc

unexpected $end, <--- it found a statement terminating condition

expecting BETWEEN or IN or SQL_TOKEN_LIKE <--- note these are keywords, but not a part of the SELECT verb

In fact when I search for those keywords in the MySQL 5.0 reference, I could find none that seem to relate.

http://dev.mysql.com/doc/refman/5.0/en/index.html

I'm off to the library to get some books on SQL. I'm going to have to do what Mark B. says to do... use the command line.

I just noticed that Mark B. also says "ooBase doesn't recognize that SQL." What versions doe it recognize? THIS could be my problem.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Sat Feb 24, 2007 9:24 am    Post subject: Reply with quote

ahhhhh HA!

MySQL is not running!

OO Base uses http://hsqldb.org/
Quote:
However, by default OpenOffice.org creates all-in-one database files, using the HSQLDB database engine, which in fact is a Java database. So, if you have a Java Runtime Environment (JRE) installed, OpenOffice.org Base will create an embedded HSQLDB database when you choose "Create a new database" in the "New Database" wizard (File|New|Database).


Here is the syntax for HSQLDB SELECT verb (Note no OUTFILE clause):

SELECT [{LIMIT <offset> <limit> | TOP <limit>}[1]][ALL | DISTINCT]
{ selectExpression | table.* | * } [, ...]
[INTO [CACHED | TEMP | TEXT][1] newTable]
FROM tableList
[WHERE Expression]
[GROUP BY Expression [, ...]]
[HAVING Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } | INTERSECT [DISTINCT] } selectStatement]
[ORDER BY orderExpression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];


So how do I take the selected records/tables and put them into a file? (INTO doesn't do it.)
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Feb 24, 2007 9:44 am    Post subject: Reply with quote

bmcswain:

According to MySQL documentation:
MySQL Documentation wrote:
The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the filename.

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.


Just as an FYI . . . another method would be to create your SQL Query, WITHOUT the INTO clause, and then 'copy' the data to a Calc Spreadsheet . . . ( if you need to know how to do this, let me know ) . . . then . . . you could 'save' Calc Spreadsheet as a CSV ( Comma Separated Value ) file.

Also, just to confirm . . . the database that you are accessing through OpenOffice . . . is it a MySQL database, right ? ? ?

And, when you attempted to Run the 'query' . . . Select . . . INTO OUTFILE . . . did you 'check' the SQL button, with the checkmark on the toolbar ? ? ? The reason for this is so the query will run 'directly' against your MySQL database, without first going through the OpenOffice GUI ( Graphic User Interface ) . . . becasue, OpenOffice does not support the 'INTO OUTFILE' . . . BUT . . . MySQL does.

Just a thought.

Sliderule
Back to top
View user's profile Send private message
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Sat Feb 24, 2007 11:15 am    Post subject: Reply with quote

>>>Also, just to confirm . . . the database that you are accessing through OpenOffice . . . is it a MySQL database, right ? ? ?

Well ... No... maybe!

I thought it was because MySQL was running yesterday when I was having problems. Now it's not running which why is said it must be http://hsqldb.org/ ,

I created the database in OO. It started as a CSV file. I opened it with CALC (spreadsheet), and copied all cells. Then I started BASE to create a new DB. When the table had been defined I pasted the cells into the table.

So it is really an OO db. .... which is another reason to look for solutions within the constraints of HSQLDB.

Thanks for all the assistance.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Feb 24, 2007 11:51 am    Post subject: Reply with quote

Bob:
Bob wrote:
So it is really an OO db

Well, now that we know it is a Base database, NOT a MySQL database . . . that explains why you could NOT use the MySQL SQL successfully ( with the INTO OUTFILE ) terminology.

I suspect, a 'simple' and straightforward way for you to create a CSV file from the OpenOffice Base file ( table Precinct 69 ) is:

  1. Create a query, in Base, like this:
    Code:
    SELECT DISTINCT
          "Name: Last,First,Middle",
          "Res Addr: Full",
          "Res Addr: City,State,Zip"
       FROM "Precinct 69"

  2. Save the Query
  3. Open a new Calc Spreadsheet
  4. Press F4
  5. Click on your Database name
  6. Click on Queries
  7. Click on the query you just created and DRAG it to Cell A1 of your spreadsheet ( this will copy the results of the query to cells in the spreadsheet )
  8. From the Calc Menu: File -> Save As..
  9. In the Save as type: box . . . select . . . Text CSV (.csv)
  10. Give the new file a name of your choice
  11. Answer the questions . . . and . . . the new file will be saved in the designated directory

I hope this helps . . . please be sure to let me / us know.

Sliderule
Back to top
View user's profile Send private message
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Sat Feb 24, 2007 12:27 pm    Post subject: Reply with quote

THAT'S IT!


Now I can go foward with the purified data - 1 record per person.


That was way too hard.

BTW if anyone is interested, my next challenge is to split one of the column into address number and street name.

But you ' all have been great.

Thanks.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
bmcswain
General User
General User


Joined: 23 Feb 2007
Posts: 11

PostPosted: Sat Feb 24, 2007 12:33 pm    Post subject: Reply with quote

THAT'S IT!


Now I can go foward with the purified data - 1 record per person.


That was way too hard.

BTW if anyone is interested, my next challenge is to split one of the column into address number and street name.

But you ' all have been great.

Thanks.
_________________
Bob
Back to top
View user's profile Send private message AIM Address
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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