Discussion :

[L0007]

Bonjour,

Je débute avec PDI et j'essaie de reproduire un exemple du Cookbook PDI 4.

Je crée une nouvelle Transformation.
J'utilise le composant Generate Rows et je lui donne un nouveau paramètre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Nom: productline_par | type: String | Value: Classic Cars

Je fais le preview : OK

J'ajoute le composant Table Input:
Je lui donne les renseignements suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL:
SELECT
  PRODUCTLINE
, PRODUCTSCALE
, PRODUCTCODE
, PRODUCTNAME
FROM PRODUCTS
WHERE PRODUCTLINE = ?

et comme Insert data from step: Generate Rows.

Je crée un Hop qui va de Generate Rows vers Table Input.
Lorsque je fais le preview: Erreur.

Voici le log:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
2012/01/06 10:24:37 - Spoon - Logging goes to file:///C:/Users/ADMINI~1/AppData/Local/Temp/1/spoon_40aa7898-3848-11e1-aa5b-eb2ef3dd2146.log
2012/01/06 10:24:48 - Version checker - OK
2012/01/06 10:24:49 - class org.pentaho.agilebi.platform.JettyServer - WebServer.Log.CreateListener localhost:10000
2012/01/06 10:24:53 - Spoon - Asking for repository
2012/01/06 10:24:54 - RepositoriesMeta - Reading repositories XML file: C:\Users\Administrator\.kettle\repositories.xml
2012/01/06 12:06:37 - Spoon - Save as...
2012/01/06 12:06:37 - Spoon - Save file as...
2012/01/06 12:09:49 - Spoon - Transformation opened.
2012/01/06 12:09:49 - Spoon - Launching transformation [01]...
2012/01/06 12:09:49 - Spoon - Started the transformation execution.
2012/01/06 12:09:50 - Transformation metadata - Natural sort of steps executed in 5 ms (2 time previous steps calculated)
2012/01/06 12:09:50 - Spoon - The transformation has finished!!
2012/01/06 14:58:42 - /Transformation 2 - Dispatching started for transformation [/Transformation 2]
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unexpected error
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : An error occurred executing SQL: 
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : SELECT
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :   PRODUCTLINE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTSCALE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTCODE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTNAME
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : FROM PRODUCTS
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : WHERE PRODUCTLINE = ?
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Trace.getStackTrace(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Trace.doAssert(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Session.execute(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.ServerConnection.run(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at java.lang.Thread.run(Thread.java:722)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :  in statement [SELECT
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :   PRODUCTLINE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTSCALE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTCODE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTNAME
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : FROM PRODUCTS
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : WHERE PRODUCTLINE = ?]
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.pentaho.di.core.database.Database.openQuery(Database.java:1912)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:223)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:133)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at java.lang.Thread.run(Thread.java:722)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.sql.SQLException: Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Trace.getStackTrace(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Trace.doAssert(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.Session.execute(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.ServerConnection.run(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at java.lang.Thread.run(Thread.java:722)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :  in statement [SELECT
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :   PRODUCTLINE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTSCALE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTCODE
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : , PRODUCTNAME
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : FROM PRODUCTS
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : WHERE PRODUCTLINE = ?]
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	at org.pentaho.di.core.database.Database.openQuery(Database.java:1898)
2012/01/06 14:58:42 - Table input 2.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : 	... 4 more
2012/01/06 14:58:42 - Table input 2.0 - Finished reading query, closing connection.
2012/01/06 14:58:42 - Table input 2.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
2012/01/06 14:58:42 - /Transformation 2 - /Transformation 2
2012/01/06 14:58:42 - /Transformation 2 - /Transformation 2

J'ai tenté la même opération avec un Data Grid. Sans succès.
Si je retire mon paramètre, les données de ma table s'affichent correctemment?

Auriez-vous une idée?

Merci d'avance

[sdecloix]

Bonjour,

Normalement tu as toutes les solutions des exemples du livre "Pentaho Kettle Cookbook".
Il te suffit de les télécharger ici :

[L0007]

Bonjour,

J'ai effectivement essayé l'exemple de ce livre avec ces sources.
Mais ça plante quand même. (Même avec leur script).
J'arrive effectivement à me connecter aux DB et à voir les tables.

Si j'effectue un select sans condition, cela fonctionne.
Dès que je mets la condition dans le select, c'est là que ça plante.

[sdecloix]

Bonjour

Peux-tu indiquer la page et le titre de l'exemple ?

[L0007]

Bien sûr.

J'ai le livre en version électronique.
Page 14.
Chapitre 1
"Getting data from a database by providing parameters"
et le script se trouve dans les solutions du livre dans le répertoire "03. getting data from a database by providing parameters"

Merci pour votre aide

[sdecloix]

Je viens de tester la transformation "steel_wheels_products.ktr" qui fonctionne très bien sur la base Hypersonic de Pentaho
(Cf pièce jointe - PS renommer "steel_wheels_products.zip" en "steel_wheels_products.ktr" )

Sur quelle base de données t'appuies-tu ?