Android Question RDC select in(multiple values)

[Filipe Ferreira]

Good afternoon,

I'm using RDC and need to do the following query on the sql server:

select * from cativos where stamp in ('stamp_1','stamp_2','stamp_3','stamp_n')

to get multiple results:

and i'm using this code:

'........................................ RECALCULAR TAMANHO ARRAY .........................
Dim STAMP_LINHA As String
Dim LISTA_1 As List=st.StringArrayToList (stamps_etiqueta,True,False)
For I = 0 To LISTA_1.Size-1
If LISTA_1.GET(I)="" Then Exit
If STAMP_LINHA="" Then
STAMP_LINHA=LISTA_1.GET(I)   
Else
STAMP_LINHA= STAMP_LINHA & "','" & LISTA_1.GET(I)   
End If
Next
ToastMessageShow(STAMP_LINHA,False)
'........................................ RECALCULAR TAMANHO ARRAY .........................


Dim cmd As DBCommand
cmd.Initialize
cmd.Name = "VERIFICA_CATIVOS_PREENCHIDOS"
cmd.Parameters = Array As Object(STAMP_LINHA)
reqmanager_ARM.ExecuteQuery(cmd,0,"receber_linhas_verifica_cativos")

But in sql profiler im receiving this:

exec sp_execute 1,N'B135-10100010001700586148'',''A135-VA51-1-N002170059314'',''A135-92006217006171489059'

my question is:
Why am i receiving double quotes ('') and not just a single quote (').

thanks in advanced.

 

[sorex]

shouldn't it be

If LISTA_1.GET(I)="" Then Exit
If STAMP_LINHA="" Then
STAMP_LINHA="'" & LISTA_1.GET(I) &"'" '<- wrap first item in quotes
Else
STAMP_LINHA= STAMP_LINHA & ",'" & LISTA_1.GET(I) &"'"  '<- close single item quote
End If

?

never used RDC myself so I don't know how it treats that object

 

[Filipe Ferreira]

hello Sorex,

thanks for the quick response,

The problem is not in the beginning or in the end of the string, the problem is the double quote in the middle.

with your response i got

exec sp_execute 1,N'''B135-10100010001700586148'',''A135-VA51-1-N002170059314'',''A135-92006217006171489059'''

still the double quote and in the beginning and int the end triple quote...

 

[sorex]

then it is escaping the quotes for you. maybe you need to feed it as an array somehow?

 

[Erel]

How is the query defined in the server?

 

[JCO]

Like Sorex above, I think there's a problem in the way you are constructing your string.
What does your

ToastMessageShow(STAMP_LINHA,False)

show?

 

[Filipe Ferreira]

How is the query defined in the server?

Good afternoon Erel,

in the server i have:

sql.VERIFICA_CATIVOS_PREENCHIDOS = SELECT * from CATIVOS where stamp_linha in (?)

 

[Filipe Ferreira]

Like Sorex above, I think there's a problem in the way you are constructing your string.
What does your

ToastMessageShow(STAMP_LINHA,False)

show?

Good afternoon JCO,

the

ToastMessageShow(STAMP_LINHA,False)

shows:

'stamp_a','stamp_b','stamp_c','stamp_N'

and in sql server it works like a charm, but i keep receiving on sql profiler double quotes.

'stamp_a'',''stamp_b'',''stamp_c'',''stamp_N'

 

[JCO]

When you declare:

sql.VERIFICA_CATIVOS_PREENCHIDOS = SELECT * from CATIVOS where stamp_linha in (?)

the server expects you to send ONE parameter value. You are sending

'stamp_a','stamp_b','stamp_c','stamp_N'

So, the server interprets it as a single value and then escapes your quotes to filter for a literal value of "'stamp_a','stamp_b','stamp_c','stamp_N'"

 

[sorex]

that's what I meant with the array part. I guess you'll need to use ... in (?,?,?,?) and submit the individual values to it or as array.

 

[Erel]

See my answer here: https://www.b4x.com/android/forum/threads/rdc-query.77007/#post-487973

 

[Filipe Ferreira]

Sorex, Erel, and JCO thanks for your help,

However I was unable to implement your solutions,
because i don't know how many values i have to send to sql server,
which is why i decided to move forward with another solution.

Anyway, thank you very much for your support.