How can I get an autogenerated value in Postgres within a new SQL statement?

[tim_m]

There are two possible ways that I could go about doing this. 

The first:On the creation of a new record, my schema in Postgres automatically inserts a unique value into a given field.  However, whenever I run queries to get that value using Teststand, I get an error that states the value is null.  However, querying from psql, I am able to see the UUID.

Note that the field itself is not problematic.  If ask for the UUID value in the previous record, it fetches the value from two records ago--which is a bit odd, but is better than recieving a null value.

The second way to do this is to query the sequence which we use to generate the UUID.  In Postgres a sequence is simply an autoincrementing value.  Each time it is used, it increments by a given amount.  Further, the current value of the sequence can be queried as follows:

SELECT last_value FROM *sequence_name_goes_here*

However, running this query and then trying to get that value and store it also returns a null error.  To be fair though, the sequence is not viewable within the Teststand Database Viewer. 

For reference, I'm using Teststand 2.0.  For the driver I'm using the Microsoft OLE DB Provider for ODBC Drivers.  I'm using Postgres 8.0.12.

I'd appreciate any thoughts or suggestions on how to make this work.  Does anyone here have Postgres experience?

Note that the same Teststand code that we're running now worked fine in Access, but causes the aforementioned errors in Postgres.

Thanks,
Tim

[Brandon_V]

tim_m,

How are you interacting with this database? Are you using database logging or the database step types? If you are using the logging, which schema are you using? Have you modified it to do these actions? It may be possible that you need to be using another ODBC driver or OLE provider for compatibility with Postgres

[tim_m]

Brandon,
Thanks for getting back to me.

I'm not quite sure what you mean.  I'm using Teststand's sequence editor.  Within this there are options for running SQL queries.

For connecting to Postgres, I'm using a .udl file that relies on a Postgres ANSI driver, which I believe came with my install of Postgres.


In any event, I think I've narrowed down my question a bit (now that I understand more how Teststand works).

When one runs an SQL query within Teststand you have the options of "Fetch previous," "Current," etc.  This was connected to the default Access schema (UUT_RESULT) and a RUN_ID was generated on every "CREATE NEW RECORD." 

I think the problem is that, when you run a "SELECT" query with the parameter set to "Create new record" Access will generate the RUN_ID before returning the results.  With Postgres, it won't generate the RUN_ID until one inserts the "default values."  Here is the SQL log of what Teststand is sending Postgres:


Mar 26 14:01:37 [postgres] [12-1] <46081865.1bcd>LOG:  statement: SELECT uut_result.run_id, uut_result.station_id, uut_result.start_date_time  FROM uut_re
sult
Mar 26 14:01:37 [postgres] [13-1] <46081865.1bcd>LOG:  statement: INSERT INTO uut_result DEFAULT VALUES

To do this, I just have to run "set" and then "put" without setting or putting any specific values.

So here is my updated question:
If I run a new select query(over a new statement) and choose "Fetch next", each time I call that it will iterate through the select query, starting with the very first record.  My question is, how can I tell it to stop when it gets to the last record, since there is no telling what the last record is?  Or, in general, how can I get the values in the final record?  We've changed the RUN_ID to simply be an incrementing integer, so if a MAX aggregate function can be run, that might work.

Any ideas?
Thanks,
Tim

[Brandon_V]

Tim,

You should be able to just go through a loop and perform this on all records. Once you try to call Get Value or use the record, it will return an error. You can ignore this error, check its error number to verify that this error occurred and if so, you know you've hit the end. This is described in the following forum link, however be sure you check which error number is actually thrown.

http://forums.ni.com/ni/board/message?board.id=330&message.id=3664&requireLogin=False