johnfastslow1.55249277948135E12 asked a question.
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;
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.
i don't recommend SQL built-in support inside script because you can't control it so easily.