External mapping utilizing a join condition in query

0 Likes

I recently had the challenge of having to do an external mapping db  lookup, but needed to use a join condition for my results.  Not much  attendtion has been paid to the more comlex queries that some times need  to be don ein order for these thing to work.  So I decided to share  what I came up with so as to help anyone else out who has similar need.


First  I mapped an event EVENTID to exteranlID and use that value in my  query.  There are many ways to do this I chose to do it through the  console.

extmap.0.properties to include join condition.

type=sql
field.getter=exterlId
field.setter.count=2
field.setter[0]=flexString1
field.setter[1]=flexString2
jdbc.class=sun.jdbc.odbc.JdbcOdbcDriver      - MSSQL  I believe it will different for Oracle and other DBs.
jdbc.url=jdb:odbc:Arcsight_myodbc             -  This is the ODBC we created for a connector on the machineso I I  used it and it works, documentation will state to create an ODBC string.


jdbc.username=myusername          
jdbc.password=myobfuscatedpw                  - This is the obfuscated pw created using the Arcsight utility.

Jdbc.query=select  a.primarykey as primarykey, a.field1 as field1, b.field2 as field2   from table1 as a  JOIN  table2 as b on a.field1 = b.field3 \

Where a.primarykey in (?\u0000?)

b.field3 is the key on the second table needed to get b.field2.

field1 gets mapped to setter0
field2 gets mapped to setter1

KB should be written on these more complex queries in addition to the easy ones

Comment List
Anonymous
Parents
  • The more I look at this (and I've spent a while at it now with SQL Profiler) the less certain I am that you'll be able to use a stored proc.  The problem is two-fold and lies in the fact that the external mapper uses a parameterised query (this is good practice and helps prevent sql injection etc) and adds a variable number of parameters based on some internal logic, this isn't a problem when using dynamic sql as the parameters just get added to the in clause "where getter in (@p1, @p2, @p3...)", however, when you define a stored proc you declare the parameters up front, if the number of parameters from the mapper was fixed, this would be ok but it's variable. The best bet and one referenced in Arrays and Lists in SQL Server 2008  was to somehow force the connector to call the stored proc with the parameters passed as a single parameter formatted as a comma separated list, then split the list and insert to a temporary table using a udf and use the temporary table in a join etc. I can't see any way to do this.  My previous answer of using a TVP doesn't work as it's not supported by the JDBC driver and it's not the way the mapper adds the parameters anyway.  The last option is that if we knew the upper limit of the number of parameters that were being passed by the mapper we could declare that number of parameters in the stored proc and assign them a default value of NULL making them optional, insert the non-null variable to a temp table and join on the temporary table but that's subject to breaking if HP change the number of parameters etc.

    If you're being asked to use a stored proc as a security measure i.e. the DBA doesn't want to give you access to the base table to run arbitrary queries perhaps you could come to a compromise by using a view and querying this dynamically instead?

Comment
  • The more I look at this (and I've spent a while at it now with SQL Profiler) the less certain I am that you'll be able to use a stored proc.  The problem is two-fold and lies in the fact that the external mapper uses a parameterised query (this is good practice and helps prevent sql injection etc) and adds a variable number of parameters based on some internal logic, this isn't a problem when using dynamic sql as the parameters just get added to the in clause "where getter in (@p1, @p2, @p3...)", however, when you define a stored proc you declare the parameters up front, if the number of parameters from the mapper was fixed, this would be ok but it's variable. The best bet and one referenced in Arrays and Lists in SQL Server 2008  was to somehow force the connector to call the stored proc with the parameters passed as a single parameter formatted as a comma separated list, then split the list and insert to a temporary table using a udf and use the temporary table in a join etc. I can't see any way to do this.  My previous answer of using a TVP doesn't work as it's not supported by the JDBC driver and it's not the way the mapper adds the parameters anyway.  The last option is that if we knew the upper limit of the number of parameters that were being passed by the mapper we could declare that number of parameters in the stored proc and assign them a default value of NULL making them optional, insert the non-null variable to a temp table and join on the temporary table but that's subject to breaking if HP change the number of parameters etc.

    If you're being asked to use a stored proc as a security measure i.e. the DBA doesn't want to give you access to the base table to run arbitrary queries perhaps you could come to a compromise by using a view and querying this dynamically instead?

Children
No Data
Related Discussions
Recommended