Re: SET ROLE withing SPL experiences?

This shows a significant shortcoming in the Informix stored procedure languate we all know about. It may be solved with the 9.2 kernel (the next release after 7.3 and 9.1x into one common server with options you can probably select to install or not) that's supposed to come out sometimes this autumn. This will among other things include the ability to write stored procedures in Java which would of course solve all the problems. In the meantime here is one totally untested idea that may be something to work on: You do your long list of if statements. Within each if statement you do however execute a stored procedure that sets the appropriate role (or does whatever else anyone might want). There is supposed to be one stored procedure for each if statement with a different name to get arround the parameter limitations. If this is unsuccessfull because the stored procedure didn't exist you use your B) solution to create one, but now with the appropriate name for this part of the if statement. Then you execute again, this time hopefully with success. The next time the same user used the main stored procedure there would be no need to create one, it allready existed. The key idea here is that you wouldn't have to manually maintain all the stored procedures. They would be automatically created as they are needed. Of course you still have the problem of maintaining the main stored procedures with all it's if's. That may be possible to simplify significantly by looking up a number for the username in a table and use that for the if-statements instead of the username directly. Then you would only have to make sure there where enough if's for the number of users you have. As to the speed of execution with a high number of if-statements I would believe it is fast enough. This is of course something you have to test. This is neither elegant nor fun, but might work. On Thu, 13 Aug 1998 15:27:03 GMT, dennisp@my-dejanews.com wrote: >Is anyone using SET ROLE within a stored procedure, and if so have you figured >out a way to SET ROLE to a variable? > >The problem is: With a dozen different ways to connect to the database (via >several front-end tools, Web/CORBA, etc), we don't want to code the "SET >ROLE" statement inline in the front-end code (some connectivity tools won't >even allow it). We can, however, impose that all front-ends execute a >"setrole" SPL right after connecting to the database. Because of the SPL >restriction that SQL within the procedure must not require run-time >preparation (no using variables except for filters & joins), we can't just >"SET ROLE variable", where variable is fetched from sysroleauth for the given >USER. > >We can think of only two ways to crack this nut, both which have problems: > >1) Use a nested if statement for each role defined in sysroleauth and call SET >ROLE specific for the given variable value. With 300+ roles expected, we fear >this approach would be too slow, and there is a maintenance issue (which we >could get around if we could assign triggers to system tables, but that's a >tangent I won't get into here). > >B) Use a SYSTEM call to echo the "SET ROLE specific" SPL syntax into a file, >then executing "CREATE PROCEDURE FROM" the filename (untested, but the >documentation implies it'll work), then EXECUTE and DROP the procedure. >This'll almost certainly be slower than above, and there are concurrency >issues to deal with. > >Anyone have a brighter idea? I suspect SPL is the only solution; coding >anything but the execute procedure into the front-end apps would be resisted. >Other SYSTEM calls solutions won't work because they would span a new DB >session and thus the role would die along with that brief session. > >-- >Dennis J. Pimple >Principal Consultant >Informix Software Inc. > >-----== Posted via Deja News, The Leader in Internet Discussion ==----- >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)