topic Re: Adding variables to SQL query for input in Talend Studio

Adding variables to SQL query for input

Anonymous — Mon, 06 Aug 2018 15:50:45 GMT

Hello,

I have a problem formating a SQL query. I format some datas using tmap, and i want to do a huge query using tPorestgresqlRow :

My SQL request begin with that :

"CREATE OR REPLACE FUNCTION initFirst() RETURNS boolean AS $$
DECLARE people RECORD;

BEGIN

For people IN (
SELECT '"+X.name+"' name, ......

)
LOOP

INSERT INTO ......
Etc.

If i don't add the ' before " (or after "), i have an SQL error indicating that X.name is not a column.

If i do with '"+X.name+"', i have another error : ERROR: unterminated quoted string at or near "' name

Any ideas how i can solve this issue ?

Thanks !

Re: Adding variables to SQL query for input

Anonymous — Mon, 06 Aug 2018 16:18:38 GMT

Hi,

Could you please try as shown in the screen shot? I have given employee_id as the column name but underlying DB column name is id.

You can give the column and table name details first and press Guess query button to get a proper query code. Once you get the code, you can add necessary where clauses in same format.

If the answer has helped you, could you please mark the topic as solution provided?

Warm Regards,

Nikhil Thampi

Re: Adding variables to SQL query for input

Anonymous — Mon, 06 Aug 2018 16:51:35 GMT

Hello,

I try to do an insert request, using loops and select. it's a multitable request in output and not a database in input.I can't specify a tablename in input.

For example, X.name is not from a database, but a text. I need to add ' ' around in order to be considered as a value and not as a column name.

Re: Adding variables to SQL query for input

Anonymous — Mon, 06 Aug 2018 18:29:40 GMT

Hi,

If you are fine, could you please share the code you have inserted in the problematic component? It will help in detailed analysis.

Warm Regards,

Nikhil Thampi

Re: Adding variables to SQL query for input

Anonymous — Mon, 06 Aug 2018 18:49:30 GMT

This is the code i posted first.

More of the code

CREATE OR REPLACE FUNCTION initFirst() RETURNS boolean AS $$
DECLARE people RECORD;

BEGIN

For people IN (
SELECT " + X.sexe + " sexe, " + X.civ + " civilite, " + X.nom + " nom, " + X.prenom + " prenom, " + X.ine + " ine, " + X.login + " login, " + X.mail + " mail "
)

LOOP
---------------------------------------------------------------
-- 1st

---------------------------------------------------------------
INSERT INTO REF_PERSONNE (idEcole, idCivilite, idSexe, idSitMaritale, idPersCreat, codePaysNaiss, codeNationnalite, codedblenationnalite, nom, nomUsage, bPubliNomMari, prenom, prenomAutre, nbEnfants, dateNaiss, villeNaiss, bInactif, siteWeb, dateCreat, idexterne)
VALUES (1,personne.civilite,personne.sexe,null,1,null, null, null, personne.nom, personne.nom, true, personne.prenom, null, null, null, personne.villenaissance, false, null, localtimestamp(0), personne.xuniqueid);

INSERT INTO REF_PERS_CATEGORIE (idPersonne, idCategorie, idPersDemande, dateDebEffet, dateFinEffet, dateDemande)
VALUES (getLastId('ref_personne_id_seq'),181, 1, localtimestamp(0),null, localtimestamp(0));

[---]

END LOOP ;

RETURN true;
END;
$$ LANGUAGE plpgsql;

select initFirst();
drop function initFirst();"

I'm using function in order to insert all the values at once. If there's any problems in any insert into, it stop the function, so i'm sure all the queries are done.

My problem is : how can i correctly write this part :

SELECT " + X.sexe + " sexe, " + X.civ + " civilite, " + X.nom + " nom, " + X.prenom + " prenom, " + X.ine + " ine, " + X.login + " login, " + X.mail + " mail "

so, the X.sexe, X.civ, ...

I send a screenshot tomorrow because i don't have my laptop, but just before the request (inserted in a tPostregresqRow), there's a tMap (and the output are the variables X.sexe, etc.)

I already use this complete query, but not interfacing it with Talend (by hand, creating the SELECT lines with UNION at the end of each select line).

Re: Adding variables to SQL query for input

Anonymous — Tue, 07 Aug 2018 06:41:07 GMT

And in the tPostgresqlRow, a simplified version :

"CREATE OR REPLACE FUNCTION initFirst() RETURNS boolean AS $$
DECLARE personne RECORD;

BEGIN

For personne IN (
SELECT '"+X.nom+"' nom

)
LOOP

INSERT INTO REF_PERSONNE (idEcole, idCivilite, idSexe, idSitMaritale, idPersCreat, codePaysNaiss,
codeNationnalite, codedblenationnalite, nom, nomUsage, bPubliNomMari, prenom, prenomAutre,
nbEnfants, dateNaiss, villeNaiss, bInactif, siteWeb, dateCreat)
VALUES (1,147,150,null,1,null, null, null, personne.nom, personne.nom, true, personne.nom, null,
null, null, null, false, null, localtimestamp(0));

INSERT INTO REF_PERS_CATEGORIE (idPersonne, idCategorie, idPersDemande, dateDebEffet, dateFinEffet,
dateDemande)
select max(rp.id) ,181, 1, localtimestamp(0),null, localtimestamp(0) from ref_personne rp;

END LOOP ;

RETURN true;
END;
$$ LANGUAGE plpgsql;

select initFirstPersonne();
drop function initFirstPersonne();"

I always have the same problem :

ERROR: unterminated quoted string at or near "' nom

My question is : how i can correctly construct the "SELECT" request ( escaping characters, etc. ?).

EDIT : if i use this query :

SELECT "+"'"+X.nom+"'"+"' nom

my error is now ERROR: unterminated quoted string at or near "'Rouffeteau'' nom (so the X.nom has been correctly set). But still an error...

Re: Adding variables to SQL query for input

Anonymous — Tue, 07 Aug 2018 09:29:15 GMT

Hi,

If I understood the requirement correctly, you would like to load multiple records to two tables based on business condition and if there is any reject or issue, you would like to do the rollback for entire transaction.

For this scenario, you can refer the below post which helped to implement the rollback for entire transaction if there are any issues.

https://community.talend.com/t5/Design-and-Development/resolved-Rollback-entire-transaction/td-p/114025

Could you please try this approach and let us know whether it helped you?

Warm Regards,

Nikhil Thampi