SQL Count(*) query on table column

[ledbelly2142]

I am having trouble getting the sequence below to work correctly. I have tried "select" with and without the count(*) query. The line commented out // does not work either.

Any help would be great.

Thanks!

global sscDB

global sqlcountquery

sscDB = DB.Open("PostgreSQL30") //DB handle does not need "usr", "psd"

//sqlcountquery = DB.QueryToClass(sscDB "select count(*) officetemp1 from officecond")//officecond is a table in the database, officetemp1 is a column

sqlcountquery = DB.QueryToClass(sscDB "select officetemp1 from officesens")

page.history.Component.Table1.ClearColumns()

page.history.Component.Table1.AddColumn('sqlcountquery.officetemp1', 'officetemp1')

DB.Close(sscDB)

[AzeoTech]

First, its unlikely that count(*) will work using queryToClass. QueryToClass does a count(*) internally to allocate memory, and I'm not sure that most databases would like the double count(*) that would result. If you want to do count(*) its best to simply use Query().

As for the second query not working, either the db couldn't open, in which case you'll get an invalid handle error, or the sql statement is invalid. If its the second, which I'm guessing it is, then there is an error in your SQL statement, which I'll admit, looks fine. However, some DB's are case sensitive. Some DB's require a ; at the end. Maybe you don't have the rights to access that table. If all else fails, try also using QUery() instead of queryToClass().

[ledbelly2142]

Ok,

I was successful in getting some of the sql to work with count(*). It may have been an issue with sql syntax and postgreSQL. I had a problem with the Talbe1 component, in that it was not named correctly (fixed). The Table1 component now shows my column "officetemp1" from officesens1 table... however it shows values at every second based on channel timing (of 1 second), not my logging set of 30 seconds (being logged to db).

It seems like my sql query is accessing DAQF cached data rather than my postgreSQL db. For example, I deleted and started a new table that now has only 71 entries (and growing), whereas the value of the global = sqlqr (below) yields a numeric value (using a Variable Value Component) of 107,948,936.0 (or roughly 30,000 hours, even though I have the History Length and Persist Length set to 100,000).

Not sure what result of sqlqr number really means, it doesn't match up with the count in the postgreSQL database...

Sequence Code:

global sscDB

global sqlqr

sscDB = db.Open("PostgreSQL30")

sqlqr = db.Query(sscDB,"select count(officetemp1) from officesens1")

page.history.Component.Table1.ClearColumns()

page.history.Component.Table1.AddColumn('sqlqr.officetemp1', 'officetemp1')

db.CloseQuery(sqlqr)

db.Close(sscDB)

[AzeoTech]

The returned value from db.query() is a handle. It means nothing to you, and its only use is in the other db functions (except of course Execute and queryToClass). You used it correctly in db.closeQuery. It is NOT an object, so your addColumn() line is wrong. That would be the syntax if you were using queryToClass() which instead of a handle, which you can then use to move through the query result set, its the full result set as an object.