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

[Solved] Copying Queries

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Thu Jul 31, 2008 5:24 pm    Post subject: [Solved] Copying Queries Reply with quote

If I set up a new db and create a table identical to a table in an existing database, is it possible to copy the queries for the existing table to the new db? If so, how?

Last edited by Bug on Fri Aug 01, 2008 3:19 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Thu Jul 31, 2008 8:22 pm    Post subject: Reply with quote

Bug:

You asked:
Bug wrote:
is it possible to copy the queries for the existing table to the new db? If so, how?

Yes, this is possible.
  1. Open up BOTH your databases in Open Office. This will provide two "windows' . . .
    • one having "DataBase_01"
    • the second "DataBase_02"

  2. Open the Query window in each of the two databases.

  3. From "DataBase_01", LEFT CLICK and HOLD the Query you want to COPY in "DataBase_02" . . . and DRAG it to the "window" of the "DataBase_02" . . . OVER the Queries icon on the LEFT.

  4. When you lift your mouse . . . it will 'COPY' the Query in the second database, and prompt you for the Name of the Query to save.


I hope that 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
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 4:55 am    Post subject: Reply with quote

Thanks for your detailed answer. I asked this question to feel out in theory whether the copying of queries could be done so that I could start the task of rebuilding the db with realistic expectations of what I could and couldn't copy over. I'll let you know how well the copying of queries went after I've rebuilt the db and copied the queries: Could be as long as a week before I'm done.
Back to top
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 9:14 am    Post subject: Reply with quote

This appears to be working for the most part. On some queries that work in the source, the copied queries give this error:

The data content could not be loaded.

Invalid argument in JDBC call: parameter index out of range:


Any ideas?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Fri Aug 01, 2008 11:36 am    Post subject: Reply with quote

Bug:

Please tell us the database engine you are using. For example, ¿ ¿ ¿ MS Access, Oracle, MySQL, dBase, SQLite, other ? ? ?

The embedded OpenOffice database engine . . . HSQL . . . is NOT a JDBC ( Java Database Connectivity ) database, by default.

Additionally, what would be helpful is to see the actual SQL that is causing you an error. Just so you understand, OpenOffice Base, is passing / sending the SQL ( Structured Query Language ) you create from a Query, DIRECTLY to your database engine for porcessing. Since you are 'connected' to your database ( per your comment ) with the JDBC . . . it is up to your database engine to run the Query and pass the results back to OpenOffice.

Please follow these steps . . . to include the actual SQL:
  1. Open your database in OpenOffice Base
  2. Click on Queries on the left
  3. Right click on the individual Query that is in 'question' ( the problem you are noting ) and select Edit in SQL View... from the popup menu
  4. The actual SQL that OpenOffice created is displayed in the new window . . . Copy it ( the SQL ) to the computer Clipboard
  5. Paste the SQL here, and, let me see it.
Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 1:21 pm    Post subject: Reply with quote

The queries are being copied from a Base connection to a .mbd file (MS Access) to a native Base db (HSQL db engine). I'm trying to establish a native Base db and dispense with connected databases and the .mdb format.

Let me explain in more detail. There is a Base db file connected to a .mdb file. I took the original .mdb, converted to .csv, opened the .csv in Calc, fixed the column names, then finally pasted this Calc spreadsheet into a new, clean Base db table, making sure the columns in this table were identical to those in the "connected" db.

Then, I opened the old Base connection (to .mdb) and copied the queries to the new native base db, using the method you described. It is working for the most part. A few queries gave the error I just described. Some, but not most. The errors do not appear in the original connection to the .mdb file. The queries run there. The errors appear in the copied queries, some of them, not all.

In any case here is the SQL info you asked about. This is coming from the copied query in the new native db, where the query gives the error I reported above. (I had to run a Find / Replace on some text here to avoid divulging identifiable info. Those are: Table1, Designation, and Activity. The syntax below, however, should not be affected.)

----

SELECT `Table1`.`ID`, `Table1`.`First Name`, `Table1`.`Last Name`, `Table1`.`Address`, `Table1`.`City`, `Table1_1`.`State`, `Table1`.`Zip`, `Table1`.`Phone Number`, `Table1`.`Wk Number`, `Table1`.`Cell`, `Table1`.`E-mail`, `Table1`.`Designation`, `Table1`.`Activity` FROM `Table1`, `Table1` AS `Table1_1` WHERE `Table1`.`Activity` = 'x'
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Fri Aug 01, 2008 2:13 pm    Post subject: Reply with quote

Bug:

I took the SQL you provided . . . EXACTLY as you wrote it . . . and made it more readable . . .
Code:
SELECT
   `Table1`.`ID`,
   `Table1`.`First Name`,
   `Table1`.`Last Name`,
   `Table1`.`Address`,
   `Table1`.`City`,
   `Table1_1`.`State`,
   `Table1`.`Zip`,
   `Table1`.`Phone Number`,
   `Table1`.`Wk Number`,
   `Table1`.`Cell`,
   `Table1`.`E-mail`,
   `Table1`.`Designation`,
   `Table1`.`Activity`

FROM `Table1`, `Table1` AS `Table1_1`

WHERE `Table1`.`Activity` = 'x'


Now . . . the line for the FROM clause . . . FROM `Table1`, `Table1` AS `Table1_1` should say: FROM `Table1` AS `Table1`

you have defined 'Table1' TWICE . . . and it should NOT be. Additionally, the line that says: `Table1_1`.`State`, should probably should say: `Table1`.`State`

FURTHERMORE . . . a difference between an ACCESS table and an Embedded Base database . . . to denote table and field names, the use of single back quotes ` in Access compared to HSQL requirement of double quotes to" makes a difference.

NOTE: You did NOT say you were copying a Query defined with DIFFERENT database engines. SO . . . I would recommend this Query . . . open up your database and query as I indicated above . . . and . . . change the SQL to:
Code:
SELECT
   "Table1"."ID",
   "Table1"."First Name",
   "Table1"."Last Name",
   "Table1"."Address",
   "Table1"."City",
   "Table1"."State",
   "Table1"."Zip",
   "Table1"."Phone Number",
   "Table1"."Wk Number",
   "Table1"."Cell",
   "Table1"."E-mail",
   "Table1"."Designation",
   "Table1"."Activity"

FROM "Table1" AS "Table1"

WHERE "Table1"."Activity" = 'x'


Run the Query . . . if it runs successfully, save it. OF COURSE, since you have said:
Bug wrote:
I had to run a Find / Replace on some text here to avoid divulging identifiable info. Those are: Table1, Designation, and Activity.

you will have to 'compensate' for the "identifiable" info here too.

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

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 3:17 pm    Post subject: Reply with quote

Your help has been most instructive, and of course practical. Yes, this solved the problem. Furthermore, I understand your explanation and believe I can apply the solution as needed. (EDIT: Yes, it is working across the board. Thanks.)

When you say that I have defined Table1 twice, do you mean that my problematic query-example has this error, or that the whole table does? If the latter, do I need to fix this for the whole table?

Also, I have a theoretical question. One difference I note between this query and some others that work is that the ones that work do not include the table name under the SELECT section, but only under the FROM section. The single back quotes seem to work in these queries. Is this going to be an issue down the line? Should I standardize everything acc. to the syntax you provide, or can I leave the queries that are working as they are without concern?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Fri Aug 01, 2008 3:57 pm    Post subject: Reply with quote

Bug:

You asked:
Bug wrote:
When you say that I have defined Table1 twice, do you mean that my problematic query-example has this error, or that the whole table does? If the latter, do I need to fix this for the whole table?

A very few things about SQL ( Structured Query Language ) . . . it is 'reasonably' standard among different database engines . . . that is the 'beauty' of it. SQL contains some KEY words . . . instructions . . . that tells the database what YOU want, how to get it, and, where to get it from.

In simple words . . .
  1. SELECT is the list of data to return . . . the fields . . . and / or calculations to perform ( for example, to 'concatenate' "First Name" || ' ' || "Last Name" - ( First Name, a space, Last Name ) as ONE FIELD - AS "Full Name" ) each name in the list separated by a comma ,
  2. FROM is a list of TABLES that are needed, each name in the list separated by a comma ,
  3. WHERE is / are the 'condition(s)' that must be met - only the data that meet the given conditions will be returned WHERE "Activity" = 'x' ( conditions CAN be 'combined' with AND / OR )
  4. ORDER BY if you want the display to be presented in a user defined order, ascending or descending . . . rather then by default, based on the Primary Key value

There are other parts to a query, more 'sophisticated' queries . . . BUT . . . that is a start for you. You can create MOST queries, with the GUI ( Graphical User Interface ), you can add tables to the Query, define an "Alias" for each field, Sort, Criteria etc. Bottom line, I do NOT know why what you originally presented above contained your table twice, BUT, since it is now 'fixed' . . . no reason to fix it again. Very Happy

You asked:
Bug wrote:
Also, I have a theoretical question. One difference I note between this query and some others that work is that the ones that work do not include the table name under the SELECT section, but only under the FROM section.

That is because, if the Query only uses one TABLE, it knows which TABLE each FIELD belongs to. BUT, if a Query uses more then one TABLE, it needs to know which TABLE the particular FIELD belongs to, since, multiple TABLES CAN use identical FIELD names. ( Try to say that fast five times ) Laughing

You asked:
Bugs wrote:
Should I standardize everything acc. to the syntax you provide, or can I leave the queries that are working as they are without concern?

If it were me, and, the Queries are working now as you need them . . . I would just leave it alone. Translation, if it works, do not fix it.

I hope this helps. Welcome to the universe of OpenOffice database. Perhaps, now, you can help others in this forum as they ask questions, now that you are an expert.

Sliderule
Back to top
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 6:11 pm    Post subject: Reply with quote

Before knowing what you have shown me, I've read many times and believed that queries are impossible to move from MS Access to Base, and vice versa. If I understand what I'm seeing here, the SQL syntax of the queries could theoretically be altered to reproduce the same results, provided a new query were created and the SQL edited from the old one to apply to the respective database engine. Am I correct in this inference? For some simple queries like my example above, this would seem to be a realistic task.

Thanks again. This has been very helpful. As for my being an "expert", I think I am giving it a decade or so, or at least a couple of years.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


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

PostPosted: Fri Aug 01, 2008 6:47 pm    Post subject: Reply with quote

Bug:

Good that you are learning . . . that is real positive.

Just to let you know . . . but . . . I hate to tell you now after 'reading' the task you went through to 'create' your tables in Base HSQL, from Access . . .

Just as you dragged Queries, from your two registered databases,
  1. OpenOffice connection to Access
  2. Embedded database HSQL

you could have also, dragged the TABLES . . . including field names, data, etc.

I challenge you, just for the fun of it, to create a new ( another ) BLANK ( empty ) HSQL database, and, drag a TABLE or two into HSQL, and, see what you get. I am assuming, your 'original' Access database DOES contain a Primary Key . . . for . . . simplicity. Remember, every database table, SHOULD, by definition ( relational database requirement to be a true relational database ) have a Primary Key.

Short definition ( my words, not taken from any publication ) of a Primary Key is:

the field, or fields ( may be more than one ) that will make each row in your table UNIQUE. May be an ID ( autoincrememt by the database ), a combination of DATE/TIME, or other identifier. In the United States, Social Security Number ( not really a number, but rather a series of numbers ) should ( unless identity theft ) make this unique to an individual.

Hope that is clear, or at least, as clear as mud. Laughing

Sliderule
Back to top
View user's profile Send private message
Bug
OOo Advocate
OOo Advocate


Joined: 29 Oct 2005
Posts: 336

PostPosted: Fri Aug 01, 2008 7:04 pm    Post subject: Reply with quote

Quote:
you could have also, dragged the TABLES . . . including field names, data, etc.

I challenge you, just for the fun of it, to create a new ( another ) BLANK ( empty ) HSQL database, and, drag a TABLE or two into HSQL, and, see what you get.

This will disappoint you. The initial reason for the process of my exporting .mdb to .csv, then copying the resulting spreadsheet from Calc to Base, was that the original .mdb had no primary key set, yet queries had been made by a user in the Base connection to that .mdb. The result was a Base db connection to .mdb that had working queries but whose Table was locked to changes. The attempts to add a primary key in Access or Base after the fact were fruitless, wrt allowing Base to edit the Table.

This thread was the result of my wanting to know whether those queries in the "locked" connection would come over to the new, native Base db that I intended to make, as described above. This obviously worked well, thanks to your instructions.

So I can't accept your challenge, if, that is, I'm correct in the first place to assume the original Base connected to .mdb was next to useless and worthy of the trash heap.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
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