PostgreSQL

[dle]

Can I use DAQFactory to access PostgreSQL database?

[AzeoTech]

Probably.  You just need to find a 32 bit ODBC connector for PostgreSQL.  DAQFactory connects to all databases via ODBC and really doesn't know exactly which database you are working with.  ODBC makes this transparent.

[Rachel]

I would like to automatically log data from DAQFactory to a database in PostgreSQL. I followed this website to configure an ODBC driver for PostgreSQL. Then I created an Export Set and changed the settings so that it uses ODBC Database as Export Method. What are the next steps? I am completely new to these platforms so any help is much appreciated! Thanks!

[AzeoTech]

First, make sure the ODBC connection works by clicking on the Test button in your ODBC configuration.  Also, make sure you created the ODBC data source in the 32 bit version of the control panel applet, and not the 64 bit version.  DAQFactory, as a 32 bit app, only connects to 32 bit data sources.

You can quickly verify that the ODBC is accessible from DAQFactory by going to the command alert window and typing:

global dbase = db.open("DAQFactory")

That will try and open the data source named "DAQFactory".  If it just does it without any message, then you are probably good.  If not, it will display an error message.  If it does work, you'll need to close the connection by then typing:

db.close(dbase)

Next, I would probably recommend a logging set if you are just getting started.  It is largely setup the same way, except you just select the channels you want to log.

Finally, I do not know the dialect of PostgreSQL, so you might have to click the SQL button in the logging/export set next to where you put the data source and tweak the SQL commands to match what PostgreSQL uses.

[Rachel]

Thank you! When I clicked Test, the connection worked. I had the wrong version installed, but now I have the 32 bit version. I tried global dbase = db.open("DAQFactory") but an error popped up and I'm not sure why. The error: 01002 Unable to open ODBC database: Line 1

[AzeoTech]

Make sure you didn't put a user/password on the data source itself.  This is different than the user/password the data source uses to access the database, but if you don't know better, maybe you put the same user/password in both places.

[Rachel]

How do I check this?

[AzeoTech]

Unfortunately I don't know the details of the setup for every ODBC data source.  From your screen shot, I would try clicking the DataSource button next to options in your ODBC configuration and see if there is a user/password there.  I would also make sure you don't have another ODBC data source with the same name.  I don't actually know if it matters, but make sure you don't still have a DAQFactory data source in the 64 bit ODBC.

[Rachel]

Ah, I just needed to put my user/pass as paraemters in db.Open as well. Thanks! Now I am trying to insert values into my table, but I keep getting this error: db Line 15 - Uncaught error in sequence db

Here is my code

[Rachel]

I fixed it by first parsing my sql string and then using the sql string in my Execute function. However, now my value is a string.

global string sqlquery

sql_query = "INSERT INTO public.pressure_table_01 (pressure) VALUES (" + DoubleToStr(val) + ");"
db.Execute(dbase,sql_query)

[Rachel]

Do you have any suggestions on how to keep the original data type while still being able to write the data to my database? Thanks!

 

[AzeoTech]

It is important to understand that the command you send to your database has nothing to do with, nor has any access to, anything inside of DAQFactory.  This is why your first command failed.  "val" is a DAQFactory variable, not a SQL variable, and the database has no idea what val is.  You fixed it in your second post, though truthfully you did not need to use doubleToStr() as DAQFactory would do this automatically since the concatenation starts with a string.

As for the data in your database, again, it has nothing to do with DAQFactory.  In fact, the SQL command:

INSERT INTO public.pressure_table_01 (pressure) VALUES (3);

which is what you would get if val was 3, should only work if the pressure field was numeric.  If the pressure field was a string in the table, you would have to put quotes around the 3:

INSERT INTO public.pressure_table_01 (pressure) VALUES ('3');

So, I'm not really sure what you mean by the value becoming a string?

[Rachel]

Haha my bad, thank you so much! I was able to input the numeric.

Now I'm trying to input the date and time into a column in my table (column is called "thetime" and it is of type: time without time zone). DAQFactory logs date and time (for example, 6/16/2022 16:10) but I'm not sure how to access it.

I tried datetimes = File.ReadDelim(FileHandle,0,",",chr(10),0) but this just gives me an array with only the first value of each of the dates: {6, 6, 6, 6, 6}.

And after adding that line of code, pressureVals = File.ReadDelim(FileHandle,1,",",chr(10),0) does not work as I would like it to anymore. When I print printVals, an empty line is printed rather than an array of values.

Thanks for all your help!

[AzeoTech]

Are you reading from your database or a file?  I'm assuming a file since you are using File. functions.  To read a date value that is stored in human readable format you have to read that column as a string then use the strToTime() function to convert it into an actual time stamp.

[Rachel]

My problem has been solved! For configuration from DAQFactory to Postgresql, in the Logging set settings, I had to input FLOAT8 as the DATE variable type in SQL settings, and change the time setting from Custom to DAQFactory Time. Thank you for all your help!