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

Connecting to a database and running SQL queries

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Thu Jun 07, 2007 11:42 am    Post subject: Connecting to a database and running SQL queries Reply with quote

Hello,

I'm tinkering with a macro that would allow me to connect to a database and insert records using the INSERT INTO query. Since I've never done this before, my initial macro is rather simple:

Code:
Sub ConnectToDatabase()

Dim DBConnect As Object
Dim DataSource As Object
Dim SQLQuery As String

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("MiniDB")
ConnectToDB=DataSource.GetConnection ("","")

SQLQuery="INSERT INTO tiny_table (FieldA, FieldB) VALUES (Monkey, Banana)"

SQLStatement=ConnectToDB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)

ConnectToDB.close
ConnectToDB.dispose()

End Sub


As you can see, the macro connects to a database called MiniDB containing the tiny_table with two fields: FieldA and FieldB. The macro establishes connection without any problem. However, when it comes to executing the SQL part, I get the following error message:



What am I doing wrong?

Thank you!

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Thu Jun 07, 2007 11:58 am    Post subject: Reply with quote

Lazy-legs,

I cannot test it at the moment, but should not the table names, field names and values be surrounded by double-quotes "? You may like to try (a double double-quotes in a string will be interpreted as a single double-quote):
Code:
SQLQuery="INSERT INTO ""tiny_table"" (""FieldA"", ""FieldB"") VALUES (""Monkey"", ""Banana"")"

_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Back to top
View user's profile Send private message
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Thu Jun 07, 2007 12:05 pm    Post subject: Reply with quote

Thank you for you reply, squenson. Unfortunately, the SQL statement you've suggested doesn't work either: I get an error message saying that the column MONKEY not found.

Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
Lazy-legs
Super User
Super User


Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark

PostPosted: Thu Jun 07, 2007 12:21 pm    Post subject: Reply with quote

OK, I've figured it out. The SQL query should be as follows:

Code:
SQLQuery="INSERT INTO ""tiny_table"" " + "(""FieldA"", ""FieldB"") VALUES " + "('Monkey', 'Banana')"


Kind regards,
Dmitri
_________________
http://code.google.com/p/writertools/
Back to top
View user's profile Send private message Visit poster's website
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Thu Jun 07, 2007 12:34 pm    Post subject: Reply with quote

This code is working (just run on a database called MiniDB with a table tiny_table which has two fields FieldA and FieldB):
Code:
Sub test
Dim DBConnect As Object
Dim DataSource As Object
Dim SQLQuery As String
Dim DBContext as Object
Dim ConnectToDB as Object
Dim SQLStatement as Object
Dim Result as Object

DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("MiniDB")
ConnectToDB=DataSource.GetConnection ("","")

SQLQuery="INSERT INTO ""tiny_table"" (""FieldA"", ""FieldB"") VALUES ('Monkey2', 'Banana')"

SQLStatement=ConnectToDB.createStatement
Result=SQLStatement.executeQuery (SQLQuery)

ConnectToDB.close
ConnectToDB.dispose()
End Sub

_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
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 Macros and API 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