>//In firebird example database "employee" is table 'job'
>//with string[] column 'language_req'
>
>// i have some code:
>
>fbcmd.CommandText = "select language_req from job where job_code="SRep";
>fbreader = fbcmd.ExecuteReader ();
>
>Array array_value;
>
>if (fbreader.Read ())
>array_value = (Array)fbreader[0];
>fbreader.Close ();
>
>fbcmd.CommandText = "select * from job where language_req = @par1";
>fbcmd.Parameters.AddWithValue ("@par1",array_value);
>fbreader = fbcmd.ExecuteReader (); // ERROR -> Cannot cast from source type to
>destination type
>
>//is this code wrong ?
>//what is solution for searching table by array values ?
Hi Arthur!
I don't quite understand your question, but if you want your query to check for
multiple values, then you cannot use '='. The standard way to do this is using
IN, i.e.
select * from job where language_req in (@par1, @par2, @par3...
The PLAN of a query is determined at prepare time (before execution) and you
ought to know the (maximum) number of parameters in advance, one parameter
cannot contain more than one value (though it is possible to circumvent by
using EXECUTE BLOCK containing EXECUTE STATEMENT, I will not show you how since
it is normally a suboptimal solution).
What I think I would recommend you, is to change your query a bit and not use
parameters at all (or just use it for job_code):
select *
from job j1
where exists(select * from job j2
where j2.job_code = 'SRep'
and j1.language_req = j2.language_req)
Or it might be that
select distinct j1.*
from job j1
join job j2 on j1.language_req = j2.language_req
where j2.job_code = 'SRep'
(distinct may or may not be what you want)
could be even better - it depends a bit on how your data looks, EXISTS works
fine on small tables (use EXISTS with 1000 rows, consider the JOIN variant if
it can be used and you have more than 100000 rows) as long as you have an index
for job_code.
If it is a big table, you could even consider:
EXECUTE BLOCK RETURNS(ID INTEGER = ?, LANGUAGE_REQ INTEGER = ?, JOB_CODE
VARCHAR(10) = ?, <other output fields>) AS
DECLARE VARIABLE LANG_REQA INTEGER --Change to other type if required
BEGIN
FOR SELECT DISTINCT LANGUAGE_REQ
FROM JOB
WHERE job_code = 'SRep'
INTO :LANG_REQA DO
BEGIN
FOR SELECT ID, LANGUAGE_REQ, JOB_CODE, <other output fields>
FROM JOB
WHERE LANGUAGE_REQ = :LANG_REQA
INTO :ID, :LANGUAGE_REQ, :JOB_CODE, <other output fields> DO
SUSPEND;
END
END
(if you want duplicate rows to appear as separate rows, you cannot use
DISTINCT, whereas EXECUTE BLOCK can be used).
HTH,
Set
