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

How to create a table with 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
Krankenstein
General User
General User


Joined: 06 Aug 2003
Posts: 12
Location: Zutphen, Netherlands

PostPosted: Sun Aug 10, 2003 2:38 pm    Post subject: How to create a table with a macro? Reply with quote

I can't figure out how to create a new table using a StarBasic macro. The following code worked for sending a SELECT query to the dbase, but it won't let me send a CREATE TABLE query.

Code:
Sub Main

   Dim oDatabaseContext, oDataSource As Object
   Dim oConnection, oStatement, oResultSet As Object
   Dim sQuery As String
   Dim i As Integer
   
   oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   oDataSource = oDatabaseContext.getByName("Testdb")
   oConnection = oDataSource.GetConnection("","")
   oStatement = oConnection.CreateStatement()
   
   sQuery = "CREATE TABLE test_table (col1 char(50), col2 char(50))"
   
   oResultSet = oStatement.executeQuery(sQuery)
   
End Sub


Fill in any SELECT query for sQuery ("SELECT * FROM Table1" for example).

How do I then create tables with my macro?
_________________
For I Am Evil Twisted Evil


Last edited by Krankenstein on Mon Aug 11, 2003 7:29 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Sun Aug 10, 2003 8:14 pm    Post subject: Reply with quote

what you can do will be dependent on the capabilities of the connection (odbc, jdbc etc) and database type. what are you using? can you do the sql create table from the data source interface of OOo client?
Back to top
View user's profile Send private message
Krankenstein
General User
General User


Joined: 06 Aug 2003
Posts: 12
Location: Zutphen, Netherlands

PostPosted: Mon Aug 11, 2003 1:21 am    Post subject: Reply with quote

If I create a SQL query manually, it doesn't work either. It says "No data can be loaded" "No Select statement". It's the standard database type (dBase). I can try changing that. But what would otherwise be the procedure for creating a table? I can't find it in the basic macro programming guide. What other sources can I use?

edit: Using other databases has no effect. Exactly the same error Sad

edit2: the error message I mentioned is what I get when I try to execute a query manually. If I do it with my macro it keeps saying:

Code:
BASIC runtime error.
An exception occured
Type: com.sun.star.sdbc.SQLException
Message: Driver does not support this function!.


Does this mean the CREATE query is not supported for the SOffice dbase? If so, what's the workaround? I use OOo 1.03 btw.
_________________
For I Am Evil Twisted Evil
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Krankenstein
General User
General User


Joined: 06 Aug 2003
Posts: 12
Location: Zutphen, Netherlands

PostPosted: Mon Aug 11, 2003 5:35 am    Post subject: Reply with quote

Now it won't even execute an INSERT query. The query is roughtly as follows (using other values):

INSERT INTO table1 (col1, col2, col3, col4, col5, col6) VALUES ("Something", "", "123456", "Somename", "Some short scentence.", "")

The error:

Type: com.sun.star.sdbc.SQLException
Message: Function sequence error.


Never mind Embarassed used " instead of '. Now it works Smile (well the INSERT query that is, the other one still won't do anything Sad

edit: I tried different query syntaxes, and I get different error messages:

CREATE TABLE Table1 (row1 VARCHAR, row2 VARCHAR)

gives me the message: syntax error, unexpected NAME

and this: CREATE TABLE 'whatever' (row1 VARCHAR, row2 VARCHAR)

gives me the message: syntax error, unexpected STRING, expecting NAME

I don't get it Mad why is it torturing me? Wink
_________________
For I Am Evil Twisted Evil
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Noelson
Guest





PostPosted: Mon Aug 11, 2003 5:14 pm    Post subject: Create a dBase Table Reply with quote

Hi,

This code works for me. More info in Dev Guide section 12.4.3.

Code:

Sub criaTabelasMinhaFonte
'
' OBS:
' 1) Obrigatoriamente, fechar a conexão com dispose()
'
   Dim oContexto As Variant
   Dim oFonte As Variant

   oContexto = createUnoService("com.sun.star.sdb.DatabaseContext")
   ' obtem a fonte de dados
   If oContexto.hasByName("Minha_Fonte") Then
      oFonte = oContexto.getByName("Minha_Fonte")
   Else
      MsgBox "Não existe, saindo ..."
      Exit Sub
   End If
   ' faz a conexão com a fonte de dados
   Dim oConex As Object
   oConex = oFonte.getConnection("","")
   ' obtem tabelas da fonte de dados
   oTabelas = oConex.getTables()
   ' cria um descritor de dados para a tabela amigos.dbf
   oTab = oTabelas.createDataDescriptor()
   ' define propriedade nome
   oTab.Name = "amigos"
   ' obtem colunas da tabela
   oColunas = oTab.getColumns()
   ' cria um descritor de dados para uma coluna
   oCol = oColunas.createDataDescriptor()
   ' define as propriedades da 1a. coluna
   ' decimal=3, varchar=12, date=91
   ' com.sun.star.sdbc.DataType (DECIMAL, VARCHAR, DATE)
   oCol.Name = "CODIGO"
   oCol.Type = com.sun.star.sdbc.DataType.DECIMAL
   oCol.Precision = 5
   ' adiciona a coluna ao container de colunas
   oColunas.appendByDescriptor(oCol)
   ' define propriedades da 2a. coluna (o descritor preserva os valores)
   oCol.Name = "NOME"
   oCol.Type = com.sun.star.sdbc.DataType.VARCHAR
   oCol.Precision = 50
   oColunas.appendByDescriptor(oCol)
   ' define propriedades da 3a. coluna
   oCol.Name = "ANIV"
   oCol.Type = com.sun.star.sdbc.DataType.DATE
   'oCol.Precision = 0
   oColunas.appendByDescriptor(oCol)
   '
   ' adiciona a tabela ao container de tabelas
   oTabelas.appendByDescriptor(oTab)
   '
   ' cria um descritor de dados para a tabela fones.dbf
   oTab = oTabelas.createDataDescriptor()
   ' define propriedade nome
   oTab.Name = "fones"
   ' obtem colunas da tabela
   oColunas = oTab.getColumns()
   ' cria um descritor de dados para uma coluna
   oCol = oColunas.createDataDescriptor()
   oCol.Name = "CODIGO"
   oCol.Type = com.sun.star.sdbc.DataType.DECIMAL
   oCol.Precision = 5
   oCol.Description = "Código do amigo"
   ' adiciona a coluna ao container de colunas
   oColunas.appendByDescriptor(oCol)
   ' define propriedades da 2a. coluna (o descritor preserva os valores)
   oCol.Name = "FONE"
   oCol.Type = com.sun.star.sdbc.DataType.VARCHAR
   oCol.Precision = 15
   oCol.Description = "Fone do amigo"
   oColunas.appendByDescriptor(oCol)
   '
   ' adiciona a tabela ao container de tabelas
   oTabelas.appendByDescriptor(oTab)
   '
   ' fecha a conexão
   oConex.dispose()
'
End Sub


Bye,

Noelson
Back to top
Krankenstein
General User
General User


Joined: 06 Aug 2003
Posts: 12
Location: Zutphen, Netherlands

PostPosted: Tue Aug 12, 2003 12:35 am    Post subject: Reply with quote

Thanks it works Smile.
_________________
For I Am Evil Twisted Evil
Back to top
View user's profile Send private message Send e-mail Visit poster's website
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