I was looking for the changelog on the new FSQL 4.2.0 (not in integrator portal yet), but I noticed for version 4.1.4 there was an item: Fixed - Stored procedure group CSV export not correct.
Can anyone elaborate? If either FSQL or SQL Server could export CSV files that would be extremely awesome!
Oh, and where can I find the new changelog? Likin’ the new forum theme as well.
I’m not sure what you’re looking for in terms of CSV export, but FactorySQL has always been able to export/import group configuration in CSV format. Simply select some groups in the lower right pane (or don’t select any, to export everything), then right-click and select “Export to CSV”.
If that’s not what you’re looking for, please elaborate and we’ll see what we can do!
Regards,
Well what I read in the changelog lead me to think there was some way to export stored data - not FSQL groups/configuration - via stored procedures or within FSQL. I just finished a project where I had to collect servo positions at a very fast rate and write them to a CSV file so the customer could make a pretty graph. I ended up writing my own OPC client in VB to do this, but if there is a way to do it with FSQL/SQL Server then I could have saved myself a lot of time! (I learned VB by doing this project, so it took me a while). Maybe this topic is better suited to the general section?
Ah, I see… looks like you needed a FactorySQL CSV group 
Short of that, it should be relatively easy to go to SQL and then CSV… MySQL has handy query syntax like “SELECT INTO OUTFILE blah…”. For SQL Server, it looks a bit tougher, like you might have to use a separate app to do it (that I believe comes with SQL Server).
Of course, ultimately I might ask why you’d want to graph off of csv instead of the database 
To me it seems kinda like putting your Ferrari on a flatbed truck to drive it around, but I know, I know, the allure of excel is still strong… 
Trust me, if I could graph out of SQL I would, but we had to bully our customers into SQL from dBase in the first place. One step at a time 
. Anyways, I officially submit a feature request for CSV groups in FactorySQL!
After a bit of googling and experimenting, I found a way to do it with SQL server. It is not very pretty as it opens up some huge security holes, but it works. You need to create a stored procedure that looks similar to:
exec master..xp_cmdshell 'bcp <database>.<owner>.<table out C:\<path>\<to>\<file>\<filename>.csv -c -t, -T -U<sqluser> -P<password> -S<server>'The gaping security hole is from enabling the use of xp_cmdshell, which basically allows the SQL server to execute anything that you could do from a command line.
Anyways, hopefully this is useful for someone. I’ll be using it for sure, since I don’t care about the security hole as all our databases we ship are isolated from everthing except the machine it’s collecting from.
Back to my original goal: Where is the changelog for FSQL 4.2.0? Or should I wait until it is up in the portal?
Umm... that's a good question. I'm not sure, but what I can do is paste the changes in here, taken from the change log in the install directory:
Version 4.2.0
o Added - New "database" scripting functions: IsConnectionAvailable, ExecuteScalarQuery, ExecuteUpdateQuery, ExecuteQuery, GetDataSetValue...
o Added - New "failure" handshake option, writes to OPC after group execution failure.
o Added - Advanced group options: synchronous read from OPC instead of subscribe (available for Standard, Historical, and Stored Procedure groups).
o Added - New buffered data writing option. Writes to buffer instead of directly to database- very useful for data connections that can go down (works well with data cache to prevent data loss) and for short-term quick logging.
o Added - Existing database table columns detected, field name entry on item configuration is now a drop-down, autocomplete combo box.
o Changed - Buffered logging always enabled for Historical group, advanced option for standard group.
o Changed - Improved table/procedure listing algorithm.
o Changed - Async OPC writing now the default for new installations.
o Changed - Standard/Historical groups now use parameterized statements against the DB.
o Changed - Now possible to start groups when db connection is unavailable.
o Fixed - Many improvements to datacache system:
o Better threading, lots of data in cache no longer causes service to start slowly.
o Automatic periodic database checking, data loading (earlier versions only checked on certain events).
o New "quarantined" data facility to handle queries that error out on load.
o Improved system status screen, with facilities for deleting cached & quarantined data, as well as viewing errors.
o Fixed - Subscribing to OPC value, quality, timestamp causing multiple item subscriptions on server.
o Fixed - Expression function menu now alphabetized.
o Fixed - Analog alert deadbands not working correctly.
o Fixed - Error writing alert to database in certain cases.
o Fixed - Postgres DB translator file missing 'characters to escape' field.
I haven't try it with FSQL but I think it may work since should be transparent to it. How about if you create a ODBC DSN that points to the CSV or text file. When you build the DSN you are going to have to define the field names and datatypes so that FSQL will know. Again I haven't try it with FSQL.
I hope this helps you.
That’s actually a really good suggestion, and something I’m surprised we haven’t thought of before. It’s funny how most of our competitors tout CSV writing as a major feature and we’ve never even cared enough to realize you could do it fairly easily with the ODBC driver.
Now, that said, it’s not quite perfect… because I don’t think you can do it. It looks like we need to make a few small changes to get it to work, such as allowing you to start logging a group when FactorySQL can’t verify the table. Anyhow, we’ll get it working for the next release, 4.1.2.
Thanks again for the tip!
Thanks for getting this into development so fast.
That is one of the good things that I see from Inductive Automation they have an excellent product in FSQL and they listen to us users and react quickly. I know that is something difficult to manage new features, changes, quality and testing and more testing.
Again thank you 
Colby for all your support I hope that inductive automation value your contribution and treat you nice and make sure you don’t go anywhere. 
Hey all-
As promised, version 4.2.3 works with the CSV ODBC driver (btw: you may have noticed the version jumped from 4.2.1 directly to 4.2.3… there was a slight mixup in the build/upload process that forced us to skip 4.2.2 
)
I’ve also put together a quick HOW-TO illustrating the process to get it up and running:
CSV How-To
Feel free to post here or there with any questions!
Regards,
WOW that was fast, good job guys, I also read other updates an features, thanks.
[quote=“Colby.Clegg”]Hey all-
As promised, version 4.2.3 works with the CSV ODBC driver (btw: you may have noticed the version jumped from 4.2.1 directly to 4.2.3… there was a slight mixup in the build/upload process that forced us to skip 4.2.2 )
I’ve also put together a quick HOW-TO illustrating the process to get it up and running:
CSV How-To
Feel free to post here or there with any questions!
Regards,[/quote]
Thanks a lot guys! This gives us a lot more flexibility.