Jim Marion’s post on JDBC made me think a little more. (By the way, thanks, Jim, for linking me.) The one disadvantage about accessing the PeopleSoft database via JDBC is that you have to supply the password to make the connection.
I have thought about trying to read the password from the application server or batch server configuration file, but if I remember right it only has the connect id and the connect password. With the connect password, I might be able to use it to read the Access id and password from the database, but I have would have to be able to unencrypt it.
One advantage that SQL Server might have is that you can use Window’s security. Assuming that the account running the application server or batch server has access to the database, you could just use the integrated security instead of an actual user name or password.
The advantage that JDBC has is that you don’t have to know the number of fields/columns that you want until run time. With both the SQL object and SQLExec, you have to have a variable for each field you return or a return that contains all of those fields.
For example, I am trying to loop through a group of tables in a linked server and copy all of their fields to a table in the current database. I have a problem using INSERT … SELECT, and I have to read the values in and then write them out. I can’t figure another way to do it other than use JDBC.
Please correct me if I am wrong on any of this. Maybe these thoughts will give someone else an idea that I overlooked.
Digital Eagle, you are welcome for linking to you. Thank you for writing up such good information.
I am sorry to tell you that you are wrong. Although, since you want to be wrong in this instance, I am glad to oblige you. The SQL Fetch method can take an array of any. This allows you to fetch a row of data without knowing how many columns were in the original SQL statement. Likewise, the SQLExec function can take an array for bind, output, or one array for both. Here is what PeopleBooks says about SQLExec and arrays:
Because the Array of Any promotes to absorb any remaining select columns, it must be the last parameter for the SQL object Fetch method or (for results) SQLExec. For binding, it must be the only bind parameter, as it is expected to supply all the bind values needed.
Translated, this says that the last parameter of SQLExec can be an array of any that has no elements (Len = 0) and SQLExec will add elements for each item in the select list.
PeopleBooks says the same thing for the SQL Object: Using Array of Any for Bind Values or Fetch Results
In addition, the connectid and password could also be encrypted in the application/batch server config file. And for security reason, it is maybe not very recommanded.
Moreover, I would be very interesting to know how does it work with high security level against the database, i.e. database vault. Any idea ?
Jim,
Thank you very much for pointing out that fact about SQLExec. That helps me greatly in my current project. I haven’t had the opportunity to try it out yet, but I will try to post some code when I get the chance.
Nicolas,
I have not had the chance to use any of these things in production. Everything I have done has been in test environments or conversion scenarios where security was not as much of a concern. Sorry that I am not much help here.
Hi can you briefly tell me about jdbc