First sequence, Script trouble with opening a db

[ledbelly2142]

I am new to sequences and DAQFactory scripting.

Attempting to write my first sequence for accessing my PostgreSQL database. My goal is to access the data being logged to the database (step one), then manipulate the data with queries and present things like avg for last 5 days, etc... (step two). I am successfully logging to the database, according to DAQFactory. I am not yet able to access the database. I am using the sample script out of the manual in section 9.5, starting on pg 260.

I suspect some simple scripting errors.

global dbase
global qr
global count
global thetime
global batch
global start
global strTime
dbase = db.Open("SSCTestDB1")
qr = db.Query(dbase,"select * from logfile1 where Operator_ID = 'postgres'")
global counter
counter = 0
while (!db.IsEOF(qr))
thetime[counter] = db.Field(qr,"TheTime")
batch[counter] = db.Field(qr,"Batch_Number")
start[counter] = db.Field(qr,"Start_Date")
strTime[counter] = db.Field(qr,"Start_Time")
counter++
db.MoveNext(qr)
endwhile
db.CloseQuery(qr)
db.Close(dbase)

My db fields in table "u3labjack1" are:

the time

temperature1

temperature2

temperature3

curren1

voltage1

current2

current3

flow1

tempflow

temphotpod

tempcoldpod

Error:

08/08/11 08:58:13.805

O1002 Unable to open ODBC database: test1script1 Line 8 - Uncaught error in sequence test1script1

[AzeoTech]

First things first: watch your indentation. This is programming 101 and applies for any programming language.

Second: I recommend using the queryToClass() function for all queries. The standard Query() function is slightly deprecated, though it has some rare uses. QueryToClass() is talked about in 9.5 of the user's guide, and in this forum.

Finally, if its failing to open the database, then the issue isn't your script, unless there is a typo in line 8. Did you log from within DAQFactory using this same data source name? Are there any login credentials required?

[ledbelly2142]

In the sample script I am using, in line 9 (below) what does the "logfile1" identify? Is it the table name in the database table or the ODBC windows driver (source) that I made to log to the database?

Line 9:

qr = db.QueryToClass(dbase,"select * from logfile1 where Operator_ID = 'postgres'")

I used "logfile1" only because it was in the sample script...

Also, when you said to watch my indentation, none of the lines in the script are indented... is this incorrect?

Thank you for your patience.

-Greg

[AzeoTech]

logfile1 is the table in the database. The ODBC data source is only used in the Open() function. Everything in the query is standard SQL SELECT and you should refer to your database's manual for details on how to format the string (since DAQFactory just passes it to the database).

As for indentation, please see 5.2 of the user's guide for more detail, and no, some of the lines should be indented. But I see that the sample you copied doesn't show indentation in the help file within DAQFactory. This is because it is in a browser and the browser is stripping the indentation. The PDF form of the user's guide shows the correct indentation. We will look to fix this issue with the online help.

[ledbelly2142]

I am having trouble with the following line opening the PostgreSQL db:

dbase1 = db.Open("SSCTestDB1", "postgres", "password")

In this line, I am attempting to open my database "SSCTestDB1" with my user name "postgres" and password "password"

I think I have and issue with the db.Open command with PostgreSQL, the commands in the PostgreSQL manual to open a database from a client include:

PGconnectdb :makes a new connection to the database server using parameters taken form the string conninfo e.g. PGconn *PQconnectdb (const char *conninfo)

also

PQsetdbLogin :makes a new connection to the database server e.g. PQsetdbLogin(const char *pghost, const char *pgport, const char *pgoptions, const char pgtty, const char *login, const char *psd);

also

PQsetdb :makes a new conection to the datbase e.g PQsetdb (char*pghost, char *pgport, char *Pgoptions, char *pgtty, char *dbName)

So my question is, should I be using one (perhaps more) of these commands to open my PostgreSQL database instead of "db.Open"?

Something like:

dbase1 = db.PQsetdbLogin( const char *pghost "localhost", const char *dbName "SSCTestDB1", const char *login "postgres", const char *pwd "password")

Or, is the db.Open part of the scripting DAQFactory scripting language, and I should include my PQsetdbLogin after the db.Open?

Thanks,

Greg

[AzeoTech]

The stuff you reference is for accessing the DB from C, not from DAQFactory. Doing db.PQsetdbLogin() won't do anything because PQsetdblogin() is not a function in DAQFactory, its in their DLL and you haven't linked to it. You don't want to link to it because then you are stuck using their DLL for everything. You need to figure out how to connect to the ODBC driver. Did you say it works from a logging set? If so you might need to use OpenEx() and fiddle with the specifier. Check the docs for the Postgre ODBC driver, not postgre itself.

[ledbelly2142]

I am successfully logging to the PostgreSQL server (verified with pgAdmin III and a simple SQL table query). Now I am trying to read or extract the logging data back into DAQFactory.

For example, I would like to average the last 5 days by time of day (hour) over a 24 hr period. I would like to be able to compare today with the average of last week, and average of a week a year ago by hour--all from the db.

I would also like to extract data from other databases and XML feeds (e.g. the National Weather Service from NOAA http://www.weather.gov/xml/current_obs/ )

The functions I am trying to do are:

1. first access a localhost db

2. manipulate the data and present useful information (such as comparing averages) from localhost db

3. access external db (IP address, public information) and present useful information

4. take in an XML feed and log data to database for trending (e.g. NOAA information)

I had assumed that I would need to use a sequence that included an SQL query to extract the data from the localhost database. Perhaps I am going about this wrong and (hopefully) there is an easier way to accomplish this task.

Because I can successfully log, using the *working* windows 7 ODBC driver for my PostgreSQL database server that connects DAQFactory, is there another way (besides a sequence) to query the logged data for averages?

Thanks,

Greg

[AzeoTech]

No, you have to use scripting to load data in. Try eliminating the password on the ODBC and doing:

private handle = db.openex("DSN=xxx;ODBC;")

where xxx is the ODBC data source name.

[ledbelly2142]

That got me past line 8. I had no error with the following line:

private handle = db.openex("DSN=PostgreSQLSSC35W;ODBC")

Does this give me access to my entire database server on localhost? e.g. all my datases.

If I want to declare a global variable like dbase1 = db.Open("temptest1", xxusername", "xxpassword")

where temptest1 is the database I am successfully logging temperature readings to, "xxusername" is the user name, and "xxpassword" is the password. It seems that if I have access through the DataSource ODBC Windows 7 driver, I would not have to use a username or password when defining dbase1.

I tried:

dbase1 = db.Open(DSN="temptest1")

But it does not work, any suggestions?

Thanks

[AzeoTech]

You have access to whatever the data source gives you access to, usually a single database (but multiple tables within that database). Your line:

dbase1 = db.Open(DSN="temptest1")

does not work because it is malformed. In C and some other languages you can do assignment within a function call, but not in DAQFactory, and besides, I don't think that's what you want anyway. First, Open() doesn't require "DSN=", it does it automatically for you, and second, if you were using openEx with that format, you'd enclose the whole thing in quotes. But why are you trying to use Open() anyway when you got openEx() to work properly? If you want to pass a user/password in using OpenEx, just add:

UID=%s;PWD=%s;

to the string, where %s should be replaced by the user/password as appropriate. But this will likely now work for you since doing:

dbase1 = db.Open("temptest1", xxusername", "xxpassword")

is identical to doing:

dbase1 = db.OpenEx("DSN=temptest1;ODBC;UID=xxusername;PWD=xxpassword;")

which you already said didn't work. My guess is that it didn't work because you have the wrong user name and password, or, for some reason, your DSN is picky about ordering of the parameters in the open call. I can't really say. I'd consider just skipping the user/password and protect the DB using a firewall if you can, or through database security (i.e. make a separate DSN for reading that uses database credentials that allow only for reading).