| View previous topic :: View next topic |
| Author |
Message |
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
 Posted: Thu Apr 14, 2005 10:49 pm Post subject: MailMerge and DataSource (i can't find the table I need) |
 |
|
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 |
|
 |
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Mon Apr 18, 2005 5:18 am Post subject: |
|
|
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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Mon Apr 18, 2005 10:38 pm Post subject: |
|
|
| 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...  
_________________
Dyvim
AnalyseSI (open source software for DataBase Design) |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Tue Apr 19, 2005 5:15 am Post subject: |
|
|
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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Tue Apr 19, 2005 5:39 am Post subject: |
|
|
| 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 |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
| Posted: Tue Apr 19, 2005 6:20 pm Post subject: |
|
|
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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Tue Apr 19, 2005 10:36 pm Post subject: |
|
|
| 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... 
| 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 |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
| Posted: Wed Apr 20, 2005 4:35 am Post subject: |
|
|
| 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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Wed Apr 20, 2005 5:02 am Post subject: |
|
|
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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Wed Apr 20, 2005 5:23 am Post subject: |
|
|
| 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 |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
|
| Back to top |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
| Posted: Wed Apr 20, 2005 7:59 pm Post subject: |
|
|
| 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 |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Wed Apr 20, 2005 10:26 pm Post subject: |
|
|
| 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.
|
 Thanks, I'm a newbee in delphi too  and I didn't know it could create problems
It's the solution....
_________________
Dyvim
AnalyseSI (open source software for DataBase Design) |
|
| Back to top |
|
|
dyvim
General User
Joined: 13 Apr 2005
Posts: 19
Location: France, Paris
|
| Posted: Wed Apr 27, 2005 11:45 pm Post subject: |
|
|
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 |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
|
| Back to top |
|
|
|
