Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Question Génération dynamique de requete

More
05 Jun 2018 12:05 #1 by olivier
Génération dynamique de requete was created by olivier
Bonjour,

dans le cadre d'une validation de données, je souhaite construire une requête dynamique.

En source, j'ai une table qui comporte
Colonne,
typedonnees,
longueur,
RGcolonne,
ControleSql

Je parcours la table que j'ai créer, dans un lien bind,

je souhaite construire ma requête.

select :{Colonne}:
from %x{$ACQ/../tech:rejectName()}x%.%x{$ACQ/tech:rejectName()}x% r
where 1=1
and nullif(:{Colonne}:, '') is not null


ici contruire ma close where.
%b(rhino){
addSql="" ;
if (:{longueur}: > 0)
addSql=" and length(:{Colonne}:) not between 1 and :{longueur}:" ;
__ctx__.retValue = addSql ;

}b(rhino)%


Je pense avoir des pb de syntaxes,

Merci de votre aide
More
05 Jun 2018 12:08 #2 by Thomas BLETON
Replied by Thomas BLETON on topic Génération dynamique de requete
Quelle erreur rencontrez-vous ?
More
05 Jun 2018 14:05 #3 by olivier
Replied by olivier on topic Génération dynamique de requete
Bonjour Thomas,

j'ai cette erreur

SqlOperation::ControleDataVarChar
com.indy.engine.common.exceptions.EngineExceptionI: com.indy.engine.common.exceptions.EngineExceptionI: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: erreur de syntaxe (<Unknown source>#3) in <Unknown source> at line number 3
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:580)
at com.indy.engine.actionCodes.JdbcActionCodeI.executeDirectBindedFetch(SourceFile:1028)
at com.indy.engine.action.common.ActionCodeTypeI.executeDirectBindedCode(SourceFile:1440)
at com.indy.engine.action.common.ActionCodeTypeI.executeBindedCode(SourceFile:1612)
at com.indy.engine.action.common.ActionCodeTypeI.executeCode(SourceFile:1677)
at com.indy.engine.action.common.ActionCodeTypeI.run(SourceFile:1852)
at java.lang.Thread.run(Unknown Source)
Caused by: com.indy.engine.common.exceptions.EngineExceptionI: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: erreur de syntaxe (<Unknown source>#3) in <Unknown source> at line number 3
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:540)
... 6 more
Caused by: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: erreur de syntaxe (<Unknown source>#3) in <Unknown source> at line number 3
at com.sun.script.javascript.RhinoScriptEngine.eval(Unknown Source)
at com.sun.script.javascript.RhinoScriptEngine.eval(Unknown Source)
at javax.script.AbstractScriptEngine.eval(Unknown Source)
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:477)
... 6 more
Caused by: sun.org.mozilla.javascript.internal.EvaluatorException: erreur de syntaxe (<Unknown source>#3)
at sun.org.mozilla.javascript.internal.DefaultErrorReporter.runtimeError(Unknown Source)
at sun.org.mozilla.javascript.internal.DefaultErrorReporter.error(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.reportError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.reportError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.reportError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.primaryExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.memberExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.unaryExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.mulExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.shiftExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.relExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.eqExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.bitAndExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.bitXorExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.bitOrExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.andExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.orExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.condExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.assignExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.expr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.condition(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.ifStatement(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.statementHelper(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.statement(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.parse(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.parse(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileImpl(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileReader(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileReader(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.evaluateReader(Unknown Source)
... 10 more
More
05 Jun 2018 14:38 #4 by Thomas BLETON
Replied by Thomas BLETON on topic Génération dynamique de requete
OK merci.
La syntaxe du script semble correcte. Il y a peut être une donnée qui rend le script invalide une fois les variables substituées ?
Pouvez-vous vérifier les données contenues dans les colonnes :{longueur}: et :{Colonne}: ?
Pour ce faire, vous pouvez par exemple faire un
print("longueur: :{longueur}: Colonne: :{Colonne}:");
... et consulter le résultat sur la Console
More
05 Jun 2018 17:16 #5 by olivier
Replied by olivier on topic Génération dynamique de requete
Je n'arrive pas a consulter la console. Ca se trouve ou dans le designer ?

Cependant, j'ai ecrit les instructions dans un writefile.

J'ai bien la substition attendu.

j'ai simplifié mon code.. pour comprendre le fonctionnement du scripting
insert into %x{$DHB_DHB_DAX_RJT/tech:physicalPath()}x%
(displayproductnumber, colonne_rejet, Motif)

select displayproductnumber, ':{Colonne}:', 'Lolo'
from %x{$ACQ/../tech:rejectName()}x%.%x{$ACQ/tech:rejectName()}x% r
where 1=1
and nullif(:{Colonne}:, '') is not null
%b(rhino){
var nomColonne=':{Colonne}:'
var addSql= "" ;
addSql += " and "+ nomColonne ;

__ctx__.retValue = addSql ;
}b(rhino)%


j'ai cette errreur
WriteTxtFile
com.indy.engine.common.exceptions.EngineExceptionI: com.indy.engine.common.exceptions.EngineExceptionI: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: le littéral de la chaîne n'a pas de limite (<Unknown source>#2) in <Unknown source> at line number 2
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:580)
at com.indy.engine.action.common.ActionCodeTypeI.getFinalBindedCode(SourceFile:663)
at com.indy.engine.actionCodes.WriteTxtFileActionCodeI.prepare(SourceFile:54)
at com.indy.engine.actionCodes.WriteTxtFileActionCodeI.executeDirectBindedFetch(SourceFile:36)
at com.indy.engine.action.common.ActionCodeTypeI.executeDirectBindedCode(SourceFile:1440)
at com.indy.engine.action.common.ActionCodeTypeI.executeBindedCode(SourceFile:1612)
at com.indy.engine.action.common.ActionCodeTypeI.executeCode(SourceFile:1677)
at com.indy.engine.action.common.ActionCodeTypeI.run(SourceFile:1852)
at java.lang.Thread.run(Unknown Source)
Caused by: com.indy.engine.common.exceptions.EngineExceptionI: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: le littéral de la chaîne n'a pas de limite (<Unknown source>#2) in <Unknown source> at line number 2
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:540)
... 8 more
Caused by: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EvaluatorException: le littéral de la chaîne n'a pas de limite (<Unknown source>#2) in <Unknown source> at line number 2
at com.sun.script.javascript.RhinoScriptEngine.eval(Unknown Source)
at com.sun.script.javascript.RhinoScriptEngine.eval(Unknown Source)
at javax.script.AbstractScriptEngine.eval(Unknown Source)
at com.indy.engine.main.scripting.ScriptManagerI.executeSubstitutionScript(SourceFile:477)
... 8 more
Caused by: sun.org.mozilla.javascript.internal.EvaluatorException: le littéral de la chaîne n'a pas de limite (<Unknown source>#2)
at sun.org.mozilla.javascript.internal.DefaultErrorReporter.runtimeError(Unknown Source)
at sun.org.mozilla.javascript.internal.DefaultErrorReporter.error(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addError(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.addError(Unknown Source)
at sun.org.mozilla.javascript.internal.TokenStream.getToken(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.peekToken(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.assignExpr(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.variables(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.statementHelper(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.statement(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.parse(Unknown Source)
at sun.org.mozilla.javascript.internal.Parser.parse(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileImpl(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileReader(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.compileReader(Unknown Source)
at sun.org.mozilla.javascript.internal.Context.evaluateReader(Unknown Source)
... 12 more
More
05 Jun 2018 17:51 #6 by Thomas BLETON
Replied by Thomas BLETON on topic Génération dynamique de requete
Je voulais parler de la console du Runtime (là où la sortie standard du scripting s'affiche).

Dans le dernier exemple, il manque peut être un point virgule à la fin de cette ligne :
var nomColonne=':{Colonne}:'

Vérifiez bien que dans la table source le champ Colonne ne contienne pas de valeur avec des sauts de lignes.
More
05 Jun 2018 20:35 #7 by olivier
Replied by olivier on topic Génération dynamique de requete
Bonsoir,

j'avais corrigé cette erreur avant de poster.

en effet dans les données sources j'avais un retour chariot.

Merci
More
06 Jun 2018 11:01 #8 by olivier
Replied by olivier on topic Génération dynamique de requete
Thomas,

pour utiliser des fonctions java contains, substring, etc... nous devons faire un appel spécifique ?


j'ai ajouté cette ligne
importPackage(java.text);


j'ai ce retour

om.indy.engine.common.exceptions.EngineExceptionI: com.indy.engine.common.exceptions.EngineExceptionI: javax.script.ScriptException: sun.org.mozilla.javascript.internal.EcmaError: TypeError: Cannot find function contains in object 8. (<Unknown source>#8) in <Unknown source> at line number 8
More
06 Jun 2018 11:31 #9 by olivier
Replied by olivier on topic Génération dynamique de requete
Remplacé contains par indexOf=>ca fonctionne