on 05-06-2014 1:00 AM
Hello,
we have a PowerBuilder application and want to add a feature where the user can run queries to extract spreadsheets/txt files. The required SQL is stored in a text field in the database. With a simple Select I generate a dynamic datawindow and all is good. Some of the queries though are large compound statements and I put these into a file and call dbisql to execute the file, which works very nicely....... In my development environment So far we haven't deployed dbisql with the application. We do put out dear old dbisqlc but that doesn't do the OUTPUT statement we need.
The solution would seem to be to deploy dbisql which is fine. Unfortunately we have some users who will notice and of course they'll be able to login to the database with dbisql using their application login and potentially cause all sorts of havoc. Is there any way we can restrict the people who can use dbisql on our client installations?
Failing that can anyone think of an alternative to execute a series of SQL commands stored in a string that is able to output a text file, all done from within an application (PowerBuilder in our case)?
TIA, Paul
W.r.t. your idea to restrict users from accessing the database via DBISQLC you might want to try the following:
Create a login script that parses the connection property APPINFO. If that contains DBISQL and the user isn't authorized to use ISQL, signal INVALID_LOGON to quit the connection.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
EXECUTE IMMEDIATE is your friend...
string ls_sql
ls_sql = "BEGIN " &
+ "DECLARE s VARCHAR ( 10 ); " &
+ "SET s = 'Hello'; " &
+ "MESSAGE s TO CONSOLE; " &
+ "END; " &
+ "MESSAGE 'Goodbye' TO CONSOLE;"
EXECUTE IMMEDIATE :ls_sql USING SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
MessageBox ( 'Error', &
'EXECUTE IMMEDIATE failed in open:' &
+ '~r~nSQLCode = ' &
+ String ( SQLCA.SQLCode ) &
+ '~r~nSQLDBCode = ' &
+ String ( SQLCA.SQLDBCode ) &
+ '~r~n' &
+ SQLCA.SQLErrText )
RETURN
END IF
MessageBox ( 'EXECUTE IMMEDIATE', 'OK' );
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Breck,
I looked at EXECUTE in all it forms (shudder!) but the difficulty of getting the result set into an xls/txt is a bit of a deal breaker. Sure, I guess it can be done with lots of coding but it's very difficult to keep it truly dynamic. It's much easier to throw the string into a file and have dbisql execute that, with an OUTPUT tacked on the end. Being simple, I like to keep it stupid.
Thanks, Paul
To avoid shipping DBISQL, you could try using UNLOAD SELECT ... INTO CLIENT FILE directly from the server instead:
--Jason
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Jason,
if it's just a select that's required I'm building a dynamic datawindow and saving the data from there. The problem is the more complex queries that need variables and temporary tables and all the good stuff. These are stored in a text column that is read (by PB) and written to a file that is executed by DBISQL. Thank goodness I found the 5.5 documentation for DBISQLC
I guess I could use views and stored procs to get stuff for the queries and unload select from those but that would mean deployment of DDL and all the testing/QA/paperwork that goes with a program release. Using this technique I can just save the query in to the table and it's ready to go. This also helps when I throw in that the queries need to be replicated (with SQL Remote) across 5 databases.
Paul
It's OK. I did a serious quantity of Googling and found out how to get dbisqlc to work. I guess the security hole is still there but at least we won't be advertising the fact with a bunch of Java directories.
Sigh! Wasn't it great when every piece of software you install didn't have to bring with it the version of Java or dotNet that it needs. Sorry, tired old programmer having a whine.
Paul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.