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

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:

Serinter

Member
Licensed User
Longtime User
You mean SQL commands?
SELECT * FROM MyTable WHERE Col LIKE ?
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
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.

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.

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

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

Serinter

Member
Licensed User
Longtime User
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


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

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

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
Top