Home : SQLXFIRST
Q10957 - INFO: SQLXFIRST

SQLXFIRST returns a value directly from an external database (such as a SCADA Historian, LIMS...) using the SQL Query.  If the result of the SQL Query is a table of more than 1 column by 1 row, SQLXRESULT must be used to retrieve other values.

 

SYNTAX:

SQLXFIRST(Col, Row, "Connection String","SQL Query", MaxColumns, MaxRows, Parameter1,…,Parameter20)

Col: Column number to retrieve result from

Row: Row number to retrieve result from

"Connection String": An ADO connection string to the external database or the name of an External Data Source.  See External Data Source Setup for example connection strings.  If your connection string contains quotes (") you MUST but the connection string in different cell and refer to the cell.

"SQL Query": SQL select statement to execute

MaxColumns: Specifies the number of columns in the resulting table

MaxRows: Specifies the number of rows in the resulting table

Parameter1..20: OPTIONAL. External values to be used substituted into the query, should the query refer to them.  In SQL Query, use @Px@ to refer to the parameter, or #Px# to refer to the parameter as a date, $Px$ as a number.

NOTES:

You must install the OLE DB driver (provider) for your database on each PC that will use the report.  See your database admin for the connection string and driver for any database you want to connect to. 

EXAMPLES:

=SQLXFIRST(1,1,"Provider=SQLOLEDB;Data Source=LT006;initial catalog=Cimplicity;User Id=sa;Password=admin;OLE DB Services =-2;","SELECT * FROM EPA_LOG WHERE RIGHT(BatchNum, 1) = '@P3@' AND STARTTIME>='@P1@' and STARTTIME <'@P2@'",8,100,C4,K5,C3)

Returns the 1st column and first row from 8X100 table of records from the EPA_LOG table in the Cimplicity database on the LT006 MSSQL Server.


=SQLXFIRST(1,1,"Provider=ihOLEDB.iHistorian.1;User Id=;Password=;Data Source=IS001;","SELECT Avg(Value) from ihRawData WHERE SamplingMode = 'Lab' And Tagname = 'LT006.Simulation00001’ And TimeStamp >= '11/24/2003 00:00:00' And TimeStamp <= '11/24/2003 23:59:59' And NumberOfSamples = 1440 And Quality = 100",1,1,C4,K5,C3)

Returns the average value for tag LT006.Simulation00001 from an Ihistorian server via the OLEDB Ihistorian driver.


=SQLXFIRST(1,1,"ChemicalCosts","Select TOP 1 Price FROM ChemCosts WHERE Chem = 'Cl2' ORDER BY DATESTAMP DESC",1,1)

Returns the Price of the most recent entry from the ChemCosts table for Cl2.  Uses the connection string defined in the External Data Source named "ChemicalCosts".  Using the external data source allows you to hide connection information (Username and Password) for the users of the report. 

 

 

Download Example Template: Help_SQLXFIRST.ss3 (Click here for download instructions)

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 4/21/2008 11:29 AM.
Last Modified on 2/7/2014 11:45 AM.
Last Modified by Scott Dorner.
Article has been viewed 3259 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article