Stored Procedure group problem

The Where clause would be this:

RunNumber={RunNumber}

The actual value going to the SQL sever would be this

RunNumber=202L-0840-L001

This is where it freaks out and says there’s a problem converting the string to integer. Why does it need to be an integer?
If I write this in Query Analyzer it needs to look like this

SELECT RunNumber FROM L_TestRN WHERE(RunNumber = '202l-0835-l001')

So I tried doing it like this:

RunNumber='{RunNumber}'

But it still doesn’t work. If you can help me get this working it’ll surely be simpler than the workaround I just came up with.

I put together a standard group that runs once a second. I’ve got one OPC item and one action item. The action item runs this query:

SELECT L_RunNumbers_ndx FROM L_TestRN WHERE RunNumber='{RunNumber}'

That stores its value to the OPC item “RunID”, which stores its value in N7:0 in the PLC. Then in my other group that’s updating the done bit for the run in the database, my where clause uses N7:0, which is obviously an integer. This works, but it’s way more complicated than you’d think it needs to be.

The second version:

RunNumber='{RunNumber}'

is what you want… Does it error out when you start the group, or when the group actually executes? Do errors show up on the status tab of the group? If so, double click one of the messages and paste the text in here.

Also, just for completeness, what version of FactorySQL are you using?

Regards,

Sonic, I accidently deleted your post. Could you please repost your response?

perhaps RunNumber is set in your database table as an integer instead of varchar…

SonicClang wrote (before Nathan somehow deleted it…)

[quote]I’m at home now, but I saw the error enough times today to have it memorized. With the single quotations it still says “unable to convert string to integer.” I can copy and paste it tomorrow.

I’m using whatever version is downloadable right now from your site. I never thought to upgrade. I also have some weird issues with the mouse disappearing when I type any kind of expression anywhere. I have to click for it to show back up again. Maybe fixed with the upgrade?[/quote]

Umm… I highly doubt an upgrade would help, since FactorySQL doesn’t actually control painting the mouse, that’s window’s domain. Could be some mixture of the .net framework version and the version of windows. On my system (vista) the mouse flickers while I type, which I still can’t really explain, but it doesn’t disappear completely.

As for the data problem… I’m just trying to figure out if it’s from the FSQL side or the database side. I’ve mocked up various situations using strings in dynamic where clauses without a problem, so I’m trying to figure out what the missing link is… If you go to Help->Log Viewer, does a similar message appear in the log? If so, does it provide any more information?

Regards,

I’ll definitely get more info on the error tomorrow.

When I go into Query Analyzer on the SQL server, the query works just fine.

Alright, so here’s my where clause, along with the error.

RunNumber=’{RunNumber}’
Conversion from string “Test” to type ‘Integer’ is not valid.

I get the same error if I wrap the where clause in parenthesis. If I take away the quotation marks it says “Unknown column test”.

Please provide the following:

  1. column names in the db table with types
  2. OPC items with types that are not read only

Column “RunNumber”, type varchar.
OPC item “RunNumber”, type string.

And just to confirm, you’re using FactorySQL 4.2.7? You can find the version on the Help->About screen or on the title bar of the frontend.

Regards,

Yes, that is the version I’m running.

Could you right-click the group, select “Export to CSV”, and send the file to support AT inductiveautomation.com ? (Or you could upload it here).

Something’s just not adding up for me… it’s got to be something simple! I just created a variety of groups using string based item references in the where clause, and I didn’t have any problems… There’s got to be some simple explanation.

Regards,

Well, I honestly don’t understand why, but it’s working now. The only thing I can think of is that I made a new table today. Maybe something was messed up with my old table. I didn’t let FSQL create the table yesterday, I tried doing it myself. Today I let it make it so I could get an index column working correctly. Now the where clause is working just fine with the string.

Ahhh… yes, I had the thought that you may have tried to specify your RunNumber as the table’s index in FactorySQL (by clicking the “options” link above the table name).

In FactorySQL, you can use an existing/manually created table, but there must be an integer index column. Setting the index column to a string currently won’t work… but I didn’t think it came into play with a custom where clause.

Glad it’s working,

Me too :slight_smile:

So far I’m really loving this software. I’m currently in the process of convincing the owner of the company we should purchase it. I don’t think it’ll be a hard sell once he sees what it can do.

I’m glad you like it. In our experience, we’ve found that people switching from RSSQL 1) Really really like FSQL and 2) Catch onto how to do things in FSQL very quickly. We tend to spend most of our time promoting the overall package and I sometimes think the stand alone functionality of FactorySQL gets overshadowed. So, feel free to spread the word to all of your RSSQL using friends! :laughing:

Regards,

Well, I can't say I know anyone else using RsSQL. I dragged another guy from the engineering department to a class last year for it, but he never used it after that. I'm the only person at my company who does. But that's not to say I'm not telling every person I know about FactorySQL! :smiley: I just spent 20 minutes telling my brother-in-law all about it and he doesn't know the first thing about databases and PLC's. I also just told my wife all about it, and trust me, she definitely doesn't care.

Again, you guys rock! :prayer:

Well, you’re ok to share comments like that on PLCs.NET or mrplc. I won’t start any such threads, but there’s no reason you can’t - especially if you have specifics to discuss or questions. We get a pretty good amount of exposure from those forums from posts that talk about FactorySQL or FactoryPMI.

Have you played with FactoryPMI? I think you’ll find it similarly more interesting than RSView.

I watched a couple videos on it yesterday. After my experience so far with FactorySQL and how amazing your customer support is, I'm seriously considering installing it to try it out. The problem is that we have SOOOO many thousands of dollars invested into RsView, and 8 years of experience with it, that it would be extremely difficult to justify a change to FPMI. I have absolutely no doubts that it's better than RsView though, without even trying it yet. From what I can see, Inductive Automation truly understands what end-users want. It seems like Rockwell software is "designed by engineers, for engineers", without any ease of use in mind.

Although I should point out that any kind of interlocks or true controls are handled in ladder code in our PLC's. For our uses, RsView works quite well and we've had minimal issues with it over the last 8 1/2 years. I learned it very quickly and we had our first computer controlled machine up and running within just a couple months of me learning to use it. But I think my background in game designed helped me get my head around industrial controls faster than some. I used to make levels for games, and it's amazing how close that is to industrial controls. The big difference though that when you press a button in RsView, something happens in the real world :slight_smile:

Good - keep it that way. We are not trying to be a soft PLC - we are are big believers in real PLC control.

Rather satisfying, isn't it?

That's a very valid point. However, since you're obviously doing some work with a database with FactorySQL, next time you find yourself wishing you had a quick and easy way to visualize that data, think of FactoryPMI. There's nothing that says you can use both of them together- after all, FactoryPMI only talks to the database, so many customers actually install it side-by-side with their existing control systems and use it as an information platform. It's main features are perfect for this: quick and easy DB UI development, no-install clients, and no client licensing.

Just wanted mention that- it doesn't have to be one or the other. A very good portion of our sales come from people installing our system next to an existing one just to make data available more easily.

Regards,