It is currently Wed Jul 30, 2014 5:09 am




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 
 Syntax for database name MSSQL 
Author Message
Corporal
Corporal

Joined: Fri Jun 29, 2012 6:10 am
Posts: 36
Post Syntax for database name MSSQL
Need syntax to do an Insert query to a Table that is not on the Default database:

Insert into [Ignition_PLU].[dbo].[Scrap_Scales] (Scrap_Lbs, etc)

Query works if I use the default database, but I need to use a different Database than the default.


Fri Jun 29, 2012 6:16 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Syntax for database name MSSQL
If you are using a SQL query for a property binding, SQL tag, or something similar, you will be provided a dropdown box with the list of database connections to choose. If you're scripting, each of the scripting functions can take the name of the database connection as a parameter. To do any operations on a database, each database connection must be properly set up in the Ignition Gateway.

For information about scripting and database configurations take a look at our online manual:

http://inductiveautomation.com/support/ ... /ignition/

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Fri Jun 29, 2012 7:21 am
Profile
Trooper
Trooper

Joined: Sat Aug 02, 2008 6:03 pm
Posts: 14
Post Re: Syntax for database name MSSQL
I have a button and I want to insert Data to 2 tables on 2 different servers. The default database works good. but the non-default table fails.


Fri Jun 29, 2012 7:27 am
Profile
Moderator
Moderator

Joined: Mon Jun 04, 2012 8:53 am
Posts: 440
Post Re: Syntax for database name MSSQL
Bassafish, please post your topic in a new thread. Even though your problem is similar, it can be confusing to the original poster if there are other conversations going on in a single thread. It also keeps things organized on our end, having one problem per thread is the easiest way for our tracking system to handle the forum. Also, it also makes "solving" a thread more difficult if there are multiple users with different questions. Thanks.

_________________
James Lorenz
QA/Build Systems Engineer
Inductive Automation


Fri Jun 29, 2012 7:42 am
Profile
Corporal
Corporal

Joined: Fri Jun 29, 2012 6:10 am
Posts: 36
Post Re: Syntax for database name MSSQL
yes I am using a script. I want to insert data into 2 tables (Same name) but they are on 2 different servers. They both work if they are selected as the default database connection.

Is it possible to change the database connection in the script?


Fri Jun 29, 2012 7:48 am
Profile
Moderator
Moderator

Joined: Mon Oct 27, 2008 4:10 pm
Posts: 1980
Location: Folsom, CA
Post Re: Syntax for database name MSSQL
Jerry_H wrote:
yes I am using a script. I want to insert data into 2 tables (Same name) but they are on 2 different servers. They both work if they are selected as the default database connection.

Is it possible to change the database connection in the script?


Yes, the system.db scripting functions all take an optional database name parameter. See the user manual for more info.

_________________
Kevin Herron
Software Developer
Inductive Automation


Fri Jun 29, 2012 7:51 am
Profile WWW
Corporal
Corporal

Joined: Fri Jun 29, 2012 6:10 am
Posts: 36
Post Re: Syntax for database name MSSQL
I see that in the manual, but I what is the Syntax for MSsql. The manual does not have any examples on how to switch servers and database connections.

Examples only show the table name.

userText = event.source.parent.getComponent("TextArea").text
userName = system.security.getUsername()
system.db.runPrepUpdate("INSERT INTO Comments (Name, UserComment) VALUES (?,?)", [userName, userText])


Fri Jun 29, 2012 8:02 am
Profile
Moderator
Moderator

Joined: Mon Oct 27, 2008 4:10 pm
Posts: 1980
Location: Folsom, CA
Post Re: Syntax for database name MSSQL
There's no special syntax, it's just another parameter in the scripting call.

Code:
system.db.runUpdateQuery("INSERT INTO RecipeSteps (StepNum, Gallons) VALUES (%d, %f)" % (nextStepNum, gallons))


vs.

Code:
system.db.runUpdateQuery("INSERT INTO RecipeSteps (StepNum, Gallons) VALUES (%d, %f)" % (nextStepNum, gallons), "the_name_of_my_db_con")

_________________
Kevin Herron
Software Developer
Inductive Automation


Fri Jun 29, 2012 8:11 am
Profile WWW
Corporal
Corporal

Joined: Fri Jun 29, 2012 6:10 am
Posts: 36
Post Re: Syntax for database name MSSQL
Kevin
Below is the actual script I am trying to run. I added the db connection at the end as shown in your example above, it failed. The script works if I run it on the default database connection. iI need to run it on another server database connection which is "Ignition_PLU".


fpmi.db.runPrepStmt("INSERT INTO Scrap_Scales (Mac_Code,Job,DtCategoryCode,DTCode,ScrapLbs,Source_Ref,REP_TIME) VALUES (?,?,?,?,?,?,?)",
[Mac_Code,Job,DtCategoryCode,DTCode,ScrapLbs,Source_Ref,REP_TIME],"Ignition_Plu")


Fri Jun 29, 2012 10:16 am
Profile
Moderator
Moderator

Joined: Mon Oct 27, 2008 4:10 pm
Posts: 1980
Location: Folsom, CA
Post Re: Syntax for database name MSSQL
Are you using FactoryPMI or Ignition? What's the error you are getting?

Edit: I see you're using the deprecated runPrepStmt() call, which needs a 3rd Object[] args parameter after the datasource...

_________________
Kevin Herron
Software Developer
Inductive Automation


Fri Jun 29, 2012 10:43 am
Profile WWW
Corporal
Corporal

Joined: Fri Jun 29, 2012 6:10 am
Posts: 36
Post Re: Syntax for database name MSSQL  Topic is solved
Using Ignition, but running some converted scripts from PMI.

Ok ! Got it, Works good now. Both Databases and servers are updating properly.

Thanks for your help !


Fri Jun 29, 2012 10:51 am
Profile
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 11 posts ] 


Who is online

Users browsing this forum: Google [Bot], IronYeti and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: