| View previous topic :: View next topic |
| Author |
Message |
Lazy-legs
Super User
Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark
|
 Posted: Thu Jun 07, 2007 11:42 am Post subject: Connecting to a database and running SQL queries |
 |
|
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 |
|
 |
squenson
Super User
Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia
|
| Posted: Thu Jun 07, 2007 11:58 am Post subject: |
|
|
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 |
|
|
Lazy-legs
Super User
Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark
|
| Posted: Thu Jun 07, 2007 12:05 pm Post subject: |
|
|
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 |
|
|
Lazy-legs
Super User
Joined: 21 Jul 2004
Posts: 564
Location: Århus, Denmark
|
| Posted: Thu Jun 07, 2007 12:21 pm Post subject: |
|
|
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 |
|
|
squenson
Super User
Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia
|
| Posted: Thu Jun 07, 2007 12:34 pm Post subject: |
|
|
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 |
|
|
|
