JDBC Sender - Wrong SELECT statement (Oracle driver)

[Former Member]

Hi,

3 days ago I was having a problem with my JDBC Sender adapter [; .

Finally I discovered what's wrong but I can't understant why is not working. When I deleted the "WHERE" condition in my SELECT statement all worked OK (except that I need the WHERE statement...).

Why that statement is not working in my JDBC Sender:

SELECT * FROM MATERIALES WHERE LEIDO<>'X'

and that statement works:

SELECT * FROM MATERIALES

The field "LEIDO" is my flag that I must set to X when I read with the UPDATE statement, so I need the where condition...

[dharamveer_gaur2 · Accepted solution]

First test your select statement with where condtion on SQL Editor.

change your select statement, make it

SELECT * FROM MATERIALES WHERE LEIDO = 'X'

[Former Member · Accepted solution]

Sorry, SDN is not working very well today and I couldn't edit my mistake in the main thread.

My SQL statement is

SELECT * FROM MATERIALES WHERE LEIDO \<\> 'X'

Without the '\' but the forum doesnt show the unequal symbol...

I've tested the Statement in my SQL editor and it works fine... what can it be?

[Former Member · Accepted solution]

Hello,

I may be wrong, but I'm not sure you need to escape the "<" and ">" symbols when entering the SQL statement in the adapter config screen. I think it will be automatically handled within adapter logic ... But if it does not seem to work that way, try using sthg like "... WHERE field != value", I think it may work too

Rgds

Chris

[Former Member · Accepted solution]

Thanks Christopher but still not working

I tried two new statements:

1 - Escaping: SELECT * FROM MATERIALES WHERE LEIDO <> 'X'

2 - Changing the operator symbol: SELECT * FROM MATERIALES WHERE LEIDO != 'X'

The nº1 gives me an error of invalid character

The nº2 has the same effect than the other symbol, the adapter reads from the DB but no XML message is generated and no payload to check what's happening... just a message ID without information and without a message generated in SXMB_MONI

[Former Member · Accepted solution]

Did not work but did not raise an error regarding invalid chars or similar ? If so, that could mean the cause is somewhere else ... Can you see the msg (and its audit log) at the AFW level using the message display tool ?

Chris

Edited by: Christophe PFERTZEL on Nov 10, 2008 4:33 PM

[Former Member · Accepted solution]

Resolved.

I don't know what's happening but there is a problem with the field "LEIDO". That field has been added to the table after the creation (but before entering test data...). And if I execute the statement with SQL client I don't have any problem, but the JDBC driver don't recognize that field or something.

There aren't errors about inexistent column but I've tried the where condition with another column and all runs OK. I think I just have to delete and rebuild the DB table.

Thanks for your help, I regarded you some points

[Former Member · Accepted solution]

Marshal,

Your "added" column may have been populated with "null" values, and, in ORACLE, in order to select such values you need to specifically use the "is null" clause. So try to add this in your query:

SELECT * FROM your_table WHERE leido <> 'X' or leido is null

This may work, so you won't have to rebuild table

Chris

[Former Member · Accepted solution]

HI Marshal

Is this field added after you use the table in PI interface. If yes then you can try deactivating the JDBC channel -> then refresh the cache and again activate.

This will work.

You dont have to re create the table.

Thanks

Gaurav

[Former Member · Accepted solution]

Thanks Christophe! It worked!!!

Looks like NULL is not the same of NON EQUAL to X for Oracle....

To Gurav: Yes, I know that this usually resolves the problems but I restarted the channel a lot of times before asking to SDN, thanks anyway

[Former Member]

Hi Marshal,

I think it's a problem with the bigger then and smaller then signs.

Unfortunately Oracle did not understand EQ, NE, etc commands, also the != could not be processed by Oracle.

So I would suggest to test this statement:

SELECT * FROM MATERIALES WHERE LEIDO not in ('X')

To use the not in command gives you not the best performance but I think this should solve your Problem!

Regards,

Björn

[prateek]

Instead of you statement

SELECT * FROM MATERIALES WHERE LEIDO'X'

Use this

SELECT * FROM MATERIALES WHERE LEIDO = 'X'

Regards,

Prateek

[Former Member]

in

select Statment give as

SELECT * FROM MATERIALES WHERE LEIDO EQ 'X'

in Update statement

UPDATE MATERIALES SET LEIDO EQ 'Y" WHERE LEIDO EQ 'X'