Write batch results to external SQL database

[Transistor]

I want to send a batch report to my SQL server which will be used to keep history of all batches.

On previous e910 applications I created a text block with the fieldnames and values on it and emailed it to the SQL server where hMailServer caught it and posted the record into the database using ODBC connection.

I can script an email in my new iX developer application but I'd rather write directly to the database. Can anyone think of a way of doing this? I presume I can't add an ODBC connection to the iX panel.

[JohnCZ]

Hi,

what type of databse you have Microsoft,MySQL,Oracle ??

what type of iX you used ( HMI or runtime app ) ??

BR
JohnCz

[Transistor]

Hi, John.

SQL Express, 2013 or 2015 - I can't remember which.
HMI T12B

The only way I could get it to work was to send an email report to my server and have hMailServer running on it, parse the email and stuff the results into the database. I'd be interested in any other tricks.

[JohnCZ]

Hi,

I will send you to your pm how it's possible to connect to your Microsoft SQL from TxA panel.

BR
JohnCZ

[liestol]

You can do this by importing the System.Data.SqlClient.dll assembly.

You can then do something like this in a script;

Code: Select all

	public void DoSQLWrite(string strEntry, string strDatasource,string strInitialCatalog, string UserName, string Password)
	{
			
		SqlConnection myWConnection;		

	
		try
		{
			myWConnection = new SqlConnection("Data Source=" + strDatasource + ";Initial Catalog=" + strInitialCatalog + ";User Id=" + UserName + ";Password=" + Password);
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);               
			return;
		}

		try
		{
			myWConnection.Open();
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message);
			return;
		}

		try
		{
				
				SqlCommand myWriter = new SqlCommand(strEntry, myWConnection); 
				SqlParameter exParam1 = new SqlParameter("@param1", SqlDbType.Int);
				SqlParameter exParam2 = new SqlParameter("@param2", SqlDbType.Int);
				SqlParameter exParam3 = new SqlParameter("@param3", SqlDbType.Decimal);
				exParam1.Value = Globals.Tags.tag1.Value;
				exParam2.Value = Globals.Tags.tag2.Value;
				exParam3.Value = Globals.Tags.tag3.Value;
				myWriter.Parameters.Add(exParam1);
				myWriter.Parameters.Add(exParam2);
				myWriter.Parameters.Add(exParam3);
		
			myWriter.ExecuteNonQuery();

         myWConnection.Close();
		}
		catch (Exception ex)
		{
			MessageBox.Show(ex.Message); 
			myWConnection.Close();
		}
}

You can then call the function like this (on value change, timer etc):

Code: Select all

Globals.Sql.DoSQLWrite("INSERT INTO db (Column1, Column2, Column3) VALUES (@exParam1, @exParam2, @exParam3)", "localhost\\NAMEDPIPE", "DB", "user", "pwd");

This is just a quick mockup, and the code can absolutely be optimised in a lot of ways (especially with a lot of tags, I would suggest handling the creation of parameters etc in a loop). You can also read stuff from the db and put it in tags with SqlDataReader. This only works against MS SQL (any edition, I've run it smoothly with SQL Server Express).