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

MailMerge and DataSource (i can't find the table I need)

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


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Thu Apr 14, 2005 10:49 pm    Post subject: MailMerge and DataSource (i can't find the table I need) Reply with quote

I try to execute a mail merge for some days but i don't manage to...
I've read that jbotte got the same problem as me and I wanted to ask him how he manage to resolve it.But if anybody else can ...
My mailmerge concerns a .sxw document (not a model) and i want to merge it with a .txt flatfile with ";" like separators. It's perfect when the datasource is selected in OOo but i want to create one with the OLE components.

jbotte wrote:
Quote:

"The statement is invalid. It contains no valid table."

My code is as follows:
Code:

oDbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDbInstance = oDbContext.createInstance()
oDbContext.registerObject("testData", oDbInstance)
oDbInstance.URL = "sdbc:flat:file:///home/jbotte/testData.dat"

' Code from Danad
Dim oDbProperties(1) As New com.sun.star.beans.PropertyValue

oDbProperties(0).Name = "HeaderLine"
oDbProperties(0).Value = True
oDbProperties(1).Name = "FieldDelimiter"
oDbProperties(1).Value = ":"

oDbInstance.setPropertyValue("Info", oDbProperties())

' *** So far so good, I hope...
' *** Suspect here is where something is needed...

Dim oRowSet As Variant
oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.setPropertyValue("DataSourceName", "testData")
oRowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
sCmdSQL = "SELECT * FROM testData"
oRowSet.setPropertyValue("Command", sCmdSQL)
oRowSet.execute()



' Get above error about no valid tables


There is mention in the IDL docs that you need to set TableTypeFilter and TableFilter or "If empty, all tables/table types are rejected". However, the IDL docs don't tell you what the valid options are, and I don't understand how to speciry "TABLE" to the TableTypeFilter parameter if that's what I'm supposed to be doing (I get syntax errors with everything I've tried). Am I doing this wrong, or am I still missing something?

P.S. I"m just using a simple ":" delimited text file with a few rows as my test data.



my code (in delphi sorry):

Code:

Datacontext:=OpenOffice.createInstance('com.sun.star.sdb.DatabaseContext');
// Core reflection est utilisé pour pouvoir créer des property value
      CoreReflection := OpenOffice.createInstance
                     ('com.sun.star.reflection.CoreReflection');
      // un tableau dynamique de propertyvalues
      arguments:= VarArrayCreate([0,7], varVariant);
      CoreReflection.forName('com.sun.star.beans.PropertyValue').createObject(PropertyValue);
      PropertyValue.Name := 'Extension';
      PropertyValue.Value := 'txt';
      arguments[0]:= PropertyValue ;
      PropertyValue.Name := 'Charset';
      PropertyValue.Value := 0;
      arguments[1]:= PropertyValue ;
      PropertyValue.Name := 'FixedLength';
      PropertyValue.Value := true;
      arguments[2]:= PropertyValue ;
      PropertyValue.Name := 'HeaderLine';
      PropertyValue.Value := true;
      arguments[3]:= PropertyValue  ;
      PropertyValue.Name := 'FieldDelimiter';
      PropertyValue.Value := ';';
      arguments[4]:= PropertyValue ;
      PropertyValue.Name := 'StringDelimiter';
      PropertyValue.Value := '"';
      arguments[5]:= PropertyValue ;
      PropertyValue.Name := 'DecimalDelimiter';
      PropertyValue.Value := '.';
      arguments[6]:= PropertyValue ;
      PropertyValue.Name := 'ThousandDelimiter';
      PropertyValue.Value := ',';
      arguments[7]:= PropertyValue ;
      //création de la datasource avec parametres
      Datasource:=OpenOffice.createInstance('com.sun.star.sdb.DataSource');
      datasource.URL:= 'sdbc:flat:file:///C:/Dossiers/coquery';
      datasource.Info:= arguments;
      datasource.TableFilter:='essai';
      datacontext.registerObject('src1',datasource);

var   publipostage:variant;

begin
   publipostage:=OpenOffice.createInstance('com.sun.star.text.MailMerge');
   publipostage.DataSourceName:= 'src1' ;
   publipostage.DocumentURL:= document.getUrl;
   publipostage.OutputType:=1;
   publipostage.Command:= 'essai';
   publipostage.CommandType:=0;
   publipostage.execute(VarArrayCreate([0, -1],varVariant));
end;


note:publipostage is the term for mailmerge in french

It doesn't work. The 'essai' table is said unknown.
Iwanted to know if it comes from the TableFilter or TableTypeFilter or if it comes from an absence of connection or if it'because i don't mind of getting a 'rowset'.
Forgive my poor english (i'm french)
Wink
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
pitonyak
Administrator
Administrator


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

PostPosted: Mon Apr 18, 2005 5:18 am    Post subject: Reply with quote

Are you able to actually read the text file? The table name is the same as the file name with out the path and without the file extension. It looks to me like your code is creating a data source. I have only experimented using the 2.0 development build, and I am able to import flat files using macros. So, are you able to do this and then read the data?
_________________
--
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
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Mon Apr 18, 2005 10:38 pm    Post subject: Reply with quote

pitonyak wrote:
Are you able to actually read the text file? The table name is the same as the file name with out the path and without the file extension.

With "Memo-pad" Ican read the ".txt"... but my datasource seems not including the table...
The table name is the name of my file without the path and the extension so it should work...
Quote:
It looks to me like your code is creating a data source. I have only experimented using the 2.0 development build, and I am able to import flat files using macros. So, are you able to do this and then read the data?

I effectively create a datasource but the table "essai" is not visible... If I open OOo, i can see the table in the datasource dialog but i have to uncheck and recheck it to be able to use it...
I don' t understand... Bernard Marcelly help me and I correct some little errors but the table still doesn't exists...
( CharSet not Charset and the value is a string)

my new code:
Code:

//création du service databasecontext
Datacontext:=OpenOffice.createInstance('com.sun.star.sdb.DatabaseContext');
   //si openoffice connait la source src1
   if Datacontext.hasbyname('src1')
   then Memo_Enumeration.Lines.Add('src1 existe déjà')
   else
      begin
      // Core reflection est utilisé pour pouvoir créer des property value
      CoreReflection := OpenOffice.createInstance
                     ('com.sun.star.reflection.CoreReflection');
      //création d' un tableau  de propertyvalues équivalent à createproperties
      arguments:= VarArrayCreate([0,7], varVariant);
     
CoreReflection.forName('com.sun.star.beans.PropertyValue').createObject(PropertyValue);
      PropertyValue.Name := 'Extension';
      PropertyValue.Value := 'txt';
      arguments[0]:= PropertyValue ;
      PropertyValue.Name := 'CharSet';
      PropertyValue.Value := 'ISO-8859-15';  //ou 'ISO646-FR1'
      arguments[1]:= PropertyValue ;
      PropertyValue.Name := 'FixedLength';
      PropertyValue.Value := true;
      arguments[2]:= PropertyValue ;
      PropertyValue.Name := 'HeaderLine';
      PropertyValue.Value := true;
      arguments[3]:= PropertyValue  ;
      PropertyValue.Name := 'FieldDelimiter';
      PropertyValue.Value := ';';
      arguments[4]:= PropertyValue ;
      PropertyValue.Name := 'StringDelimiter';
      PropertyValue.Value := '"';
      arguments[5]:= PropertyValue ;
      PropertyValue.Name := 'DecimalDelimiter';
      PropertyValue.Value := '.';
      arguments[6]:= PropertyValue ;
      PropertyValue.Name := 'ThousandDelimiter';
      PropertyValue.Value := ',';
      arguments[7]:= PropertyValue ;
      //création de la datasource avec parametres
      instance:=Datacontext.createInstance;
      //enregistrement dans openoffice
      datacontext.registerObject('src1',instance);
      instance.URL:= 'sdbc:flat:file:///C:/Dossiers/coquery';
      instance.Info:= arguments;
      end;
datasource:=datacontext.getbyname('src1');
datasource.getconnection;

When i use Bernard Marcelly's code (a delphi_OOo library which includes CreateProperties, ConnectOpenOffice or CreateUnoService procedures like DannyB's library for VB)), it works... I don't understand why my code which should do the same things doesn't work... Question Question Crying or Very sad
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
pitonyak
Administrator
Administrator


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

PostPosted: Tue Apr 19, 2005 5:15 am    Post subject: Reply with quote

Are you able to inspect the meta-data from the connection? does it list a table? This is very strange.... Does this work from Basic? I typically do not create a saved data source, but rather connect to the flat file when I want it. Also, I always use CSV rather than TXT as my file extension.
_________________
--
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
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Tue Apr 19, 2005 5:39 am    Post subject: Reply with quote

pitonyak wrote:
Are you able to inspect the meta-data from the connection? does it list a table?

I don't understand what you mean by inspecting the meta-data from the connection...
pitonyak wrote:
This is very strange.... Does this work from Basic? I typically do not create a saved data source, but rather connect to the flat file when I want it.

I don't know that it was possible... I wan't to execute a mailmerge and i thought i need a datasource... Is it wrong??? (but i create my datasource as i need)
pitonyak wrote:
Also, I always use CSV rather than TXT as my file extension.

I haven't the choice using csv files because the application on which i'm working creates txtfiles...
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Tue Apr 19, 2005 6:20 pm    Post subject: Reply with quote

With VBScript it's working ( well, DS was created and txt_file is checked ), but I get error on last line and I don't know VBS to fix it.

I'm using Bridge_GetStruct instead of CoreReflection methods.

Code:

'
' ...
Dim flatProp(7)

Set flatProp(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(0).Name  = "Extension"
flatProp(0).Value = "txt"   ' string
'
' repeat til flatProp(7)
'
oSource.setPropertyValue "URL", "path_URL"
oSource.setPropertyValue "Info", flatProp()
' ERRORS:
' with () -> Subscript out of range
' without () -> lang.IllegalArgumentException

If there's some not formatted txt_file into path_URL, OO.o becomes confused with DS tables. At least, it's almost working (OO.o 1.1.3 + XP).

About your code, is this correct ?
Code:

      //création de la datasource avec parametres
      instance:=Datacontext.createInstance;

and you can try:
Code:

      instance.URL:= 'sdbc:flat:file:///C|/Dossiers/coquery';
      // instead of
      // instance.URL:= 'sdbc:flat:file:///C:/Dossiers/coquery';

HTH
Back to top
View user's profile Send private message
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Tue Apr 19, 2005 10:36 pm    Post subject: Reply with quote

Danad wrote:
With VBScript it's working ( well, DS was created and txt_file is checked ), but I get error on last line and I don't know VBS to fix it.

Your last line is :
Quote:
oSource.setPropertyValue "Info", flatProp()
??
I 've had the same messge but for an other line , I was trying to create a DataFilter which is an array of string... I don't manage to affect any value to this array...
In delphi this line does not cause any error, i can affect an array to this property
Quote:

I'm using Bridge_GetStruct instead of CoreReflection methods.

Code:

'
' ...
Dim flatProp(7)

Set flatProp(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(0).Name  = "Extension"
flatProp(0).Value = "txt"   ' string
'
' repeat til flatProp(7)
'
oSource.setPropertyValue "URL", "path_URL"
oSource.setPropertyValue "Info", flatProp()
' ERRORS:
' with () -> Subscript out of range
' without () -> lang.IllegalArgumentException

If there's some not formatted txt_file into path_URL, OO.o becomes confused with DS tables. At least, it's almost working (OO.o 1.1.3 + XP).

If I understand the line where you set the property Info does not work but you still create a datasource and the table inside is visible... it's very strange... Shocked
Quote:

About your code, is this correct ?
Code:

      //création de la datasource avec parametres
      instance:=Datacontext.createInstance;


It is correct (in delphi)... no error message... and a datasource is instanced... In other languages it's necessary to have () at the end but it should work...
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Wed Apr 20, 2005 4:35 am    Post subject: Reply with quote

Quote:

If I understand the line where you set the property Info does not work but you still create a datasource and the table inside is visible... it's very strange

Exactly.

Here's the VBS code:
Code:

'
Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager")
Set objDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")
'
Dim args()
Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)
'
Set oContexto = objServiceManager.createInstance("com.sun.star.sdb.DatabaseContext" )
Set oFonte = objServiceManager.createInstance("com.sun.star.sdb.DataSource" )
oContexto.registerObject "Fonte1", oFonte
'
Dim flatProp(7)
Set flatProp(0) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(0).Name  = "Extension"
flatProp(0).Value = "txt"   ' string
Set flatProp(1) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(1).Name  = "CharSet"
flatProp(1).Value = 0 ' long
Set flatProp(2) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(2).Name  = "FixedLength"
flatProp(2).Value = True   ' boolean
Set flatProp(3) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(3).Name  = "HeaderLine"
flatProp(3).Value = True   ' boolean
Set flatProp(4) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(4).Name  = "FieldDelimiter"
flatProp(4).Value = ";"   ' string
Set flatProp(5) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(5).Name  = "StringDelimiter"
flatProp(5).Value = "" ' string
Set flatProp(6) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(6).Name  = "DecimalDelimiter"
flatProp(6).Value = "."   ' string
Set flatProp(7) = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(7).Name  = "ThousandDelimiter"
flatProp(7).Value = ","   ' string
'
sFlatUrl = "sdbc:flat:file:///C|/Documents and Settings/My Documents/ooo/com_ole"
oFonte.setPropertyValue "URL", sFlatUrl
oFonte.setPropertyValue "Info", flatProp()

As you can see, I'm using createInstance from XMultiServiceFactory and not, as you, from XSingleServiceFactory ( DEPRECATED ).

HTH
Back to top
View user's profile Send private message
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Wed Apr 20, 2005 5:02 am    Post subject: Reply with quote

Your code instanciate a datasource so i'ts normal that you get one... but how can it see any table if the Info property is incorrect???

I thought that it was my Info property which was wrong but visibly it's not the reason i can't see my table...
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Wed Apr 20, 2005 5:23 am    Post subject: Reply with quote

Code:
flatProp(1).Name  = "CharSet"
flatProp(1).Value = 0 ' long

B. Marcelly said me that CharSet is a string... if you don't instanciate it OOo will use the system value...
Code:
sFlatUrl = "sdbc:flat:file:///C|/Documents and Settings/My Documents/ooo/com_ole"

the "|" (pipe?) is equivalent to ":" on all systems??? I didn't know that...
Quote:
As you can see, I'm using createInstance from XMultiServiceFactory and not, as you, from XSingleServiceFactory ( DEPRECATED ).

ah it's deprecated, it's because of that that you asked me if it works...
visibly like the deprecated in java it's still working...
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
pitonyak
Administrator
Administrator


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

PostPosted: Wed Apr 20, 2005 6:13 am    Post subject: Reply with quote

When you have a connection, you can obtain meta data concerning the connection.
http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XConnection.html#getMetaData
http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XDatabaseMetaData.html
_________________
--
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
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Wed Apr 20, 2005 7:59 pm    Post subject: Reply with quote

Quote:

When i use Bernard Marcelly's code (a delphi_OOo library which includes CreateProperties, ConnectOpenOffice or CreateUnoService procedures like DannyB's library for VB)), it works... I don't understand why my code which should do the same things doesn't work...

Did you try to create new instances for every element of array flatProp ? That is, don't reuse the same instance as you are doing. It's only a suggestion I don't know Delphi.

Quote:

B. Marcelly said me that CharSet is a string... if you don't instanciate it OOo will use the system value...

That's right. The long value is used with Driver connection, sorry.

Quote:

the "|" (pipe?) is equivalent to ":" on all systems??? I didn't know that...

I don't know and I didn't find anything about it in the DevGuide.

HTH
Back to top
View user's profile Send private message
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Wed Apr 20, 2005 10:26 pm    Post subject: Reply with quote

Danad wrote:

Did you try to create new instances for every element of array flatProp ? That is, don't reuse the same instance as you are doing. It's only a suggestion I don't know Delphi.

Very Happy Thanks, I'm a newbee in delphi too Wink and I didn't know it could create problems
It's the solution....
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
dyvim
General User
General User


Joined: 13 Apr 2005
Posts: 19
Location: France, Paris

PostPosted: Wed Apr 27, 2005 11:45 pm    Post subject: Reply with quote

It could be a new topic but I post it here....

Thanks to Danad and other developpers i could create a module in Delphi who could create a datasource and execute a mailmerge with OOo 1.1.4...
I test my function with OOo 1.9.95 (the last release of 2.0 beta) and it did not works...

first problem:
You can't open a document which contains Macros (the fields for mailmerge seems to be Macros) with the API without having authorized the execution of Macros with OOo...
It's good for security but how can we authorize this with the API ???
or how can we register our Macros (get an official certification ?) so that OOo won't reject our Macros...
Of course it can be done manually but it's not cool for users

second problem:
You can't register a datasource in the Data Context... It seems that you must save it as a database before (".odb")...

If someone can give me some answers...
_________________
Dyvim
AnalyseSI (open source software for DataBase Design)
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Danad
OOo Advocate
OOo Advocate


Joined: 22 Feb 2004
Posts: 293
Location: Brasil

PostPosted: Thu Apr 28, 2005 4:09 pm    Post subject: Reply with quote

More about:

http://www.oooforum.org/forum/viewtopic.phtml?p=76328
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