B4J Question SOLVED - config.properties - Error in SQL sentence (INSTR function)

S

Serinter

Member
Licensed User
Longtime User
Hello!

I hope someone can help me because I've been having this problem for a couple of days that I don't know how to solve:

Is it possible to include the INSTR function in the JRDC2 statements?
I have also tried CONTAINS and it doesn't work either. Even the thrown error is even worse.


Thanks in advance and regards
 
Last edited:
Sort by date Sort by votes
S

Serinter

Member
Licensed User
Longtime User
aeric said:
You mean SQL commands?
SELECT * FROM MyTable WHERE Col LIKE ?
Click to expand...
Hi aeric!
Thanks for reply!

This is not working for me:
SELECT *' from vendedores WHERE VenActivo=true AND (VenNoVisibleCajas LIKE ? ) ORDER BY VenCodigo ASC
Error:
<tr><th>MESSAGE:</th><td>java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &apos;LIKE &apos;0&apos; ORDER BY VenCodigo ASC&apos; at line 1</td></tr>

Commands like this are not working in JRDC2
SELECT * FROM MyTable WHERE INSTR(VenNoVisibleCajas ,?)

In HeidySQL and MySQL works perfectly changing parameter (?)
Example:

SELECT * FROM MyTable WHERE INSTR(VenNoVisibleCajas ,'0')
works like a charm in HeidySQL

Thanks in advance again and forgive me for my poor english!
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
Serinter said:
SELECT *' from vendedores WHERE VenActivo=true AND (VenNoVisibleCajas LIKE ? ) ORDER BY VenCodigo ASC
Click to expand...
why you have a 'single quote' after SELECT * ? I guess it is a typo.

How about this?
SQL: 
SELECT INSTR(VenNoVisibleCajas, ?) AS Position FROM vendedores
The INSTR() function returns the position of the first occurrence of a string in another string.
Click to expand...

How do you pass the search string ? If you use the LIKE command, then

B4X: 
CreateCommand("SQL_SelectCommand", Array("%0%"))
 
Last edited:
Upvote 0
S

Serinter

Member
Licensed User
Longtime User

Hello again, aeric.

I apologize for the delay in answering you, but I could not do it before.

B4X: 
Serinter said:
SELECT *' from vendedores WHERE VenActivo=true AND (VenNoVisibleCajas LIKE ? ) ORDER BY VenCodigo ASC
why you have a 'single quote' after SELECT * ? I guess it is a typo.

Yes, he was indeed a typo, I'm sorry.

B4X: 
How do you pass the search string ? If you use the LIKE command, then

B4X:
CreateCommand("SQL_SelectCommand", Array("%0%"))

This has been the key to solving my problem!!

There are times when the trees do not let us see the forest, right?

I had been obsessed with modifying the SELECT statement (which there was no way to make it work correctly), when it was much easier to modify how to pass the search string.
Thank you very very very much for your help!!, you have opened my eyes to the error that I was making and indicated to me a better way.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…