LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pspang78

Hi there,

I manage to extract data from SQL Server but having difficulties update the table in SQL SERVER.

Which node should I use to INSERT / UPDATE into SQL SERVER?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

If you are only doing INSERTs then you could use just the DbStore node.

If you want to do UPDATEs, then you'll have to configure a DbExecute node.


Sample Code:

query="update mytable set myfield=:1 where somefield=:2"
sqlNonSelect(query,'myfield','somefield')

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pspang78

Here's some background ...

EXTRACT
I use the "JDBC Query" to retrieve sample data

TRANSFORM
I use multiple different nodes to manipulate the data

LOAD
Update the same table using the PK

I'm current stuck at the last leg (LOAD). I've tried using the following:

1. JDBC Execute

BRX Description
node:4d38fc59050615bb
type:binary
name:OCMS+v2%2E8%2ETest
inputut1=4d38dc2752a16485.out1
prop:ls.brain.node.doPreviews=1,0
prop:ls.brain.node.java.SQLRunner.SQLQuery=<<EOX
query = "UPDATE Appointments Set Subject=:1 where id=:2"
sqlNonSelect(query,Subject,Id)

EOX
prop:ls.brain.node.java.SQLRunner.DBUser=sa
prop:ls.brain.node.java.SQLRunner.DBPassword=maske d
prop:ls.brain.node.java.SQLRunner.DBURL=jdbc:sqlse rver://ppc-ss-01
prop:ls.brain.node.java.SQLRunner.DBDriver=com.ncr .teradata.TeraDriver
prop:ls.brain.node.java.nodeClass=com.lavastorm.br ain.node.SQLRunner
prop:binary=javaNode.py
end:4d38fc59050615bb

Error Log
Exited with non zero status

Installing log (01/21/2011 16:01:55) ...
2011-01-21 16:01:55; Level: 3; Type: 0; Desc: "error on row 0. com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '='."; File: "SQLRunner.java"; Line: 217; Context: "nodeMain"
Uninstalling log (01/21/2011 16:01:55) ...

Error: property ls.brain.node.java.logLevel set incorrectly (set to 2)Error: Allowed values are Critical, Error, Warning, Info, DebugError: Using Warning

2. DB Execute

BRX
node:4d38d924031470e9
type:binary
name:OCMS+v2%2E8%2EDB+Execute
input:Appointments=4d38dc2752a16485.out1
prop:ls.brain.node.doPreviews=1,0
prop:ls.brain.node.sqlRunner.OutputExprFile=<<EOX
;line 1
(bs-set "query" "UPDATE Appointments Set Subject=:1 where id=:2")
;line 2
(sql-non-select $query 'Subject' 'Id')
EOX
prop:ls.brain.node.sqlRunner.dbUser=sa
prop:ls.brain.node.sqlRunner.dbPassword=masked
prop:ls.brain.node.sqlRunner.dbService=ppc-ss-01
prop:ls.brain.node.sqlRunner.dbInterface=cli
prop:binary=nodeDbExec
end:4d38d924031470e9

Error Log
ERROR initializing expert output:
Unable to load library 'wincli32': The specified module could not be found.

Installing log (01/21/2011 15:56:23) ...
2011-01-21 15:56:23; Level: 2; Type: 0; Desc: "Unable to load library 'wincli32': The specified module could not be found."; File: "\src\brain_d\jigsaw\Code\Source\Misc\JLibrary.cpp "; Line: 125; Context: "JLibrary::OpenLibrary() - Win32 Impl"
2011-01-21 15:56:23; Level: 2; Type: 0; Desc: "...exception seen"; File: "\src\brain_d\ra_dev\code\source\expert\SqlExprUti ls.cpp"; Line: 163; Context: "Salmon::NED::SqlExprUtils::InitializeDB()"
2011-01-21 15:56:23; Level: 2; Type: 0; Desc: "...handling expression #2 in file"; File: "\src\brain_d\ra_dev\code\source\brain\ExprTableIo .cpp"; Line: 86; Context: "Salmon::NED::ExprTableIo::initialize()"
2011-01-21 15:56:23; Level: 2; Type: 0; Desc: "...exception seen"; File: "\src\brain_d\ra_dev\code\source\brain\NodeControl .cpp"; Line: 1152; Context: "Salmon::NED::NodeControl::initializeExpert()"
Uninstalling log (01/21/2011 15:56:23) ...

I suspect I didn't use "DB Execute" properly. Since I manage to extract data using "JBDC Query", I'm hoping to use "JDBC Execute" to update the records.

Can you please tell me where I did wrong?

The table has 6 fields - Id (int), Body (string), End (date), Location (string), Start (date), Subject (string)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pspang78

I manage to run with node without error (Green tick). However it didn't update the record in SQL Server.

Here's the query I use
sqlNonSelect("UPDATE Appointments Set Subject=:1 where id=:2",'Subject','Id')

Error Log
Installing log (01/21/2011 16:14:30) ...
Uninstalling log (01/21/2011 16:14:31) ...

Error: property ls.brain.node.java.logLevel set incorrectly (set to 2)Error: Allowed values are Critical, Error, Warning, Info, DebugError: Using Warning

Does that help?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

We have seen a similar behavior when connecting to SQL Server caused by permission issues. The issue we saw, the user got no errors on JDBC Execute, but errors on JDBC Store. The issue ended up being insufficient privileges on the account being used. Can you make sure you have an account with correct read/write privileges?

If you continue to have issues, please see this post which should help you work with our support organization to resolve your issue.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rabbott

Hi,

The JDBC Execute Node does not use BRAINScript. Try this:

UPDATE Appointments Set Subject = 'Subject' where Id = Id

Intuitively, one would think that the JDBC Execute node would work like the DB Execute node (i.e. use sqlSelect, sqlNonSelect, etc.), but it doesn't. You can see this if you compare the two Node Help files.

Sorry for the confusion and inconvenience. This usability issue has been reported to Product Development.

Regards,

- Bob

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Leaner

Over three years later, is this still the case?

I've got a 50+ column table that I need to update every night. Do I really need to write the whole SQL query to update the table? :'(

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

It depends on what you mean by "write the whole SQL query" and by "update every night".

If you use the JDBC Store node you don't have to write the SQL statement, it gets created automatically by the node and you never see it.

If you want to update various columns in a table, you have to put the logic telling the computer exactly what has to happen with which columns somewhere. And the way you do this is to write an SQL update statement specifying which columns get updated with which values on which records.

But there are shortcuts you can use to avoid keying in every single character in the SQL statement. You can use the lal1::Get Metadata node to get a list of the fields containing the input. You can do a query on the database table retrieving one record and then use this same Get Metadata node to give you the column names in the table. Then if you have large sections of that update statement that are very repetitious, you can use the Filter node to create those repetitive lines of code rather quickly. The Filter node would use the string concatenation operations and once created, you would copy those lines of code from the BRD Viewer into the Update node as you create the update statement.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: xathras

What are you trying to do exactly. We update all the time without issues

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Leaner

Hi Gents,

Thanks @ejones, I'm going to try your suggestion, will save me lots of time as it looks like I will need to use a statement.

What I am trying to do is to _update_ tables on one database with data from a different database.

Example:
Database 'A' has table 'foo', so I take table 'foo' data and process it with a bunch of filters. I then need to take that data and _update_ database 'B' table 'bar' with it.

I've got a whole bunch of tables that need this treatment on a nightly basis and most of them have 50+ columns. So I'll use Jetty server for the nightly job, but I was hoping not to have to write update queries to get the job done.

Ta.