NI Home > Community > NI Discussion Forums

LabVIEW

Reply
Active Participant
astroboy
Posts: 402
0 Kudos

Unfamilair with ADO DB connections

Hi I am not familiar with ADO DB connection.
I am using Labview 7.1, MySQL and LabSQL.
 
In those LabsQL vi functions, there are no vi descriptions for connections.
I tried finding similar descriptions to compare from ADO websites. I am getting confused.
 
Here are LabSQL vi functions:-
1) ADO Connection Create.vi - means  Create an connection to database?
2) ADO Connection Open.vi - means Open the connection to database?
3) ADO Connection Close.vi - means Close the connection  to database?
4) ADO Connection Destroy.vi - means Destroy the connection to database?
Are ADO Connection Close and Destroy same?
 
And I am not familiar with the sequence of those vi.
For eg. I read  the procedure from http://www.w3schools.com/ado/ado_intro.asp

Accessing a Database from an ASP Page

The common way to access a database from inside an ASP page is to:

  1. Create an ADO connection to a database
  2. Open the database connection
  3. Create an ADO recordset
  4. Open the recordset
  5. Extract the data you need from the recordset
  6. Close the recordset
  7. Close the connection

So when I cannot find LabSQL functions for eg Open recordset and close recordset, so what is the equivalent ADO function to "destroy the connection"?

And here are my attached, and I there's something i do not understand,

And my question for my attached, typing my show processlist in command prompt,

When run attached for the first time, "show processlist" command show no host to "test" db.

when click to open database, "show  processlist" command shows my host  to "test" db.

When click to close database, "show  processlist" command still shows my host  to "test" db.

Why is this so? By right, there should be no sign of "test" db. Do i have to use ADO Connection Destroy vi to destroy connection?

 

Please advise

Thanks

Member
Van_L
Posts: 121
0 Kudos

Re: Unfamilair with ADO DB connections

Hi Astroboy,

I am not too familiar with LabSQL either, but after doing some search on the discussion forum I found som pretty useful threads which can be really helpful to you:

LabSQL Can't make it work
labsql - LabVIEW
labsql step-by-step
Create new table using LABSQL

If you type in LabSQL at NI Discussion Forums you can find more.

I'll look into this also
Van L
NI Applications Engineer
Knight of NI
Posts: 18,064

Re: Unfamilair with ADO DB connections

LabSQL comes with a few examples. The "Fetch a Table" is the basic sequence for getting a recordset and actually answers most of your questions:
  • The "ADO Connection Create" just creates the software object. It does not make a connection.
  • The "ADO Connection Open" actually connects to the database. It calls the "Open" method of the connection object.
  • The "ADO Connection Execute" executes a SQL statement and returns a 2D array of strings which is the table. Internally it creates a recordset object.
  • The "ADO Connection Close" closes the connection to the database. It calls the "Close" method of the connection object.
  • The "ADO Connection Destroy" nulls out the software object (i.e. closes the reference - not the connection) that was created by "ADO Connection Create".
In your VI you have the basic (and correct) sequence for connecting to a database and then disconnecting. I have no idea what you mean by "show processlist". Your LEDs are not going to be accurate because of dataflow. For instance, you enter a loop to wait for a button press, after which you open the connection. The problem is the indicators can be updated before you've actually opened the connection.

I also think you should be using an event structure rather than the multiple loops.

Active Participant
astroboy
Posts: 402
0 Kudos

Re: Unfamilair with ADO DB connections

Hi Van_L and Smercurio_fc,

Thanks for your advices.

Now I undesrtand better.

clement

Active Participant
astroboy
Posts: 402
0 Kudos

Re: Unfamilair with ADO DB connections

Hi Smercurio_fc,
Show processlist is actually a command used in MySQL that will show you the actual connection threads to the various databases.
So that we will know how many actual and duplicated connections we have. Using this, we can determine whether our LV program has actually closed the connection based on the number of open connections, so that it will not lead to error such as "Too many connections" that runs out of the max number of connections as set.
 
Clement
 
Knight of NI
Posts: 18,064
0 Kudos

Re: Unfamilair with ADO DB connections

OK, thanks for the info. Sounds like the MySQL version of SQL Profiler that comes with SQL Server.
Member
EWTech
Posts: 82
0 Kudos

Re: Unfamilair with ADO DB connections

There are other possibilities. I built a dll project in Visual Studio and used the dll Constructor Node and Invoke Node fuctions in LV 8..5.1 to attach to the dll file. It saves to a separate database using ADO.NET rather than the ADO functions built into LabSQL. It requires a bit more effort in some ways but it's very flexible. I'm not sure what there is in LV 7 but these fuctions seem to be in 8.2.1 as well.
Active Participant
astroboy
Posts: 402
0 Kudos

Re: Unfamilair with ADO DB connections

Hi Bob,

 

Can you show me how they can be done? I will try to duplicate them in LV version 7.1

Thanks

 

astroboy

Member
EWTech
Posts: 82
0 Kudos

Re: Unfamilair with ADO DB connections

There are two problems with trying this on LV 7: I used Shared Variables which are not supported on 7 and I am not sure if what I did exists on ADO.Net 1.1. I created a DLL using Visual Studio 2005 and ADO.Net for SQL Server 2005. The DLL code is C#: //VitalData.cs //Items of data class;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; using System; using System.Collections.Generic; using System.Text; namespace SaveData { public class VitalData { public Double Value; private Double Last; public DateTime Time; public String Name; public VitalData(String InName, Double InValue, DateTime InTime) { Name = InName; Value = InValue; Time = InTime; Last = -999999.9; } public override String ToString() { String Temp; Temp = Name + ";" + Value.ToString() + ";" + Time.ToLongTimeString(); return Temp; } public bool SameValue() { if (this.Value == this.Last) return true; else return false; } public void Update(DateTime TIn, Double VIn) { Last = Value; Value = VIn; Time = TIn; } } } //SaveData.cs //::::::::::::Functional class for DLL ::::::::::::::::: using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlTypes; using System.Data.SqlClient; using System.Windows.Forms; using System.IO; namespace SaveData { // Declaration. public class SaveData { String connSQL, strSQL, ValuesIn; List DataList; public SaveData() { DataList = null; connSQL = @"Data Source=PQLSVR2\SQLExpress;" + "Initial Catalog = VITAL;Integrated Security = True;"; DataList = new List(); } public int Add(String Input) { DateTime InTime; int count; VitalData DataLine; InTime = DateTime.Now; String[] Lines; String Sensor; Double Value; ValuesIn = Input; Lines = Input.Split('\n'); foreach (String line in Lines) { { String[] parameters = line.Split(';'); Sensor = parameters[0]; Value = System.Convert.ToDouble(parameters[1]); if (Find(Sensor) == null) { DataLine = new VitalData(Sensor, Value, InTime); DataList.Add(DataLine); } else { DataLine = Find(Sensor); DataLine.Update(InTime, Value); } } } foreach (VitalData element in DataList) { int local; if(!element.SameValue()) try { SqlConnection cn = new SqlConnection(connSQL); strSQL = "Insert INTO dbo.Measurements (DateTime, Value, Channel) " + "VALUES ('" + element.Time.ToShortDateString() + " " + element.Time.ToLongTimeString() + "','" + element.Value.ToString() + "','" + element.Name.ToString() + "')"; cn.Open(); try { SqlDataAdapter da = new SqlDataAdapter(strSQL, connSQL); SqlCommand cmd = new SqlCommand(strSQL, cn); local = cmd.ExecuteNonQuery(); cn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message, "SqlDataAdaptor Failure", MessageBoxButtons.OK, MessageBoxIcon.Error); return 0; } } catch (SqlException ex) { MessageBox.Show(ex.Message, "SQL NonQuery Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return 0; } } count = DataList.Count; return count; } private VitalData Find(String name) { foreach (VitalData d in DataList) { if(d.Name.CompareTo(name) == 0) { return d; } } return null; } } } The data is formatted to a long string. I used a second DLL on the client side to look up values on the string from the shared string variable to send the resulting data values through the network: //StringToVars.cs //:::::::::::smileyfrustrated:plits and returns data elements encapsulated in long shared string:::::::::::::::::::::::::: using System; using System.Collections.Generic; using System.Text; namespace StringToVars { public class StringToVars { String InString; int Count; public StringToVars() { InString = ""; } public void Update(String vars) { InString = vars; } public Double GetValue(String var) { String[] temp; Double Value; String[] List = InString.Split('\n'); foreach (String s in List) { if (s.Contains(var)) { temp = s.Split(';'); Value = System.Convert.ToDouble(temp[1]); return Value; } } return 0.0; } public int VarsCount() { String[] temp = InString.Split('\n'); Count = temp.Length; return Count; } public String All() { return InString; } } } I was able to load the resulting DLL code into LabVIEW 8.21 > 8.6. LabVIEW 7 or 7.1 may have problems with this if you use one of the newer Microsoft compilers as it requires a CLR that compiles for DotNet 2.0 or 3.x. You might be bale to get this to work if DotNET 1.1 is supported and you compile using Visual Studio 2003 (which really isn't a very stable development environment compared to the two versions since). Good Luck.
Member
EWTech
Posts: 82
0 Kudos

Re: Unfamilair with ADO DB connections

What happened? What a mess...