cancel
Showing results for 
Search instead for 
Did you mean: 

Deploying and restricting DBISQL

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

reimer_pods
Participant
0 Kudos

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.

-> Login Procedure

Former Member
0 Kudos

Thanks Reimer that works like a charm,

I had to wait until Monday to try it and it does exactly what I need. I even tested it with some of our extracts .

Thanks again, Paul

Answers (3)

Answers (3)

former_member188493
Contributor
0 Kudos

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' );
Former Member
0 Kudos

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

JasonHinsperger
Advisor
Advisor
0 Kudos

To avoid shipping DBISQL, you could try using UNLOAD SELECT ... INTO CLIENT FILE directly from the server instead:

DocCommentXchange

--Jason

Former Member
0 Kudos

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

former_member188493
Contributor
0 Kudos

> all the testing/QA/paperwork that goes with a program release

Got it... no testing required when dbisql is involved.

former_member188493
Contributor
0 Kudos

Either this forum's software had a senior moment, or I did... fifty-fifty on that one.

The point is, the "Got it" post above was intended as a reply to a different post, the "I looked at EXECUTE in all it forms" post.

Of course, who knows where THIS reply will show up.

Former Member
0 Kudos

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