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

Database acces using a macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
danieldussart
Newbie
Newbie


Joined: 30 Jan 2004
Posts: 3

PostPosted: Mon Feb 02, 2004 5:36 am    Post subject: Database acces using a macro Reply with quote

Hi,

I'm trying to write a macro which must delete some datas from a table, and write them into another table (archives).

I have already written such a macro using VBA (MS Access) and it works fine. As I want to migrate from MS Access to OOo + MySQL, I must re-write the macro, but VBA and StarOffice Basic seem to be very different !

I search for some examples of macros using databases ; this could be really helpful.

Particularly, I'd like to find some information about how to modify ResultSetConcurrency and ResultSetType values of a Resultset. This seems to be necessary in order to modify or delete datas in a datatable.

I've already read (some parts of) the StarOffice 7 Software Basic Programmer's Guide, but it isn't really simple. That's why any good example is welcome !

Thanks.

Daniel
Back to top
View user's profile Send private message Visit poster's website
wasgedkongred
General User
General User


Joined: 22 Aug 2005
Posts: 12

PostPosted: Sat Aug 27, 2005 2:50 am    Post subject: Reply with quote

got the same problem!
please help!
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Sun Aug 28, 2005 7:05 pm    Post subject: Reply with quote

Are you able to create a datasource that references the data, or do you need to create the datasource manaully as well? It should be easier if you can create a data source and then use it. THis would assume that you can create the references initially and then use them.

Can you use the 2.0 beta? The database support is better there. I have never done anything database related using 1.1.4.

Finally, have you searched the archives here for some code accessing MySQL?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
alenis
Newbie
Newbie


Joined: 19 Feb 2006
Posts: 1

PostPosted: Sun Feb 19, 2006 8:57 am    Post subject: Reply with quote

I think you should try something like this:


DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("your db name")
If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
Statement = Connection.createStatement()
Statement.SetPropertyValue("ResultSetType", 1005) 'SCROLL_SENSITIVE (1003 FORWARD, 1004 SCROLL_INSENSITIVE)
Statement.SetPropertyValue("ResultSetConcurrency", 1008) 'UPDATABLE (1007 not updatable)

I hope the code is readable...
Back to top
View user's profile Send private message
Rekel
General User
General User


Joined: 08 May 2006
Posts: 8

PostPosted: Mon May 08, 2006 2:02 pm    Post subject: Reply with quote

The following works and is a bit more simple:

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("your db name")
If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
Statement = Connection.createStatement()
Statement.SetPropertyValue.ResultSetType=1005
Statement.SetPropertyValue.ResultSetConcurrency=1008

But can someone explain me why, although I changed them and now can effectively scroll freely my resultsets (as in ADO, from which i'm trying to migrate), the properties in the debugger for my ResultSet are still 1004 and 1007 ??????????????????
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Mon May 08, 2006 10:51 pm    Post subject: Reply with quote

Rekel wrote:
can someone explain me why the properties in the debugger for my ResultSet are still 1004 and 1007 ??????????????????

Probably bug http://www.openoffice.org/issues/show_bug.cgi?id=61869
Back to top
View user's profile Send private message Visit poster's website
NestorL
Newbie
Newbie


Joined: 01 Apr 2011
Posts: 1

PostPosted: Fri Apr 01, 2011 9:54 am    Post subject: Reply with quote

I hope Messrs Do not me wrong, I have searched around the forum and I could not find. Attempt to implement a macro to run the “Search and replace” function in a database table. With ResultSet I found the searched string easily and I can change it, but I can write on the Table (open only for reading). I await your assistance in completing and then send the complete solution to the forum. The Macro is based in this Forum itself. Thanks

Sub TableChg
Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object
Dim sSql as string
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("BDAEmerg")

If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If

Statement = Connection.createStatement()
Statement.SetPropertyValue("ResultSetType",1005)
Statement.SetPropertyValue("ResultSetConcurrency",1008)
ResultSet = Statement.executeQuery("SELECT ""ID"",""Período"" FROM ""TPeríodos""")
If Not IsNull(ResultSet) Then 'Search while the table is non null
While ResultSet.next
N = N + 1 'Numbering registers
Encon = ResultSet.GetString(2) ' String found
Longe = len(Encon)
Cambi = "2 de Marzo " 'Phrase to change
Posi = InStr(Encon,"Marzo 2 ") ' Search if searched phrase is in the string
If InStr(ResultSet.getString(2),"Marzo 2 ") > 1 then
Print ResultSet.getString(2);" ID:";ResultSet.GetString(1)
final = left(Encon,posi - 1) & Cambi & right(Encon, longe - 7 - posi)' Make the string change
print final 'Print the new string
sSQL = "UPDATE (dbname) SET (Colname) = (final) Where (IDCol) = (N);"
ResultSet = Statement.ExecuteUpDate(sSQL)' HERE IS THE PROBLEM I CAN'T WRITE TO TABLE
'the sSQl is a not defined yet SQL string instruction for to UPDATE
End If
'Print ResultSet.getString(1);" "; N
Wend
End If
End sub


Last edited by NestorL on Tue Apr 05, 2011 8:04 am; edited 5 times in total
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Fri Apr 01, 2011 10:22 am    Post subject: Reply with quote

Hello

I think it was better you did have start a new thread.
Also study some tutorials can help for designing better macro. I do think of the tutorials of Benitez. The tutorial of Arineckaig can help you to understand more of the form filters. The basic tutorial can help you to understand better the API. All these tutorials do you need for writing macros. You can find here links for the tutorials. This can maybe help you also for what you want.

Romke
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