Re: [basex-talk] problem with prepared - BaseX-Talk - mailman.uni-konstanz.de

10 Sep 2015


      Sorry and thanks for the answer.
Michele
--------- Original Message --------
Da: "Dirk Kirsten" &lt;dk@basex.org&gt;
To:
Cc: "Maximilian G&Atilde;&fnof;&Acirc;&curren;rber"
&lt;mgaerber@arcor.de&gt;, basex-talk@mailman.uni-konstanz.de
Oggetto: Re: [basex-talk] problem with prepared
Data: 10/09/15 10:28
Hello Michele,
please always make sure to send us a SSCCEE (see http://sscce.org/). It is
very time-consuming to read such a large XQuery and I highly doubt all this
is relevant for your actual problem.
Also, I highly doubt that you need 8 (!) for-loops, which are all nested.
This takes an execution time of n^8, which is **a lot**, even for small
values of n.
Also, all your for loops seem to do basically the same and work on the same
data, so I doubt that it is really neccessary.
Based on this and your previous questions I would recommend you to get a
better understanding of XQuery before tackling your actual problem.
Otherwise, you will always stumble across new problems, which you could self
yourself better and faster if you know the core conecpts. I can recommend
the XQuery book by Priscilla Walmsley, I also find the wikibook about XQuery
(https://en.wikibooks.org/wiki/XQuery) quite useful.
As part of our commercial offering we also offer seminars or on-site
training (see http://basexgmbh.de/en/). If you are interested, please
contact us in private.
Regards,
Dirk
On 09/10/2015 10:10 AM, michele.greco2@email.it wrote:
Thanks for the help ,Max. I modified my xquery this way:
declare namespace pkg="http://schemas.microsoft.com/office/2006/xmlPackage";
declare namespace
w="http://schemas.openxmlformats.org/wordprocessingml/2006/main";
declare function local:indice() as xs:integer* {
&nbsp; for $i in(1 to count(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Nome"]
&nbsp;return $e/./following::text()except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Indirizzo"]
return $x//following::text())))
return $i
};
declare function local:nome() as xs:string* {
for $n in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Nome"]
&nbsp;return $e/./following::text()except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Indirizzo"]
return $x//following::text()))
return $n
};
declare function local:indirizzo() as xs:string* {
for $in in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Indirizzo"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Telefono"]
return $x//following::text()))
return $in&nbsp;
};
declare function local:telefono() as xs:string* {
for $t in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Telefono"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Fax"]
return $x//following::text()))
return $t
};&nbsp;
declare function local:fax() as xs:string* {
for $f in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Fax"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text()="E-mail"]
return $x//following::text()))
return $f
};
declare function local:email() as xs:string* {
&nbsp;for $em in
&nbsp;(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="E-mail"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Nazionalit&agrave;"]
return $x//following::text()))
return $em &nbsp;
};
declare function local:nazionalita() as xs:string* {
for $na in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Nazionalit&agrave;"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text() ="D" ]
return $x//following::text()))
return $na
};
declare function local:dataNascita() as xs:string* {
for $d in
(for $document in collection("curriculum")
let $c:= document-uri($document)
for $a in doc($c)
order by $c
return&nbsp;
for $e in doc($c)//w:tc[.//text()="Data di nascita"]
return $e/./following::text() except
&nbsp; (for $x in doc($c)//w:tc[.//text()="Esperienza lavorativa"]
return $x//following::text()))
return $d
};
for $indice at $pos in local:indice()
for $nome in local:nome() [$pos]
for $indirizzo in local:indirizzo()[$pos]
for $telefono in local:telefono()[$pos]
for $fax &nbsp;in local:fax()[$pos]
for $email &nbsp;in local:email()[$pos]
for $nazionalita &nbsp;in local:nazionalita()[$pos]
for $datanascita &nbsp;in local:dataNascita()[$pos]
let $init := sql:init("com.mysql.jdbc.Driver"),
&nbsp; &nbsp; $conn :=
sql:connect("jdbc:mysql://localhost:3306/DbName","user","password''),
&nbsp; &nbsp; $stmt := sql:prepare($conn, "INSERT INTO
informazionipersonali(idInformazioniPersonali,nome,indirizzo,telefono,fax,email,nazionalit&agrave;,dataNascita)
VALUES(?,?,?,?,?,?,?,?) ")
&nbsp; &nbsp; &nbsp; &nbsp; let $params:= &nbsp;&lt;sql:parameters&gt;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp;&lt;sql:parameter
type="int"&gt;{$indice}&lt;/sql:parameter&gt;&nbsp;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$nome}&lt;/sql:parameter&gt;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$indirizzo}&lt;/sql:parameter&gt;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$telefono}&lt;/sql:parameter&gt;&nbsp;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$fax}&lt;/sql:parameter&gt;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$email}&lt;/sql:parameter&gt;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$nazionalita}&lt;/sql:parameter&gt;&nbsp;
&nbsp; &nbsp; &nbsp; &lt;sql:parameter
type="string"&gt;{$datanascita}&lt;/sql:parameter&gt;&nbsp;
&nbsp; &nbsp; &lt;/sql:parameters&gt;
&nbsp;return sql:execute-prepared($stmt,$params)
&nbsp;
It works,but it returns only the first row in my database, and i want a row
for each value (indice,nome,indirizzo,........)
How can i do?
--------- Original Message --------
Da: "Maximilian G&Atilde;&curren;rber" &lt;mgaerber@arcor.de&gt;
To: "basex-talk@mailman.uni-konstanz.de"
&lt;basex-talk@mailman.uni-konstanz.de&gt;
Oggetto: Re: [basex-talk] problem with prepared
Data: 09/09/15 09:12
or using a where statement
for $indice at $pos in local:indice()
for $nome at $pos2 in local:nome()
let $params:= &lt;sql:parameters&gt;
&lt;sql:parameter type="int"&gt;{$indice}&lt;/sql:parameter&gt;
&lt;sql:parameter type="string"&gt;{$nome}&lt;/sql:parameter&gt;
&lt;/sql:parameters&gt;
where $pos2 = $pos
return $params
2015-09-09 9:05 GMT+02:00 Maximilian G&auml;rber &lt;mgaerber@arcor.de&gt;:
&gt; Hi,
&gt;
&gt; when you join (by nesting for statements) you will always get the
&gt; combinations unless you provide more constraints.
&gt;
&gt; You might try something like:
&gt;
&gt; declare function local:indice() as xs:integer* {
&gt;
&gt; for $i in (1 to 10)
&gt; return $i
&gt; };
&gt;
&gt; declare function local:nome() as xs:string* {
&gt;
&gt; for $n in ('a', 'b', 'c', 'd')
&gt; return $n
&gt; };
&gt;
&gt; for $indice at $pos in local:indice()
&gt; for $nome in local:nome()[$pos]
&gt;
&gt; let $params:= &lt;sql:parameters&gt;
&gt; &lt;sql:parameter type="int"&gt;{$indice}&lt;/sql:parameter&gt;
&gt; &lt;sql:parameter type="string"&gt;{$nome}&lt;/sql:parameter&gt;
&gt; &lt;/sql:parameters&gt;
&gt; return $params
&gt;
&gt;
&gt; Regards, Max
&gt;
&gt; 2015-09-08 16:20 GMT+02:00 &lt;michele.greco2@email.it&gt;:
&gt;&gt; Hi i have following xquery:
&gt;&gt; declare function local:indice() as xs:integer* {
&gt;&gt; count(for $document in collection("curriculum")
&gt;&gt; let $c:= document-uri($document)
&gt;&gt; for $a in doc($c)
&gt;&gt; order by $c
&gt;&gt; return
&gt;&gt; for $e in doc($c)//w:tc[.//text()="Nome"]
&gt;&gt; return $e/./following::text()except
&gt;&gt; (for $x in doc($c)//w:tc[.//text()="Indirizzo"]
&gt;&gt; return $x//following::text()))
&gt;&gt; };
&gt;&gt;
&gt;&gt; declare function local:nome() as xs:string* {
&gt;&gt; for $document in collection("curriculum")
&gt;&gt; let $c:= document-uri($document)
&gt;&gt; for $a in doc($c)
&gt;&gt; order by $c
&gt;&gt; return
&gt;&gt; for $e in doc($c)//w:tc[.//text()="Nome"]
&gt;&gt; return $e/./following::text()except
&gt;&gt; (for $x in doc($c)//w:tc[.//text()="Indirizzo"]
&gt;&gt; return $x//following::text())
&gt;&gt; };
&gt;&gt; for $ indice in local:indice()
&gt;&gt; let $init := sql:init("com.mysql.jdbc.Driver"),
&gt;&gt; $conn :=
&gt;&gt;
sql:connect("jdbc:mysql://localhost:3306/DBName","user","password''),
&gt;&gt; $stmt := sql:prepare($conn, "INSERT INTO
&gt;&gt;
informazionipersonali(idInformazioniPersonali,nome,indirizzo,telefono,fax,email,nazionalit&agrave;,dataNascita)
&gt;&gt; VALUES(?,?,'null','null','null','null','null','null') ")
&gt;&gt; for $nome in local:nome()
&gt;&gt; let $params:= &lt;sql:parameters&gt;
&gt;&gt; &lt;sql:parameter type="int"&gt;{$indice}&lt;/sql:parameter&gt;
&gt;&gt; &lt;sql:parameter type="string"&gt;{$nome}&lt;/sql:parameter&gt;
&gt;&gt; &lt;/sql:parameters&gt;
&gt;&gt; return sql:execute-prepared($stmt,$params)
&gt;&gt;
&gt;&gt;
&gt;&gt; which return a combination of index and names, but i want only one
row for
&gt;&gt; each index and name.
&gt;&gt; How can i do?
&gt;&gt;
&gt;&gt; ----
&gt;&gt; ZE-Light e ZE-Pro: servizi zimbra per caselle con dominio
email.it, per
&gt;&gt; tutti i dettagli clicca qui
&gt;&gt;
&gt;&gt; Sponsor:
&gt;&gt; Registra i domini che desideri ed inizia a creare il tuo sito web
&gt;&gt; Clicca qui
----
ZE-Light e ZE-Pro: servizi zimbra per caselle con dominio email.it, per
tutti i dettagli clicca qui
Sponsor:
Soluzioni di email hosting per tutte le esigenze: dalle caselle gratuite a
quelle professionali su piattaforma Zimbra, da quelle su proprio dominio a
quelle certificate PEC. Confronta le soluzioni
Clicca qui
-- 
Dirk Kirsten, BaseX GmbH, http://basexgmbh.de
|-- Firmensitz: Blarerstrasse 56, 78462 Konstanz
|-- Registergericht Freiburg, HRB: 708285, Gesch&auml;ftsf&uuml;hrer:
|   Dr. Christian Gr&uuml;n, Dr. Alexander Holupirek, Michael Seiferle
`-- Phone: 0049 7531 28 28 676, Fax: 0049 7531 20 05 22


 --
 ZE-Light e ZE-Pro: servizi zimbra per caselle con dominio email.it, per tutti i dettagli 
Clicca qui http://posta.email.it/caselle-di-posta-z-email-it/?utm_campaign=email_Zimbra...

 Sponsor:
 Caselle con tuo dominio su piattaforma Zimbra, fino a 30 GB di spazio, sincronizzazione dati e backup
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=13324&d=10-9