This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- Re: How to Execute SQL query inside InstallScript12?
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Oct 03, 2007
09:45 AM
How to Execute SQL query inside InstallScript12?
i have problem executing sql queries inside installscript
i have to execute something like this:
sEntry = "Insert into [dbo].Account(IDAccount, acName) Values('" + globalVariable1 +"','" + globalVariable2 +"')";
i can connect to SQLServer and find database;
I don't know to use SQLRTComponentInstall();
and this function does not work:
unction BOOL ExecuteSQLScript(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword, svScriptFile, svErrLog)
OBJECT pADOObj, pADOCommObj;
STRING szADOObjID, szADOCommObjID;
STRING svLine, szConnString, szSQL, svString;
STRING szHead, szTail;
NUMBER nResult, nRecords, nBegin, nEnd;
BOOL bExitLoop;
LIST listID;
LIST listCmd;
number nRet;
string szErrorLog;
begin
szErrorLog = "";
nRet = 0;
// Create an empty string list.
listID = ListCreate(STRINGLIST);
listCmd = ListCreate(STRINGLIST);
// Read the SQL script file into the list
if (ListReadFromFile(listID, svScriptFile) < 0) then // read list from file
svErrLog = "Unable to open SQL script: " + svScriptFile + ".";
return -1;
endif;
// Go through each list item and add it to a string (which will then hold the script)
szSQL = "";
nResult = ListGetFirstString(listID, svString);
while (nResult = 0)
if (StrFind(svString, "GO") = 0) then
// Remove comments
bExitLoop = FALSE;
repeat
nBegin = StrFind(szSQL, "/*");
nEnd = StrFind(szSQL, "*/");
if ((nBegin >= 0) && (nBegin < nEnd)) then
StrSub(szHead, szSQL, 0, nBegin);
StrSub(szTail, szSQL, nEnd+2, 65535);
szSQL = szHead + szTail;
else
bExitLoop = TRUE;
endif;
until (bExitLoop);
// Add it to commands
if (StrLengthChars(szSQL) > 0) then
ListAddString(listCmd, szSQL, AFTER);
endif;
szSQL = "";
elseif (StrFind(svString, "--") = 0) then
// It is a comment, so do nothing
else
szSQL = szSQL + "\r\n" + svString;
endif;
nResult = ListGetNextString(listID, svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listID);
// Create ADO Connection Object to connect to the SQL server
szADOObjID = "ADODB.Connection";
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=" + svDatabaseName;
// Open the ADO Connection
pADOObj.Open(szConnString);
// Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOObj;
// Execute each command
nResult = ListGetFirstString(listCmd, svString);
while (nResult = 0)
try
// Execute the call to run the script
pADOCommObj.CommandText = svString;
pADOCommObj.Execute(nRecords ,0x81);
catch
nRet = Err.Number;
szErrorLog = szErrorLog
+ "Error: " + Err.Description + "\n"
+ "Source: " + Err.Source + "\n"
+ "Script:\n" + svString + "\n\n";
endcatch;
nResult = ListGetNextString(listCmd, svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listCmd);
svErrLog = szErrorLog;
return nRet;
end;
i have to execute something like this:
sEntry = "Insert into [dbo].Account(IDAccount, acName) Values('" + globalVariable1 +"','" + globalVariable2 +"')";
i can connect to SQLServer and find database;
I don't know to use SQLRTComponentInstall();
and this function does not work:
unction BOOL ExecuteSQLScript(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword, svScriptFile, svErrLog)
OBJECT pADOObj, pADOCommObj;
STRING szADOObjID, szADOCommObjID;
STRING svLine, szConnString, szSQL, svString;
STRING szHead, szTail;
NUMBER nResult, nRecords, nBegin, nEnd;
BOOL bExitLoop;
LIST listID;
LIST listCmd;
number nRet;
string szErrorLog;
begin
szErrorLog = "";
nRet = 0;
// Create an empty string list.
listID = ListCreate(STRINGLIST);
listCmd = ListCreate(STRINGLIST);
// Read the SQL script file into the list
if (ListReadFromFile(listID, svScriptFile) < 0) then // read list from file
svErrLog = "Unable to open SQL script: " + svScriptFile + ".";
return -1;
endif;
// Go through each list item and add it to a string (which will then hold the script)
szSQL = "";
nResult = ListGetFirstString(listID, svString);
while (nResult = 0)
if (StrFind(svString, "GO") = 0) then
// Remove comments
bExitLoop = FALSE;
repeat
nBegin = StrFind(szSQL, "/*");
nEnd = StrFind(szSQL, "*/");
if ((nBegin >= 0) && (nBegin < nEnd)) then
StrSub(szHead, szSQL, 0, nBegin);
StrSub(szTail, szSQL, nEnd+2, 65535);
szSQL = szHead + szTail;
else
bExitLoop = TRUE;
endif;
until (bExitLoop);
// Add it to commands
if (StrLengthChars(szSQL) > 0) then
ListAddString(listCmd, szSQL, AFTER);
endif;
szSQL = "";
elseif (StrFind(svString, "--") = 0) then
// It is a comment, so do nothing
else
szSQL = szSQL + "\r\n" + svString;
endif;
nResult = ListGetNextString(listID, svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listID);
// Create ADO Connection Object to connect to the SQL server
szADOObjID = "ADODB.Connection";
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=" + svDatabaseName;
// Open the ADO Connection
pADOObj.Open(szConnString);
// Create the ADO Command object to execute the script
szADOCommObjID = "ADODB.Command";
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj.ActiveConnection = pADOObj;
// Execute each command
nResult = ListGetFirstString(listCmd, svString);
while (nResult = 0)
try
// Execute the call to run the script
pADOCommObj.CommandText = svString;
pADOCommObj.Execute(nRecords ,0x81);
catch
nRet = Err.Number;
szErrorLog = szErrorLog
+ "Error: " + Err.Description + "\n"
+ "Source: " + Err.Source + "\n"
+ "Script:\n" + svString + "\n\n";
endcatch;
nResult = ListGetNextString(listCmd, svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listCmd);
svErrLog = szErrorLog;
return nRet;
end;
(2) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Oct 03, 2007
11:09 AM
The SQLRTComponentInstall() function is designed to execute a SQL script specified in the SQL Scripts view. If you write your own code to manage database connections and SQL scripts, the function does not help.
If you would like to get the ExecuteSQLScript() function working, I would suggest you to debug the code and narrow down what is causing your issue.
If you do not need to handle query results from databases inside InstallScript, you can use InstallShield built-in SQL support as a alternate solution. Only you need to do is to add connections and SQL scripts in the SQL Scripts view. InstallShield will automatically display SQLLogin dialogs and run the SQL scripts at run-time.
Hope that helps.
If you would like to get the ExecuteSQLScript() function working, I would suggest you to debug the code and narrow down what is causing your issue.
If you do not need to handle query results from databases inside InstallScript, you can use InstallShield built-in SQL support as a alternate solution. Only you need to do is to add connections and SQL scripts in the SQL Scripts view. InstallShield will automatically display SQLLogin dialogs and run the SQL scripts at run-time.
Hope that helps.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Oct 04, 2007
09:04 AM
Tnx i found a bug inside my executesql function.
i don't recommend SQL built-in support inside script because you can't control it so easily.
i don't recommend SQL built-in support inside script because you can't control it so easily.
![](/skins/images/CC38E504FC9E1309DC48ACFEB4925670/responsive_peak/images/icon_anonymous_message.png)