Accessing data from database

General Discussion
1

how do i access individual data from a database,ex c:3,r:4

2

Assuming you are trying to access this data via a database query, you'll need to do an intro course online on using SQL in the database flavor that you are using (MySQL, Postgres, etc...).

A very basic SQL query basically is composed with SELECT, FROM, and WHERE keywords.

SELECT part 
FROM parts_table
WHERE part = 1234

Or do you mean accessing the data in an Ignition table that originates from a db query?

1 Like
3

Regarding the Ignition setting up of database connections (though I highly recommend taking all the courses in the credentials path here)

1 Like
4

can you access the database to get one value. I am trying to do an hour by hour chart. the database only contains one column of production data for two shifts

5

I do agree to look through the documentation proposed. To do what you're wanting to do there are likely very simple ways by setting up your chart component data source correctly. However, to get you experimenting I'll provide some examples.

Assuming you are doing this with with scripting, I have a couple of examples below. The first is by explicitly grabbing the column that you want by name, and the other is by index. In the example below I've run a query using system.db.runQuery, which returns a PyDataset, and inside of a loop I've declared a variable as the column I need. The query has already been set up to return exactly 1 row. The other method is by accessing it with the index of the 2D dataset ( [0][0] ).

Documentation on scripting method used: system.db.runQuery - Ignition User Manual 8.1 - Ignition Documentation

(Not Tested)

# Query to return 1 row
resultSet = system.db.runQuery("SELECT top (1) * FROM TableName")

# Prints info about the PyDataset returned by the system.db.runQuery method
print "Result Set:", resultSet

for row in resultSet:
	# Explicit access by column name
	result = row["ColumnName"]
	print "Result:", result

# Access by Index,  [0]first row of dataset, [2]third column of dataset
print "Result:", result[0][2]
6

You'll probably want to return a value for each hour from the same query. If you can share an example of what the database table data looks like, and what you expect the final results should look like, you can get more help.

8

SELECT sum (actual_poured) as molds_poured
,shift
,hour
FROM [RedLions].[dbo].[HWS_Production]
where date=cast(getdate()-1 as date) and shift='1' and hour ='6' and part_number != 'cc9020'
group by shift, hour