Grouper Developers Forum

[Kathryn Huxtable <>]

I forgot the attachments. Sorry. Here they are. -K


On 2/21/07 2:04 PM, "Kathryn Huxtable" 
<>
 wrote:

> This is in reference to the Grouper WG call this morning (CST).
> 
> If we use prepared statements in the JDBCSourceAdapter we will likely want 
> to
> change the syntax of the SQL statements in source.xml. In particular, we 
> need
> to change references to %TERM% to a question mark (?).
> 
> E.g.
> 
> select id,
>    concat(firstname, concat(' ', lastname)) as name,
>    concat(lastname, concat(', ', firstname)) as lfname,
>    lastname, firstname, middlename,
>    account.name as loginid,
>    department, curriculum, appointment
> from individual
>    left join account on (account.individualid = id)
>    left join faculty on (faculty.individualid = id)
>    left join staff on (staff.individualid = id)
>    left join student on (student.individualid = id)
> where (id='%TERM%')
> 
> Becomes
> 
> select id,
>    concat(firstname, concat(' ', lastname)) as name,
>    concat(lastname, concat(', ', firstname)) as lfname,
>    lastname, firstname, middlename,
>    account.name as loginid,
>    department, curriculum, appointment
> from individual
>    left join account on (account.individualid = id)
>    left join faculty on (faculty.individualid = id)
>    left join staff on (staff.individualid = id)
>    left join student on (student.individualid = id)
> where (id=?)
> 
> and
> 
> select id,
>    concat(firstname, concat(' ', lastname)) as name,
>    concat(lastname, concat(', ', firstname)) as lfname,
>    lastname, firstname, middlename,
>    account.name as loginid,
>    department, curriculum, appointment
> from individual
>    left join account on (account.individualid = id)
>    left join faculty on (faculty.individualid = id)
>    left join staff on (staff.individualid = id)
>    left join student on (student.individualid = id)
> where (firstname like '%%TERM%%')
>    or (lastname like '%%TERM%%')
>    or (department like '%%TERM%%')
>    or (account.name like '%%TERM%%')
> 
> becomes
> 
> select id,
>    concat(firstname, concat(' ', lastname)) as name,
>    concat(lastname, concat(', ', firstname)) as lfname,
>    lastname, firstname, middlename,
>    account.name as loginid,
>    department, curriculum, appointment
> from individual
>    left join account on (account.individualid = id)
>    left join faculty on (faculty.individualid = id)
>    left join staff on (staff.individualid = id)
>    left join student on (student.individualid = id)
> where (firstname like '%'||?||'%')
>    or (lastname like '%'||?||'%')
>    or (department like '%'||?||'%')
>    or (account.name like '%'||?||'%')
> 
> This would involve a syntax change in sources.xml for people using the JDBC
> connector.
> 
> Is this alright for people?
> 
> There has also been some suggestion that we use Velocity to do translation
> here. There are advantages there, but it would still probably involve a 
> change
> in syntax.
> 
> I believe that we are better off with the regular JDBC SQL parameter
> substitution syntax, because that's what database people know, and if 
> they're
> configuring this they will know what to do.
> 
> There is also a JNDI parameter substitution mechanism, I believe, that we
> could use.
> 
> I'm inclined to commit the code I'm attaching because it addresses the most
> pressing issues, which are performance of the JDBC connector, and SQL/LDAP
> injection in both the JDBC and JNDI connectors.
> 
> What do people think?
> 
> -K

Attachment: sources.xml
Description: Binary data

Attachment: JNDISourceAdapter.java
Description: Binary data

Attachment: JDBCSourceAdapter.java
Description: Binary data