Welcome Guest ( Login | Register )



All times are UTC - 7 hours [ DST ]



Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: Write batch results to external SQL database
PostPosted: Sun Nov 01, 2015 1:06 pm 

Joined: Sat Jul 04, 2015 6:13 am
Posts: 25

Offline
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.


 Profile  
 
 Post subject: Re: Write batch results to external SQL database
PostPosted: Thu Feb 04, 2016 5:31 am 

Joined: Wed Jun 27, 2012 1:17 am
Posts: 73
Location: CZ

Offline
Hi,

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

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

BR
JohnCz


 Profile  
 
 Post subject: Re: Write batch results to external SQL database
PostPosted: Thu Feb 04, 2016 5:35 pm 

Joined: Sat Jul 04, 2015 6:13 am
Posts: 25

Offline
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.


 Profile  
 
 Post subject: Re: Write batch results to external SQL database
PostPosted: Mon Feb 08, 2016 5:03 am 

Joined: Wed Jun 27, 2012 1:17 am
Posts: 73
Location: CZ

Offline
Hi,

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

BR
JohnCZ


 Profile  
 
 Post subject: Re: Write batch results to external SQL database
PostPosted: Thu Feb 11, 2016 5:29 am 

Joined: Thu Feb 11, 2016 5:11 am
Posts: 10

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

You can then do something like this in a script;

Code:
   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:
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).


 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 7 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: