Select ... INTO fn gets 1000 return code from MySQL 5.x

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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

Mark B · Posted: Fri Feb 23, 2007 12:41 pm Post subject:

Hi Bob

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

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

>>>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

Mark B · Posted: Fri Feb 23, 2007 2:40 pm Post subject:

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:

Sliderule · Posted: Fri Feb 23, 2007 3:19 pm Post subject:

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:

open your MySQL database within OpenOffice
on the left, click on the Queries icon under Database
on the right, click on Create Query in SQL View...
click on the icon: SQL with a check mark
enter into box below your SQL statement
Press the execute icon
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:

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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

Sliderule · Posted: Fri Feb 23, 2007 8:19 pm Post subject:

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:

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

ahhhhh HA!

MySQL is not running!

OO Base uses http://hsqldb.org/

Sliderule · Posted: Sat Feb 24, 2007 9:44 am Post subject:

bmcswain:

According to MySQL documentation:

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

>>>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

Sliderule · Posted: Sat Feb 24, 2007 11:51 am Post subject:

Bob:

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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

bmcswain · General User Joined: 23 Feb 2007 Posts: 11

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