2

Got error with PUTSQL with insert statement in replace text processor in nifi, and its showing following error in putSQL " routing to failure: com.microsoft.sqlserver.jdbc.SQLServerException: The index 4 is out of range."

sam
  • 151
  • 1
  • 1
  • 4

2 Answers2

1

@sam your insert query is perfect and

Following cases are may be reason for your issue.

i.)please ensure the SQL Connection have table "yahoo". And also ensure that your sql connection string have "database" name like below.

jdbc:microsoft:sqlserver://<host>:<port>;DatabaseName=DATABASE

ii.)If you give insert query like below,

insert into yahoo (ID, Name, Rate, Time, Ask, Bid, Date) values ('a', 'a', '1', 'a', '1', 'a', 'a')

Then you should have all columns in yahoo table in varchar or char or text type only.

iii.)If any one of columns in yahoo is "int" you have remove the single quotes like below.

For example in 3rd column of yahoo table "Rate" is an integer you have to change insert query like below.,

insert into yahoo (ID, Name, Rate, Time, Ask, Bid, Date) values ('a', 'a', 1, 'a', '1', 'a', 'a')

(removed single quotes in 3rd column Rate).

let me know above changes worked for you.

Mister X
  • 3,406
  • 3
  • 31
  • 72
  • Thank you. Now that data flow is working. I have one more issue with date , datetime, varbinary column's. How should i transfer these columns from DB to DB using nifi? – sam Feb 21 '17 at 14:49
  • If my answer work for you then feel free to upvote or accept as answer.You can use two connectionpool for two databases and then transfer columns possible – Mister X Feb 22 '17 at 04:41
1

@sam, regarding your issue with date fields, it could be related to https://issues.apache.org/jira/browse/NIFI-2625. I have faced similar kind of problem abd discussed the same here PutSql - date format error

Community
  • 1
  • 1
Pons
  • 1,101
  • 1
  • 11
  • 20