Query Templates - Insert

[Former Member]

Hi,

I want to insert the value se "B' xt into oracle db through Query template. This value will pass through input parameter Param.1 . This will insert in oracle db in normal SQL plus as

insert into <Table_Name> values (' se"B' '').

But when i try this in Query template as

 insert into <Table_Name> values (' '[Param.1]' '')  

it is throwing error. How to insert like this word through query template.

Regards,

Senthil

Edited by: senthil kumar on Jun 9, 2009 10:22 AM

[Former Member]

Hi,

we have resolved using stringreplace function in transaction.

regards,

senthil

[former_member202228]

As Michael said, if you need to insert three values, please use commas.

If you need to escape quotes (and this is why you doubled them) build your Param.1 value with the quotes already escaped (i.e. Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally), then run the query ..

For T-SQL (i.e. MS-SQL) use JS functions as below:

// escapes strings for SQL storage
function escapeSQLString(txtString) { 
   var sqlString = String(txtString).replace(/'/g,"''"); 
   return sqlString;
}

For Oracle (PL-SQL), just take a look at

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref378

and build your JS function accordingly.

[jcgood25]

Senthil,

For clarification purposes an SQL Query Template in FixedQuery mode used in conjunction with an iCommand applet on a web page is NOT the same as Mike's earlier comment about using Command mode.

Additionally, you mention that it is 'throwing an error' - actually quoting the error in your SDN post would be quite useful.

Regards,

Jeremy

[agentry_src]

Hi Senthil,

Make sure your query template is in Command Mode rather than Fixed Query or Query.

Good luck,

Mike

[Former Member]

Hi,

I have checked. It is in iCommand mode only.

Regards,

Senthil

[agentry_src]

Check your single quote count. It also looks like your syntax really should be:

  insert into <Table_Name> (<FieldName>) values ('[Param.1]')  

If you are entering 3 separate values, they need to be separated by commas.

Edited by: Michael Appleby on Jun 9, 2009 12:14 PM