From Friday, April 19th (11:00 PM CDT) through Saturday, April 20th (2:00 PM CDT), 2024, ni.com will undergo system upgrades that may result in temporary service interruption.
We appreciate your patience as we improve our online experience.
From Friday, April 19th (11:00 PM CDT) through Saturday, April 20th (2:00 PM CDT), 2024, ni.com will undergo system upgrades that may result in temporary service interruption.
We appreciate your patience as we improve our online experience.
06-12-2019 10:58 AM - edited 06-12-2019 11:09 AM
Call GlobalDim("NEWT,CheckCalLimits,CalFileName,DefFileNm") NEWT = CLng(NEWT) dim myConn dim myCommand dim RS Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=FWATRGSQLVCL2;Initial Catalog=calsample;user id ='sampleUser';password='sample@1'" Set myConn = CreateObject("ADODB.Connection") Set myCommand = CreateObject("ADODB.Command" ) myConn.Open DB_CONNECT_STRING Set myCommand.ActiveConnection = myConn myCommand.CommandText = "SELECT [heid],[zvalue],[limit],[type],[xchannel],[xvalue],[ychannel],[yvalue],[class],[descrip] FROM [calsimple].[dbo].[Req_Simpledata] where hwid= "&NEWT&""
Set RS = myCommand.Execute
I have this code establishing connection to SQL and returning the result, is there a way, i can use this data to display it to the table on GUI or write this data to .txt file?
06-13-2019 02:50 PM
If you have the data in successfully gathered from the SQL database, the vbscript for creating and writing to a .txt file should be something like:
Set objFSO=CreateObject("Scripting.FileSystemObject") ' How to write file outFile="c:\test\autorun.inf" Set objFile = objFSO.CreateTextFile(outFile,True) objFile.Write "test string" & vbCrLf objFile.Close 'How to read a file strFile = "c:\test\file" Set objFile = objFS.OpenTextFile(strFile) Do Until objFile.AtEndOfStream strLine= objFile.ReadLine Wscript.Echo strLine Loop objFile.Close
06-14-2019 08:51 AM
Hi Tyler,
is vbCrLf, the object holding the result from SQL?
06-14-2019 02:31 PM
No Vbcrlf is just the Carriage return,
I will send you some code on Saturday, that reads and SQL Db and returns the data as a Recordset. The code will show how to access this recordset, into either Channels or arrays.
As far as how to put this data into a SUD. Combobox works nice for this or an Xtable. Xtable is very powerful, but takes more getting used to than a combobox.
Suggest that you look at both of these objects in the help system. They will be good friends of yours soon.
Paul
ps. Some of your questions might be helped by looking at this website
06-14-2019 05:43 PM
Hi Kandukuri,
Here is a script function that will execute a large sql query and return the results I wanted, in a Dictionary object using the Recordset.
Please review and see if helps some.
Paul
public sub GetDBPointDetails(byref oContext_,byval sDbConnection_, byref oDictParms)
dim Connection
dim Recordset
dim sSQL
dim Server
dim MajorDescription
dim PointCode
dim MajorCode
dim MinorDescription
dim MinorCode
sSQL = sSQL & " SELECT dbo.Point2Code.Point2CodeID, dbo.Point2Code.PointCode, dbo.Point2Code.MajorCode, dbo.Point2Code.MinorCode, dbo.Point2Code.XYLocationCode, dbo.Point2Code.OtherLocationCode, dbo.Point2Code.DistanceCode, "
sSQL = sSQL & " dbo.Point2Code.ActivePassiveCode, dbo.Point2Code.SuffixCode, dbo.Point2Code.TransducerTypeCode, dbo.Point2Code.CategoryCode, dbo.Point2Code.GeneralLocationCode, dbo.Point2Code.Suspension02Code, "
sSQL = sSQL & " dbo.Point2Code.SeatCode, dbo.Major.MajorDescriptionText, dbo.Minor.MinorDescriptionText, dbo.Minor.MinorDescriptionText02, dbo.XYLocation.XYLocationDescriptionText, dbo.OtherLocation.OtherLocationDescriptionText, "
sSQL = sSQL & " dbo.OtherLocation.OtherLocationID, dbo.Distance.DistanceDescriptionText, dbo.Distance.DistanceUnit, dbo.ActivePassive.ActivePassiveDescriptionText, dbo.Suffix.SuffixDescriptionText, dbo.seat.SeatDescriptionText, "
sSQL = sSQL & " dbo.TransducerType.TransducerTypeDescriptionText, dbo.Category.CategoryDescriptionText, dbo.Suspension02.Suspension02DescriptionText, dbo.GeneralLocaton.GeneralLocationDescriptionText,dbo.Point2CodeDescription.PointIDdescription "
sSQL = sSQL & " FROM dbo.Point2Code LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Suffix ON dbo.Point2Code.SuffixCode = dbo.Suffix.SuffixCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.XYLocation ON dbo.Point2Code.XYLocationCode COLLATE Latin1_General_CS_AS = dbo.XYLocation.XYLocationCode COLLATE Latin1_General_CS_AS LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Distance ON dbo.Point2Code.DistanceCode = dbo.Distance.DistanceCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Minor ON dbo.Point2Code.MinorCode = dbo.Minor.MinorCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.ActivePassive ON dbo.Point2Code.ActivePassiveCode = dbo.ActivePassive.ActivePassiveCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Category ON dbo.Point2Code.CategoryCode = dbo.Category.CategoryCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Major ON dbo.Point2Code.MajorCode = dbo.Major.MajorCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.GeneralLocaton ON dbo.Point2Code.GeneralLocationCode = dbo.GeneralLocaton.GeneralLocationCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.Suspension02 ON dbo.Point2Code.Suspension02Code = dbo.Suspension02.Suspension02Code LEFT OUTER JOIN "
sSQL = sSQL & " dbo.seat ON dbo.Point2Code.SeatCode = dbo.seat.SeatCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.TransducerType ON dbo.Point2Code.TransducerTypeCode = dbo.TransducerType.TransducerTypeCode LEFT OUTER JOIN "
sSQL = sSQL & " dbo.OtherLocation ON dbo.Point2Code.OtherLocationCode COLLATE Latin1_General_CS_AS = dbo.OtherLocation.OtherLocationCode COLLATE Latin1_General_CS_AS LEFT OUTER JOIN"
sSQL = sSQL & " dbo.Point2CodeDescription ON dbo.Point2Code.PointCode COLLATE SQL_Latin1_General_CP1_CS_AS = dbo.Point2CodeDescription.PointCode COLLATE SQL_Latin1_General_CP1_CS_AS "
'SQL = replace(SQL,"SEARCH_TEXT",sPointCode)
'logfilewrite sSQL
'create an instance of the ADO connection and recordset objects
'
Set Connection = CreateObject("ADODB.Connection")
Set Recordset = CreateObject("ADODB.Recordset")
''open the connection to the database
call post_result(oContext_,"sDbConnection = " & sDbConnection_)
Connection.ConnectionString = sDbConnection_
Connection.Open
''Open the recordset object executing the SQL statement and return records
'recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Recordset.Open sSQL,Connection
dim iRecCtr: iRecCtr= 0
''first of all determine whether there are any records
If Recordset.EOF Then
'logfilewrite "There are no records to retrieve; Check that you have the correct job number."
Else
''if there are records then loop through the fields
Do While NOT Recordset.Eof
dim oPointDetails: set oPointDetails = new cPointCodedetails
iRecCtr = iRecCtr +1
oPointDetails.majordescription = Recordset("MajorDescriptionText")
oPointDetails.pointid = Recordset("PointCode")
oPointDetails.majorcode = Recordset("MajorCode")
oPointDetails.minordescription = Recordset("MinorDescriptionText")
oPointDetails.minorcode = Recordset("MinorCode")
oPointDetails.pointdescription = Recordset("PointIDdescription")
oPointDetails.xylocationcode = Recordset("XYLocationCode")
if oPointDetails.MajorDescription <> "" then
'logfilewrite "[POINTCODE]: "&oPointDetails.pointid &" [MajorCode]:"&oPointDetails.majorcode & " [MajorText]:" & oPointDetails.majordescription
end if
' put line away in dictionary
' key is pointID and value is object with all the fields of interest.
If oDictParms.Exists(oPointDetails.pointid) Then
Call MsgBox("Key "&oPointDetails.pointid &" already exists")
Else
Call oDictParms.Add (oPointDetails.pointid, oPointDetails)
End If
Recordset.MoveNext
Loop
End If
'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset = nothing
Connection.Close
Set Connection = nothing
end sub
' Object to pass the parameters back to calling routine
class cPointCodedetails
dim pointid
dim majorcode
dim majordescription
dim minorcode
dim minordescription
dim minordescription2
dim xylocationcode
dim transducerdescription
dim seatdescription
dim generallocationDescription
dim distancedescription
dim suspensiondescription
dim suspension02description
dim distanceunit
dim pointdescription
end class