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]ooBase SQL statement to retrieve data from an ext DB

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


Joined: 04 Mar 2012
Posts: 4
Location: Montreal, QC

PostPosted: Sun Mar 04, 2012 7:17 pm    Post subject: [Solved]ooBase SQL statement to retrieve data from an ext DB Reply with quote

Hi,

This is my first thread on this forum. I hope I'm at the right place to ask my question. Here's what I'm trying to do:

I want to copy a table using the "SELECT IN" statement. The problem is that I want to copy a table that is in an external database. When I say external, I mean another Base database that is already registered.

That said, I would simply like to know how to connect to another database table to retrieve data to use it with a simple SQL statement.

Can anyone help me? Thanks in advance.


Last edited by ncharpen on Tue Mar 06, 2012 3:16 am; edited 1 time in total
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 04, 2012 9:26 pm    Post subject: Reply with quote

Welcome ncharpen,

Open your Base (.odb) file and use Tools > SQL...
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
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: Sun Mar 04, 2012 10:01 pm    Post subject: Reply with quote

ncharpen:

ncharpen wrote:
I want to copy a table using the "SELECT IN" statement. The problem is that I want to copy a table that is in an external database. When I say external, I mean another Base database that is already registered.

Since, you want to INSERT records ( add ), from one OpenOffice Base file, to a second OpenOffice Base file, you canNOT use a "SELECT IN" statement, since you can only connect to one database source.

But, if it were me, this is what I would do:
  1. Open BOTH of your OpenOffice Base files simultaneously.

  2. Click on the Tables icon ( on the left ) in both

  3. Drag the table ( or View or Query ) you want to copy, to the Table you want to receive the data, that is, in the other database Smile

  4. A Copy Table Window will appear, choose the radio button Append data ( since I assume you just want to ADD data, not create a new table )

  5. Click the Next> button

  6. Be sure the field names with data in the Source table matches the field names you want in the Destination table ( or you can use the arrows if needed )

  7. Click on the Create button

  8. Smile and say: "Gee Sliderule, that was easy. Now all I have to do is mark the forum post as solved, per the instructions at the bottom. Smile
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.
Back to top
View user's profile Send private message
ncharpen
Newbie
Newbie


Joined: 04 Mar 2012
Posts: 4
Location: Montreal, QC

PostPosted: Mon Mar 05, 2012 3:51 am    Post subject: Reply with quote

Thanks to all for your replies.

Yet, that's not really what I want.

I want an SQL statement that I can later put in a macro so I can simply press a button in a form to have this all accomplished. In fact, forget about the SELECT IN statement. I just want to be able to connect to an external database to then be able to do ANYTHING with it.

I already knew about the Tools > SQL... and copy pasting is not user-friendly and it's time-consuming since you have to open databases and do all the steps. It's not complicated, but not friendly.

Are you telling me there is no way to be in one database, then connect to another data source using an SQL statement, then retrieve the data from that second data source to use it in the firs database?

Here are code lines I found that are closest to what I want, but I don't want to build a macro yet. I first want to do this with a simple SQL statement if that is possible at all:

Code:
Sub RequeteBase_V01
Dim oDBContext As Object , oDB As Object , oBase As Object
Dim oStatement As Object , oRequete As Object
Dim strSQL As String

oDBContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
 
'Connection to the registered database "Bibliography"
   
oDB = oDBContext.getbyName("Bibliography")

'If the database is protected, use
'oBase = oDB.getConnection("Login", "Passwrd")
oBase = oDB.getConnection("","")
oStatement = oBase.createStatement()

strSQL = "SELECT ""Identifier"",""Publisher"",""ISBN"" FROM ""biblio"" " & _
    "WHERE ""Author""='Böhm, Franz'"
oRequete = oStatement.executeQuery( strSQL )

If Not IsNull(oRequete) Then
  While oRequete.next
    MsgBox oRequete.getString(1) & " / " & _
      oRequete.getString(2) & " / " & oRequete.getString(3)
  Wend
End If

oRequete.Close
oStatement.Close
oBase.Close
oBase.Dispose
End Sub


Also, I found in another forum an SQL statement like that, but don't know how to apply it to my problem:

Code:
SELECT database.owner.table for local data
SELECT server.database.owner.table for remote data


Can anyone help me with that? Again, thanks to all...
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: Mon Mar 05, 2012 6:32 am    Post subject: Reply with quote

ncharpen:

Permission to ask you a few questions, so, maybe can come up with the best solution for you.

Please give more information about the database engines you are using. That is, one thing if you are running OpenOffice ( or LibreOffice ) with an embedded database engine ( all of data is in the OpenOffice *.odb file in a compressed - ZIP format ) . . . or . . . HSQL as a server or single user mode. And, the other database . . . where is it.

Where I am going with all of this, perhaps other external tools, such as the SqlTool.jar from HSQL can allow you to export data to a flat file ( from any jdbc or ODBC database engine ) and import it to the other engine. Or, another tool, such as SQL-WorkBench using the wbcopy command might help.

Sliderule
Back to top
View user's profile Send private message
ncharpen
Newbie
Newbie


Joined: 04 Mar 2012
Posts: 4
Location: Montreal, QC

PostPosted: Mon Mar 05, 2012 1:23 pm    Post subject: Reply with quote

Hi Sliderule,

I thank you very much for your quick replies. I actually appreciate very much your questions and hope I will be able to answer correctly.

I'm using version 3.3.0 of OpenOffice. At the bottom of my ooBase main screen is written "Embedded database" and "HSQL engine" (I guess since I'm translating this from the french version on my computer...).

Quote:
HSQL as a server or single user mode


That, I don't know, but I'm having the database on my laptop computer if that can help.

The external database is actually the "read-only" connection of my Outlook Address Book that I registered in OO to be able to use my Outlook contacts with mail merge in ooWriter.

I want to be able to copy the whole table of my Outlook contacts in my database so I don't have to retype my contact infos in my database. And I want to be able to do this specifically. Example, copy my contacts in my database, have a form to look them up and choose one contact to insert it into another table of my choice in my database. All the later, I'm able to do it except the connection to the open office copy of my outlook database.

Again, I thank you very much for your time and input on this.
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: Mon Mar 05, 2012 1:45 pm    Post subject: Reply with quote

OK. Since you are using the "Embedded database" . . . that means, you canNOT have any other database front-end get at the database back-end ( your OpenOffice HSQL data ).

Now, yes, I suppose, you could write a macro, that would go against the Outlook database, and, put the data in a result set, but, then, in order to INSERT ( add ) new records to the HSQL Embedded database, your macro would have to prepare an INSERT statement . . . and that would mean, each field would have to be separated by a comma, and, dates would have to be in 'YYYY-MM-DD' format surrounded by single quotes ( '2012-03-05' ), and, text data also surrounded by single quotes ( 'ncharpen', 'Sliderule' ).

Can it be done, yes, but, only you can determine if it is worth the effort Smile compared to the first technique I mentioned at the top, about dragging data ( from a table, view, or Query ) from your Outlook database to your HSQL database, and, making sure the fields match.

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


Joined: 04 Mar 2012
Posts: 4
Location: Montreal, QC

PostPosted: Tue Mar 06, 2012 3:15 am    Post subject: Reply with quote

Hi Sliderule,

I guess then I'm gonna go with the simple solution. I'm not an expert and I have to accept my limitations... Sad

Thanks a lot for your help.
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