Use Credential Manager to host SQL authentication

Thanks a lot rickard.norin 

If the stored procedure sends back a list of responses like an array, how do we achieve this into Intouch ? In System Platform we can directly put into an array by defining an attribute, in intouch i dont see array as a variable

Depends on what the acquired data will be used for.

You are correct that InTouch doesn't have array-typed tags, but there's several things one may do with an array, data set, image, XML document or any other thing that may come out of a database. 

• Populate a listbox, datagrid, or chart control with the data
• Extract bits a pieces of the data and use for visualization and animation
• Parse individual scalar items from the data, then assign these to scalar value tags.

Hi Rickard, in the above 3 instances that you have mentioned, how can we achieve this with Intouch ? I think the 3 you mentioned are the most popular things I do from SQL. 

If you put the logic for connecting to a SQL Server in a Industrial Graphic Symbol, then you can use the above mentioned SQL Script Library, there you can manage the result and use it as seen proper. 

This requires the symbol to be open to function.

If it is background functionality or that you can not use a Industrial Graphic Symbol then you need to use native InTouch SQL Function to connect and use tags for storing the data.

So in some cases a workaround would be to have 'Empty' a symbol in the header that is always open, that manages all connections and result.

Best solution for you is depending on what you are trying to achieve.

But if the target is to populate a user interface with data, such a listbox, datagrid, other controls etc. then you can for sure use the SQL Script library in your symbol, 

Thanks a lot Richard Köhlerstrand , you're a diamond 

Here is an example to start with, but as mentioned earlier, if your query takes time to finalize, then you need to start manage the connection Async, (let the script poll for completion and capture the result once it is completed) and don't forget your Command.Dispose(); !

doing so will generate a memory leak over time.

dim Connection 	as aaDBClient.aaDBConnection;
dim Command    	as aaDBClient.aaDBCommand;
'dim Transaction as aaDBClient.aaDBTransaction;
'Create a connection object with the connection string.
LogMessage("Creating connection");
'Connection = aaDBAccess.CreateConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=true");
Connection = aaDBAccess.GetConnection("server=localhost;database=LABDB;Trusted_Connection=yes", aaDBConnectionType.Sql);
'Create a command object for the transaction object, with a SQL statement or Stored Procedure.
LogMessage("Creating a command object");
'Command = Connection.CreateCommand("Select Column1 from ...", aaDBCommandType.SqlStatement, TRUE);
Command = Connection.CreateCommand("GetData", aaDBCommandType.StoredProcedure, TRUE);
Command.SetIntParameterByName("@wait", QueryTime, 	aaDBParameterDirection.Input);
Command.SetIntParameterByName("@rc", RowCount, 	aaDBParameterDirection.Input);
Command.SetBitParameterByName("@DoError", DOError, 	aaDBParameterDirection.Input);

'Everything is ready, let's execute the transaction sync.
LogMessage("Executing transaction sync");
dim ResultCode as integer;
ResultCode = Command.ExecuteSync();
If ResultCode <> 0 Then  
	'Failed to execute transaction sync, report the reason.
	LogMessage("Got error " + ResultCode + " executing transaction sync");
Else
	If Command.ExecutionState == aaDBTransactionState.Failed Then
			Catcherror = Command.LastExecutionError;
			LogWarning("Execution error: "+Command.LastExecutionError);
			'Possibility to retry or manage error here
	elseif Command.ExecutionState == aaDBTransactionState.Completed Then
		dim Rows as integer;
		Rows = Command.RowCount;
		LogMessage("Row count returned from command is " + Rows);
		'Use other methods of script library to read data and assign to UDAs, etc.
		if Rows > 0 then
			'Manage result from query, (single row)
			dim Column1 as string; 
			Column1 = Command.GetCurrentRowColumnByName("Column1"); 
			LogMessage("Got data "+Column1);
			Sync_Column1 = Column1;
		endif;
		if Rows > 0 then
			'Manage result from query, (loop rows), 
			'in this case the SP returns two datasets, and the second one (1) has to be selected
			Command.SelectTable(1);
			'dim dt as System.Data.DataTable;
			'DataGridView1.Datasource = Command.GetDataset().Tables(1);
			LogMessage("rows found: "+Command.RowCount());
			dim j as integer;
			for j = 0 to Command.RowCount-1
				Command.SelectRow(j);
				ComboBox1.AddItem(Command.GetCurrentRowColumnByName("Rows"));
			next;
		endif;
	EndIf;
EndIf;

Command.Dispose();

Sync_GetData = false;

Thanks a lot ! I would like to see as a feature the Credentials Manager be able to use for scripting such as web browser authentication. Also Richard, I'm planning to create an Intouch backup where I would implement a Gold Class Standard of everything ( graphics, scripting, efficient methods ) from an old Intouch project upgrade. Would you by any chance have a public project that I can use in Intouch ?