PDA

View Full Version : Execute SQL during install



mikeyfev1
12-01-2004, 05:25 PM
Hello,

Is there any way to execute an SQL statement during install and utilize the resultant output?

What if I want to execute an SQL select statement, catch the output and populate a drop-down list in a dialog box with the results? I see all the great SQL support, but I don't see any way to utilize the output from SQL. :(

Perucho
12-09-2004, 12:54 PM
Run/Execute SQL commands or scripts?

There are a few ways you can do this, the decision is up to you, like if you have a few sql commands to run, or if you have several sql commands to run (you can put them in a sql script file), or if you have a sql script...

You can use ADO (ADODB.Connection) in a function, create a string with SQL commands and execute it, and read the output with ADODB.Recordset

Another way, you can read-in your sql script file in a function, parse the string, and execute one sql command at a time using ADO.


If you have a SQL script *.sql or want to use a SQL script, you can use osql.exe or isql.exe, and call LAAW (LaunchAppAndWait()) with the correct arguments; then read the output file.

try this with the command prompt (CMD.EXE):
osql.exe /U username /P password /S server /d database /i sqlscript.sql /o sqlscript.output

It is up to you.


Also, you can use SQL-DMO, if you want to CREATE DATABASE, get a list of SQL Servers, get a list of SQL Databases from a SQL Server, and much more...


The SQLServer object contains the objects and collections that implement SQL Server administrative tasks for SQL-DMO. The object allows SQL-DMO applications to connect to a server running SQL Server by name, establishing the context for administrative tasks.
With the SQLServer object, you can:
- Connect to an existing server running SQL Server.
- Query a server running SQL Server to determine its installed configuration and run-time parameters.
- Add and remove SQL Server objects, such as backup devices, databases, and logins.
- Execute Transact-SQL or operating system commands on the server.
- Disable processes on a server running SQL Server.
- Trap SQL Server events and SQLServer object events, providing status information to SQL-DMO application users or debugging information to SQL-DMO application developers.


SQL-DMO
using SQL-DMO
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_8eun.asp


Getting a list of SQL Servers...
Getting a list of Databases from a SQL Server...
http://community.installshield.com/showthread.php?t=133990


See these posts for sample InstallShield scripts using ADO and SQL-DMO:

Creating SQL Database...
Attaching SQL Database...
http://community.installshield.com/showthread.php?t=136105

Run Sql Script while Installing...
http://community.installshield.com/showthread.php?t=130291

Using ADODB.Recordset example...
http://community.installshield.com/showthread.php?t=135822




SQL-DMO
SQLDMO.object
SQLServer Object (SQL-DMO)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/9_dmor19_5.htm

OBJECT dmoServer;
set dmoServer = CreateObject ("SQLDMO.SQLServer");
.
.
.



Using ADO
ADODB.Connection

OBJECT oConnection
set oConnection = CreateObject("ADODB.Connection");
.
.
.


using osql.exe and/or isql.exe
these are command line tools
you can create commands and put them in a batch file to execute.
the batch file can be generated using InstallScript code.

CMD.EXE /K osql.exe /?
CMD.EXE /K isql.exe /?


MSDE
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
look for these tools and/or libraries:
isql.exe
osql.exe
sqldmo.dll




Start SQL Server
http://community.installshield.com/showpost.php?p=300979&postcount=21
http://community.installshield.com/showpost.php?p=315008&postcount=22




Microsoft SQL Server 7.0 (Book Online)
http://www.schemamania.org/jkl/booksonline/SQLBOL70/

mikeyfev1
01-12-2005, 05:58 PM
The SQLDMO technique only works if the user already has SQLDMO installed, right? Same for the OSQL command; only if they have already installed something else that uses it.

So that leaves me with ADO, right?

Somebody
01-12-2005, 07:05 PM
I use ADO in a couple of installs.

Sa'di Tahboub
02-16-2005, 03:35 AM
Hi All,

Actually i want to enumerate the SQL Server Instances and then create a Database in a specific selected instance.

I Read Perucho comments about using the SQL-DMO, but i want to know what should i do to let my machine working with it, consider i don't have a SQL Server installed.

Regards.

Perucho
02-16-2005, 11:17 AM
AFAIK, in order to use SQL-DMO or the command-line tools OSQL.EXE/ISQL.EXE, you need to have MSDE installed or MSSQL Server (which comes with MSDE) installed on the target machine.

If you don't have "Microsoft SQL Server" installed on the target machine, then you need to install MSDE.
You can use InstallShield to install MSDE using the objects modules:
- MSDE 1.0
- MSDE 2000 Object

MSDE (Microsoft SQL Server Desktop Engine)

Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)
http://www.microsoft.com/downloads/details.aspx?FamilyID=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en

If you are working with MSSQL Server 7.0 then you need MSDE 1.0
If you are working with MSSQL Server 2000 (v8.0) then you need MSDE 2000

Microsoft SQL Server 7.0 : Microsoft Data Engine (MSDE 1.0)
Microsoft SQL Server 2000 : Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)

Search the forums for "MSDE" and find out how to get MSDE installed using InstallShield.


Creating SQL Database...
Attaching SQL Database...
http://community.installshield.com/showthread.php?t=136105

code for creating a new database:
http://community.installshield.com/showpost.php?p=285435&postcount=9
note that SQL-DMO is being used to create a new database
"SQLDMO.SQLServer"

code for attaching a database:
http://community.installshield.com/showpost.php?p=288448&postcount=17
note that ADO is being used to attach database
"ADODB.Connection"

I believe to use ADO you don't need MSDE.

ADO Tutorial: http://www.w3schools.com/ado/default.asp

What is ADO?
- ADO is a Microsoft technology
- ADO stands for ActiveX Data Objects
- ADO is a Microsoft Active-X component
- ADO is automatically installed with Microsoft IIS
- ADO is a programming interface to access data in a database

Perucho
02-16-2005, 02:56 PM
URL : http://community.installshield.com/archive/index.php?t-105407.html





export prototype AttachDatabase (STRING ,STRING ,STRING ,STRING, STRING);
export prototype DetachDatabase(STRING, STRING, STRING, STRING);
export prototype StartSQLServer(STRING, STRING, STRING);
export prototype CreateDataBase(STRING, STRING, STRING, STRING, STRING);
export prototype ExecuteSQLScript(STRING, STRING, STRING, STRING, STRING, STRING);


//--------------------------------------------------------------------------------//
function ExecuteSQLScript(szServer, szUser, szPassword, szDBName, szDirFileName, szFileName)

STRING szConnString, szSQLCommand, szTemp;

OBJECT oSQLServer, oADOConnection, oADOCommand;

NUMBER nFuncResult, nFileHandle, nLength;

begin

try

set oSQLServer = CreateObject("SQLDMO.SQLServer");

catch

return FAILED_CREATE_SQLDMO;

endcatch;

try

oSQLServer.Start( TRUE, szServer, szUser, szPassword);

catch

oSQLServer.Connect(szServer, szUser, szPassword);

endcatch;

//prepara a Connection String
szConnString = "driver={SQL Server};";

szConnString = szConnString + "server=" + szServer + ";";

szConnString = szConnString + "Uid=" + szUser + ";";

szConnString = szConnString + "pwd=" + szPassword + ";";

szConnString = szConnString + "database=" + szDBName;

//cria ADO Connection
set oADOConnection = CreateObject("ADODB.Connection");

oADOConnection.Open(szConnString);

//cria o ADO Command para executar o script

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

oADOCommand.ActiveConnection = oADOConnection;

OpenFileMode (FILE_MODE_NORMAL);

// Open the file for editing.
OpenFile (nFileHandle, szDirFileName, szFileName);

szSQLCommand = "";

nFuncResult = -1;
// Get lines from the file into the list.
while (GetLine (nFileHandle, szTemp) = 0)

if nFuncResult < 0 then szSQLCommand = "";

endif;

szSQLCommand = szSQLCommand + " " + szTemp;

nLength = StrLengthChars(szTemp);

if (nLength != 0) then

if szTemp[nLength - 1] = ";" then

oADOCommand.CommandText = szSQLCommand;

try
oADOCommand.Execute;
catch
MessageBox("Error creating database.\n Erro executing SQL scripts.", SEVERE);
endcatch;

nFuncResult = -1;

else nFuncResult = 1;

endif;

endif;

endwhile;

// Close the file.
CloseFile (nFileHandle);

end;
//--------------------------------------------------------------------------------//




//--------------------------------------------------------------------------------//
function CreateDataBase(szServer, szUser, szPassword, szDBName, szFileName)

OBJECT oSQLServer, oDBFile, oDatabase, oFileGroup, oRegistry;

begin

try

set oSQLServer = CreateObject("SQLDMO.SQLServer");

catch

return FAILED_CREATE_SQLDMO;

endcatch;

try

oSQLServer.Start( TRUE, szServer, szUser, szPassword);

catch

oSQLServer.Connect(szServer, szUser, szPassword);

endcatch;

set oRegistry = oSQLServer.Registry;

set oDatabase = CreateObject("SQLDMO.Database");

oDatabase.Name = szDBName;

set oDBFile = CreateObject("SQLDMO.DBFile");

oDBFile.Name = szFileName;

oDBFile.PhysicalName = oRegistry.SQLDataRoot + "\\Data\\" + szFileName + ".mdf";

oDBFile.PrimaryFile = TRUE;

oDatabase.FileGroups("PRIMARY").DBFiles.Add (oDBFile);

try
oSQLServer.Databases.Add (oDatabase);
catch
MessageBox("Error creating database", SEVERE);
endcatch;

end;
//--------------------------------------------------------------------------------//



//--------------------------------------------------------------------------------//
function AttachDatabase(szServer, szUser, szPassword, szDBName, szDBFileName)

OBJECT oSQLServer;

begin

try

set oSQLServer = CreateObject("SQLDMO.SQLServer");

catch

return FAILED_CREATE_SQLDMO;

endcatch;

try

oSQLServer.Start( TRUE, szServer, szUser, szPassword);

catch

oSQLServer.Connect(szServer, szUser, szPassword);

endcatch;

try

oSQLServer.AttachDBWithSingleFile( szDBName , szDBFileName );

catch

return FAILED_TO_ATTACH;

endcatch;

oSQLServer = NOTHING;

return SUCCESS;

end;
//--------------------------------------------------------------------------------//



//--------------------------------------------------------------------------------//
function DetachDatabase(szServer, szUser, szPassword, szDBName)

OBJECT oSQLServer;

begin

try

set oSQLServer = CreateObject("SQLDMO.SQLServer");

catch

return FAILED_CREATE_SQLDMO;

endcatch;

try

oSQLServer.Start( TRUE, szServer, szUser, szPassword);

catch

oSQLServer.Connect(szServer, szUser, szPassword);

endcatch;

try

oSQLServer.DetachDB( szDBName , TRUE );

catch

return FAILED_TO_DETACH;

endcatch;

oSQLServer = NOTHING;

return SUCCESS;

end;
//--------------------------------------------------------------------------------//




//--------------------------------------------------------------------------------//
function StartSQLServer(szServer, szUser, szPassword)

OBJECT oSQLServer;

begin

try

set oSQLServer = CreateObject("SQLDMO.SQLServer");

catch

return FAILED_CREATE_SQLDMO;

endcatch;

try

oSQLServer.Name = "(local)";

oSQLServer.Start( FALSE);

catch

endcatch;

return SUCCESS;

end;
//--------------------------------------------------------------------------------//