Four Stars

Adding variables to SQL query for input

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 ! 
 

 

6 REPLIES
Employee

Re: Adding variables to SQL query for input

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.

image.png

 

 

 

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

 

Warm Regards,

 

Nikhil Thampi

Four Stars

Re: Adding variables to SQL query for input

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.

 

 

 

Employee

Re: Adding variables to SQL query for input

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

Four Stars

Re: Adding variables to SQL query for input

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).

 

Four Stars

Re: Adding variables to SQL query for input

1.JPG

 

 

 

2.JPG

 

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...

 

Employee

Re: Adding variables to SQL query for input

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/114...

 

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

 

Warm Regards,

 

Nikhil Thampi