Installscript execute SQL script and return values

[hrushikesh1600]

Hi all,

I have an Installscript MSI project that requires me to connect to a (possibly) remote DB engine and figure out the version number before proceeding. I am using the SqlServerSelectLogin2(...) dialog to get the credentials from the user and then using SQLRTTestConnection2(...) to test the authenticity. Once the credentials provided by the user pass, I need to verify that the SQL version is 2008 R2 or above (and not express). If the DB engine was local, I would use the registry route to figure out the version number and that works fine. What I am stumped with is how to detect the version for a remote DB engine. I know I need to run the "SELECT @@VERSION" query using the SQL scripts view but I am at a loss on how to pass data back to the Installscript to take the next steps. Upon searching briefly, I have seen that this is a problem that has come up before and is unsolved (http://community.flexerasoftware.com/showthread.php?163524-How-to-determine-SQL-Server-Version-of-a-remote-machine).

I also tried using the hint provided in the given link to use SQLCMD.exe to execute commands but that poses two problems - the utility is a part of the client tools so the end user is not guaranteed to have it, and writing the results to file will result in a file being generated respective to the server machine, not the client. Because of this, i have come to the conclusion that the only feasible way is to go via the SQL scripts view. My questions are:

1) How to execute a specific SQL script file using installscript (there are multiple files under one component and I would need to execute just one to figure out the version)
2) How to pass data back to the Installscript? Can the properties somehow be used?

Thanks!
Rishi

[ch_eng]

Rishi,

Perhaps using ADO via InstallScript will help:

http://community.flexerasoftware.com/showthread.php?152884-Checking-Database-version-in-SQL


HTH

[hrushikesh1600]

Hi ch_eng

Thanks! that link is very helpful. My apologies for only just now getting back to this but requirements changed and I got sidetracked...you know how it is 🙂

Anyway, I followed the instructions and setup the following script file but I am unable to get it to run. I get an error during runtime on the line marked. The error is from the SQL data provider and the error code is a long 7 digit number that I have no clue about. Wieirdly enough, the error happens on a CreateObject call which boggles me. I have created a VB script with identical code and that executes just fine. Installscript for some reason has problems with the ADO DB provider. Any help in resolving this would be appreciated, this is getting critical now.

	//First initialize SQL Server runtime
	SQLRTInitialize2();
	
	sConnString = "Provider=sqloledb;";
	sConnString = sConnString + "server=(local);";
	sConnString = sConnString + "uid=" + user + ";";
	sConnString = sConnString + "pwd=" + pass + ";";
	sConnString = sConnString + "database=master"; 

	sSQL = :SELECT * FROM sys.server_principals WHERE name='Rishi'";

	try
	// Create ADO Connection Object to connect to the SQL server 
	set pADOConnObj = CreateObject("ADODB.Connection");
	set rs = CreateObject("ADODB.Command");                        //THIS LINE IS WHERE THE ERROR OCCURS, EVEN BEFORE I ESTABLISH A CONNECTION
        
        pADOConnObj.ConnectionString = sConnString;
	pADOConnObj.Open(); 	
	
        pADOConnObj.ActiveConnection = sConnString;
        pADOConnObj.CommandText = sSQL;
        pADOConnObj.execute(); 

	catch
	NumToStr(szDB, Err.Number);
	MessageBox(Err.Number + " Source: " + Err.Source + " Desc: " + szDB, WARNING);
	endcatch;
end;

ch_eng wrote:
Rishi,

Perhaps using ADO via InstallScript will help:

http://community.flexerasoftware.com/showthread.php?152884-Checking-Database-version-in-SQL


HTH

[ch_eng]

Rishi,

It looks like there is a little mix-up in the code. What is the 7-digit error you get? Try something like this:

...snip...

set pADOConnObj = CoCreateObject( "ADODB.Connection" );
pADOConnObj.ConnectionString = sConnString;
pADOConnObj.Open;

set rs = CreateObject("ADODB.Command");                        //THIS LINE IS WHERE THE ERROR OCCURS, EVEN BEFORE I ESTABLISH A CONNECTION
rs.ActiveConnection = pADOConnObj;
rs.CommandText = sSQL;
rs.Execute();

...snip...

HTH

[hrushikesh1600]

You, sir, are a life saver. And apparently I am blind. The problem I think stemmed from the fact that I had pADOConnObj.Open(). It didn't like the brackets but wouldn't throw a friendly warning. Seems to work now. Many thanks!

Rishi

ch_eng wrote:
Rishi,

It looks like there is a little mix-up in the code. What is the 7-digit error you get? Try something like this:

...snip...

set pADOConnObj = CoCreateObject( "ADODB.Connection" );
pADOConnObj.ConnectionString = sConnString;
pADOConnObj.Open;

set rs = CreateObject("ADODB.Command");                        //THIS LINE IS WHERE THE ERROR OCCURS, EVEN BEFORE I ESTABLISH A CONNECTION
rs.ActiveConnection = pADOConnObj;
rs.CommandText = sSQL;
rs.Execute();

...snip...

HTH

[hrushikesh1600]

Alright. I feel stupid bringing this back to life but I tried something else and I'm getting failures again.

What I did was abstract out this SQL code into a function so I could re-use it to write many a queries. That was the intention, at least. This is the function:

function BOOL RunSqlNonQuery(szServer, szDb, szUsername, szPassword, szQuery)
	string sConnString;
	OBJECT AdoConnectionObj, AdoCommandObj;
begin
	//Create the connection string
	sConnString = "driver={SQL Server};";
	sConnString = sConnString + "server=" + szServer + ";";
	sConnString = sConnString + "uid=" + szUsername + ";";
	sConnString = sConnString + "pwd=" + szPassword + ";";
	sConnString = sConnString + "database=" + szDb;
	
	try
		// Create ADO Connection Object to connect to the SQL server 		
		set AdoConnectionObj = CoCreateObject( "ADODB.Connection" );
		AdoConnectionObj.ConnectionString = sConnString;
		AdoConnectionObj.Open;

		set AdoCommandObj = CreateObject("ADODB.Command");
		AdoCommandObj.ActiveConnection = AdoConnectionObj;
		AdoCommandObj.CommandText = szQuery;
		AdoCommandObj.Execute();
	catch
		MessageBox(Err.Number + " " + Err.Description, MB_OK);
		return FALSE;
	endcatch;
	return TRUE;
end;

I tried to debug a simple query to begin with, something like "SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'Core'" and I don't even get an error code this time! The control goes haywire after the "CreateObject(ADODB.Command)" line and I it skips the remainder of the function from being debugged! I have attached a screenshot of how the debugger looks after the error happens. At this point, I am planning to give up on using SQL through Installscript and instead try a different approach.

So, my questions are: 1) What in seven hells am I doing wrong? (*snark snark*) 2) If I wanna run a VB script custom action, is there a way to call the custom action from installscript? As far as I know, custom actions can only be scheduled to run, not called on demand.

Please help! 😞

[ch_eng]

Rishi,

Putting that code in a reusable function is a great idea - don't give up!

EDIT: from your screenshot; it looks like some of your variables may be out-of-order (ex: I'm guessing "(local)" should be szServer, not szUsername)

I don't actually use this syntax, so I'm not sure what the problem is with that particular line:

set AdoCommandObj = CreateObject("ADODB.Command");

Instead of this

set AdoCommandObj = CreateObject("ADODB.Command");
AdoCommandObj.ActiveConnection = AdoConnectionObj;
AdoCommandObj.CommandText = szQuery;
AdoCommandObj.Execute();

Try this

set AdoCommandObj = AdoConnectionObj.Execute( szQuery );
AdoConnectionObj.Close;
AdoConnectionObj = NOTHING;

HTH

[hrushikesh1600]

Hi ch_eng,

Thanks for such a prompt response! I tried your code snippet and I still get the exact same behavior with the green arrows in the left during debug. If it helps, you are right about my variables being out of date - turns out the debugger was in a messed up state. I verified all the variables during this run and the connection string shows up as :

 driver={SQL Server};server=(local);uid=sa;pwd=Adc5dkd3v;database=master

Everything is good until the

set AdoCommandObj ... 

line. It fails on your code snippet as well as mine.

Any idea as to what those green arrows even mean? Could I be missing #includes or something in my PATH?

Thanks, again!
Rishi

[ch_eng]

Rishi,

I'm not sure what the green arrows mean in the debugger. My projects are InstallScript Only, so I'm not sure how much of the following applies, but may be worth trying?

As far as #includes go, in the dialog code where I call SQL, it looks like this. The "original file" referred to in the comments is InstallShield's code for SQLServerSelectLoginEx2.

#include "ISRT.h"
//#include "ISRTPriv.h"	// this is included in the original file but breaks when included in the custom file
#ifdef SQLRT_OBL
#include "SQLRT.h"
#elif SQLCONV_OBL
#include "SQLConv.h"
#endif

and near the top of "begin" for the ShowDialog function:

    if (!g_bSQLRTInitialized) then
    	//this fails the first time because of trying to customize this dialog? (because can't include ISRTPriv.h ?)
        //return ISERR_GEN_FAILURE;
    else
    	//(re)run the required SQLRTInitialize2 function and rerun the test
    	SQLRTInitialize2();
	    if (!g_bSQLRTInitialized) then
	        return ISERR_GEN_FAILURE;
	    endif;
    endif;

    // ensure general initialization is complete
    if (!bSdInit) then
      SdInit();
    endif;

HTH

[hrushikesh1600]

Sigh. Still no dice. I had the "SQLRTInitialize2();" done elsewhere before but just for sanity I did it again at the start of this function. That brought about no change.

Secondly, I don't know what all of your includes files do but I tried them all, one by one. I cannot include the "SQLRT.h" file because I get errors about duplicate definitions which tells me it's already included inconspicuously. The "SQLRTConv.h" file cannot be found and the "ISRT.h" file does not show any changes in behavior. In my build path, though, I do include "SQLRTConv.obl" library.

I am running an Installscript MSI project but I don't know how behavior between that and a pure Installscript project would be this drastic! Any more pointers?

I appreciate your help!
Rishi

[ch_eng]

Rishi,

Do you have any connections created in the "SQL Scripts" section of "Server Configuration" ? I vaguely remember needing to have one created (even if not used) so InstallShield will add some required behind-the-scenes stuff.




HTH

[hrushikesh1600]

Yup, I have three, in fact. And I can verify that I am able to successfully authenticate to those connections before running the SELECT script that I am trying to run.

EDIT: I see you have a script to create a SQL user? Would you mind sharing some pointers on how you're using that? Like what dialog do you use to get the credentials for the admin user who will create the additional user and how you invoke this script later?

Thanks!

[ch_eng]

Rishi,

I remembered that I do have one InstallScript MSI project that uses a SQL database connection. At the top of the .rul script for every dialog that uses the SQL connection, it has this block for the #includes (same as above):

#include "ISRT.h"
//#include "ISRTPriv.h"	// this is included in the original file but breaks when included in the custom file

#ifdef SQLRT_OBL
#include "SQLRT.h"
#elif SQLCONV_OBL
#include "SQLConv.h"
#endif

and near the top of the "begin" section of every .rul script in the ShowDialog function, it has this block (same as above):

    if (!g_bSQLRTInitialized) then
    	//this fails the first time because of trying to customize this dialog? (because can't include ISRTPriv.h ?)
        //return ISERR_GEN_FAILURE;
    else
    	//(re)run the required SQLRTInitialize2 function and rerun the test
    	SQLRTInitialize2();
	    if (!g_bSQLRTInitialized) then
	        return ISERR_GEN_FAILURE;
	    endif;
    endif;

    // ensure general initialization is complete
    if (!bSdInit) then
      SdInit();
    endif;

This is a simplified version of what happens when I connect via ADODB via InstallScript to get a value returned. This assumes the query is in the form "SELECT something AS Value FROM sometable".

set objADODBConnection = CoCreateObject( "ADODB.Connection" );
objADODBConnection.ConnectionString = szADODBConnectionString;
objADODBConnection.Open;

set objADODBCommand = CreateObject( "ADODB.Command" );
objADODBCommand.ActiveConnection = objADODBConnection;
objADODBCommand.CommandText = szSQLCode;
set objReader = objADODBCommand.Execute();
if !objReader.EOF then
    svResult = objReader.Fields( "Value" ).Value;
endif;
objReader.Close();

Other than that, the only thing I can suggest is to create a new blank InstallScript MSI project and try just adding the SQL functionality to it. It's been a long time since I've setup a new project with SQL connectivity, but I don't remember offhand having to manually do something like this: "In my build path, though, I do include "SQLRTConv.obl" library."

HTH and good luck!

[hrushikesh1600]

Will do, my man! Thanks for all your help.

Rishi