Sign in to follow this  
Followers 0
jeffersm

RSView script for SQL queries

9 posts in this topic

Hello I am in charge of a project to put a new solution mixing tank in service and we would like to have a report of time stamps and temperatures automatically generated and saved to a file (preferably PDF) at the end of its 2 hour cycle. This unit has RSView Studio SE HMI software Version 5.00 which is logging the the temperature and time stamp to an SQL database. Unfortunately our programmer is not familiar with writing scripts for performing this function. Does anyone have sample code using SQL querries that you could share with us? Any code samples or recommendations would be greatly appreciated.

Share this post


Link to post
Share on other sites
Hi Jeffersm, Please post the fields of the SQL database table being logged to and i will see what i can do.

Share this post


Link to post
Share on other sites
Go to w3schools and go through the tutorial on SQL. SQL itself is a language that is a bit tricky to get used to. Second, you have to navigate the SQL interface. This is a 3 step process. I'm not sure which version of VBA is built into View SE so from here on I'm running on knowledge of the general scheme. The old way is using SQLOpen (connect to the database), SQLExec (run the query), and SQLRetrieve (get the results). The new way is using ADO. Even old versions of VBA will support the ADO object. There also happens to be a good tutorial on w3schools for using the ADO object. I also strongly recommend that you download a copy of queryexecplus (queryexplus). This is a free utility which allows you to type in and run SQL queries by hand. Whenever you are developing SQL code, it's almost always a 3-4 step process. First you develop a working query by hand. Then optionally you convert part or all of the query into either a view or a stored procedure. Then you copy the query and paste it into your VBA (or other language) code. Finally, you parameterize it by inserting variables in the spots where you intend on filling in data at run time. Those first two steps are the toughest hurdles. SQL is based on set theory so it is very difficult to "get your head around it" because programming in almost every other language revolves around either declarative or imperative programming on the pieces and not the whole set at once. The second one has to do with dealing with the database interfaces and transporting the data from a binary SQL interface into native objects of the language of your choice. SQL itself is pure text...it's just the data that isn't. On your first couple attempts you may try to grab the whole data set and manipulate it in familiar (VBA) code. If you can, don't do this. SQL essentially gets turned into compiled code and the queries are much faster if you run all your data processing on the database server and only retrieve the results that you want.

Share this post


Link to post
Share on other sites
SQL isn't that complicated to learn and it's very powerful - that's #1 in Paul's response. As he suggests, W3Schools is a great tutorial. There are other sites too. I like SQL Zoo. I also love the book Head First SQL, although it is geared toward MySQL. #2 will be a process since you're using RSView so you'll need to learn the stored procedure stuff and old bits of VBA. I never really understood their infatuation with stored procedures. They miss the potential great benefits of the feature, effectively only introducing the complexity (I'd love to get into this debate...). #3, the VBA and ADO thing may be welcome if you're a geeky object oriented programmer, but probably confusing otherwise. A lot of the object model has to do with things like establishing your connection, credentials, and create a data object for you to loop code over. A nice frontend (which can be part of an HMI) will roll these into user friendly one time configurable connections and GUI fields that create the tables/etc for you. There's a lot of programmatic plumbing that you have to deal with in this case. Do lots of copy/pasting and follow Paul's advice and you should be fine here.

Share this post


Link to post
Share on other sites
Kewl - W3Schools is awesome. - I'll be spending some time there.

Share this post


Link to post
Share on other sites
There are a couple benefits to using stored procedures. They are compiled so they are faster after the first execution. (probably not too big a deal in the PLC arena.) They parameterize all variables that you pass into a procedure.(this helps mitigate against SQL injection attacks. Again, probably not too big a deal in the plc arena but it could be.) They also make things a little more secure with the credentials. you can just give a user the permission to run a stored proc and then if the username and password are stolen from the application there is less of a vulnerability. The user can only run the procedures that they are granted access to. They still can do damage but you know the full extent of the damage. For example, you have a salary table with hours worked as a column, that you join to a username table. You could create a proc that just queries how many hours were worked and relate that to a user. If you were doing this without procs, then the user would need select access on the salary table which if the username and password were stolen would let the person figure out how much everyone else is making.

Share this post


Link to post
Share on other sites
I agree with Nathan about the stored procedures argument. I use them but I use them mostly to set up things that are difficult to do any other way. If you are trying to calculate the difference between two rows, this is very difficult to do. The correlated subquery approach has horrendous performance issues. That leaves derived tables (essentially a non-correlated subquery) and temporary tables. Depending on the problem, derived table solutions can be almost unreadable. Temporary tables in stored procedures are simple, clean, and give excellent performance. DAO requires much less typing and if you can get over the silly data retrieval setup stage, it is simpler and easier to understand than ADO. However, documentation is lousy. All the add-columns business makes my head hurt. It also seems downright silly since you are adding columns to a variant array! I recommended the ADO approach simply because it took me two weeks mucking around in documentation trying to figure out how to use DAO on my first attempt with RS-View 32 over ten years ago. In comparison, ADO is well documented and well publicized. I'd rather work with a well documented interface than a poor one, especially when Microsoft is involved. I personally use DAO but the learning curve can be a lot worse. I can make one additional recommendation. Start with the examples that you can find out there and modify them. Don't think that you can simply get the documentation and code to the specification. Most Microsoft objects have very strange order-of-operation issues that will drive you insane. If you don't do things exactly in order (follow the example), things may break on undocumented bugs. This includes closing down objects. If you don't, you may also encounter strange memory leak bugs as well. These are the primary reasons that I'm not a huge fan of VB(A).

Share this post


Link to post
Share on other sites
Your points are correct. I'll comment in general and specific to this application. For the record, I am a fan of stored procedures. What I want to convey is that they don't get appropriately utilized and tend to complicate things for this sort of application. Users are taught that it's the only way to go, don't understand why (nor should they need to), and as a result, don't functionally gain anything from it. It's like if you were only allowed to write a PLC program using indirect addressing when you first learned how to program! 1. Not only are they pre-compiled, but they tend to remain memory resident in the server. Great for some applications, negligible in most cases here. These guys typically want to populate data from a SELECT query as a rare event, and that usually comes from a single table. There should never be reason to have sensitive embedded data in the same table like your example. 2. Stored procedures are great for a "black box" programming paradigm. However, the majority of these simple applications run the generated queries locally and distribute the data by some other means (or are stand alone). That's not to say that they're not vulnerable to SQL injection attacks, but these guys have much bigger worries from a knowledgeable hacker, like DCOM vulnerabilities. 3. You are correct about being able to lock down user permissions to specific stored procedures. In a financial setup this is very important. Most of your RSView users aren't DBAs, heck, they usually use the 'sa' or 'root' root account. Much tighter security can be applied with or without stored procedures - the latter can provide more granular control, if that's the only thing your project uses (with that account). However, let's take a step back for a dose of reality. Without a lot of extra, weird programming, these applications do not authenticate users separately with respect to the database! What I mean is that they don't run queries with the permissions of the user, they run it with a "system" or shared account. Even if you did lock down the system to what the most powerful user could do, you're hammering the principal of least privilege. These systems store the authentication credentials either in the script, or in the ODBC connection! In a theoretical argument you're correct, but nobody ever does it that way in RSView! They don't even come close! My big gripe is that they're using stored procedures for simple SELECT queries. Code is scripted in VBA, the system typically executing queries on the same machine, running from the same source on the same destination. This forces a de-coupling where it doesn't make sense or provide any benefit. It's like the general asking the sergeant to order the private to tie his shoes when the 3 of them are the only ones in the room. The user just wants to populate data in a table. Every step along the way is more complicated. As far as security goes, the application doesn't functionally support locking down the system to the point where having the granularity of security for stored procedures matters. Besides, the system has other gaping issues for a real attacker. It's like upgrading your locked metal cage to a vault door when there's a huge hole right next to it. The idea behind your salary example is a good textbook argument for stored procs. In this case the whole application runs off the same username/password, so you'd better separate those salaries into a different table and lock that one down!

Share this post


Link to post
Share on other sites

Script para obtenção de Dados do Sql para o FactoryTalk. 

Boa tarde, estou precisando de uma ajuda. 

Alguém sabe como fazer para buscar dados do Sql Server e mostrar este no meu supervisorio FactoryTalk? 

Sabem de algum script para fazer esta leitura? 

Agradeço a colaboração.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0