dc-pico

Citect...text string to sql

7 posts in this topic

Hi all,

Needing an assist. We been trying to send a local var alphanumeric string to an Access DB using the SQL.

We can successfully send integer/real values to the DB when they are entered via the var=FormNumPad() popup. I use SQLSet, SQLAppend, and SQLExec.

Since we cannot generate text using FormNumPad(), we use the var=Input() command. The local var seems to hold the value entered on the screen, but a plc address writes the value for a moment then wipes it out.

Then when we use the same SQL commands for the string var, the system immediately starts throwing errors. My guess is because the value is null.

Any ideas? Is there a proper way to generate a keypad or textbox on screen?

We are using v. 2016 on a Win10 box.

Thanks in advance.

Share this post


Link to post
Share on other sites

First create cicode file and put these code below into it.

INT hTextForm = -1;
STRING sTextValue = "";

STRING
FUNCTION
FnMenu_FormInput(STRING sTitle, STRING sInput)

sTextValue = sInput;

IF hTextForm = -1 THEN
hTextForm = FormNew(sTitle, 30, 2, 1);
	IF hTextForm > -1 THEN
	FormInput(0, 0, "", sTextValue, 20);
	FormButton(1, 1, "&OK", 0, 1);
	FormButton(10, 1, "&Cancel", 0, 2);
	FormRead(0);
	END
END

hTextForm = -1;
RETURN sTextValue;

END

then create string tag (this example use sText1) and place in graphic page

after that create button select input tab and put this function into up command

sText1 = FnMenu_FormInput("Text", sText1)

You can change sText1 to any string tag do you need. after finish save compile and run.

When you click button, text form input will be appear on screen if you insert string to it and click "OK" tag sText1 will change to string that you put but if you click "Cancel" tag will be no change

FormInput_01.jpg

Share this post


Link to post
Share on other sites

This a really helpful start to get the form working. Since that part is working successfully, it solves the problem of entering text and having it held in the plc. Thank you!

The next part that I need to get working is the database part.

I'm still getting an error when I try to write to the Access .mdb using the SQL.

So for instance in cicode:

FUNCTION foo()
INT hsql;

hsql = SQLConnect("DSN=foo_database");
SQLSet(hsql,"INSERT INTO foo_table(foo1, foo2, foo3) VALUES(");
SQLAppend(hsql, (foo3) + ","); //foo3 is a plc integer value
SQLAppend(hsql, (foo2) + ","); //foo2 is a plc integer value
SQLAppend(hsql, (foo) + ")");  //foo is a plc string

SQLExec (hsql, "");
SQLDisconnect (hsql); 
END

If the value of foo is "123ABC", SQLError outputs this message:

Quote

"SQL Error (4 - Database error) on Handle (1). ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1..

If I change the value of foo to "15", the database records the values without issue.

 

Any additional insights will be appreciated!

 

Share this post


Link to post
Share on other sites
8 hours ago, dc-pico said:

This a really helpful start to get the form working. Since that part is working successfully, it solves the problem of entering text and having it held in the plc. Thank you!

The next part that I need to get working is the database part.

I'm still getting an error when I try to write to the Access .mdb using the SQL.

So for instance in cicode:


FUNCTION foo()
INT hsql;

hsql = SQLConnect("DSN=foo_database");
SQLSet(hsql,"INSERT INTO foo_table(foo1, foo2, foo3) VALUES(");
SQLAppend(hsql, (foo3) + ","); //foo3 is a plc integer value
SQLAppend(hsql, (foo2) + ","); //foo2 is a plc integer value
SQLAppend(hsql, (foo) + ")");  //foo is a plc string

SQLExec (hsql, "");
SQLDisconnect (hsql); 
END

 

first try to change (foo) in cicode to '123ABC' (with ' symbol) and check result if it is not error you should add '  symbol in your cicode

FUNCTION foo()
INT hsql;

hsql = SQLConnect("DSN=foo_database");
SQLSet(hsql,"INSERT INTO foo_table(foo1, foo2, foo3) VALUES(");
SQLAppend(hsql, (foo3) + ","); //foo3 is a plc integer value
SQLAppend(hsql, (foo2) + ","); //foo2 is a plc integer value
SQLAppend(hsql, '123ABC' + ")");  //foo is a plc string

SQLExec (hsql, "");
SQLDisconnect (hsql); 
END

 

Share this post


Link to post
Share on other sites

Hi,

We have implemented the following code and it works with local string variables. The ' symbol was the problem. Thank you.

FUNCTION foo()
INT hsql;

hsql = SQLConnect("DSN=foo_database");
SQLSet(hsql,"INSERT INTO foo_table(foo1, foo2, foo3) VALUES(");
SQLAppend(hsql, (foo3) + ","); //foo3 is a plc integer value
SQLAppend(hsql, (foo2) + ","); //foo2 is a plc integer value
SQLAppend(hsql, "'"+ (foo) + "')");  //foo is a plc string

SQLExec (hsql, "");
SQLDisconnect (hsql); 
END

And we thought that the problem with writing a string to the plc was resolved but the problem remains.

We have created a string variable in the plc using Unity Pro. Then we import the tag over to Citect and do what you suggested before using cicode.

On 9/10/2018 at 10:07 AM, Wasan said:

sText1 = FnMenu_FormInput("Text", sText1)

It does not appear that the entered string '123ABC' in sText1 ever gets written to the plc when we watch the animation table.  Sometimes the entered string will flash on the HMI screen for just a moment, but then it goes blank immediately.

Any other ideas would be appreciated! Thank you.

Share this post


Link to post
Share on other sites
16 minutes ago, dc-pico said:

And we thought that the problem with writing a string to the plc was resolved but the problem remains.

We have created a string variable in the plc using Unity Pro. Then we import the tag over to Citect and do what you suggested before using cicode.

It does not appear that the entered string '123ABC' in sText1 ever gets written to the plc when we watch the animation table.  Sometimes the entered string will flash on the HMI screen for just a moment, but then it goes blank immediately.

Any other ideas would be appreciated! Thank you.

Maybe you should create empty Unity Pro project then create animation table for string then input string from Citect and check result.

Share this post


Link to post
Share on other sites

Yes. That's a good idea. We did create a new Unity Pro project to check the string from a new Citect project. The problem was still there. Just like before, the string could be added to the animation table from Unity and shown in Citect, but not the other way around with the input popup.

After much additional testing and trial and error, we installed OFS 3.60 SP3 which has fixed this issue with the writing strings to the PLC from Citect.

Wasan, thank you so much for your help!

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