Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 30 May 2009 @ 14:43:12 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: JDBC driver and the use of the '?'
 
From:   McCall, Glenn David

  Thanks again, but from an audit trail point of view, is there any way to be able to capture the data that will be used in the 'placeholders'? These values would have to be used in the creation of the SQL that is submitted, so they have to be within Teradata somewhere surely!  


I do not believe so. When using Prepared queries, The SQL and the values are submitted separately. When it is executed, the query text explain plan and any other bits and pieces enabled in DBQL are recorded. As far as I know there isn't a way to capture the individual values.

If your query was a Insert, Update or delete, you could capture values via triggers etc. Alternatively if you used macros, the macros could record the parameters used in a select. For example:

     Replace macro query_tbl (i1 integer, i2 integer) as
     (
     Select * from tbl where id1 = :i1 and id2 = :i2;
     Insert into tbl_data_requests values (:i1, :i2, user,
     current_timestamp);
     );

Or something like that.

I don't know what your environment is, but the alternative is to not use Prepared Queries and write your code so that the values appear in the query as literals.

That is instead of:

     Prepare query select * from tbl where id1 = ? and id2 = ?
     Execute query with values (1,1)
     Execute query with values (1,2)
     Etc

Do this:

     Execute query select * from tbl where id1 = 1 and id2 = 1
     Execute query select * from tbl where id1 = 1 and id2 = 2

This *may* raise potential security issues (for example an SQL Injection attack) and performance issues (the first approach will likely run faster than the second due to cacheing of query plans and possibly due to lower network traffic although this will depend upon a few other things as well).


Glenn Mc



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023