Last week, I was asking about how to use a variable list for embedded SQL
in the form " SELECT ... WHERE foo IN (:bar) ". I got a suggestion of a
couple of possibilities thanks to Elvis.
Elvis Budimlic wrote on 13/09/2007 17:07:11:
Some options, in order of personal preference:
1) Dynamic SQL
2) list each element (you can use host variables for elements in the
list)
3) create an array to be used for the IN clause. Make the array fit
your
max elements in the list. Use the array in your static SQL, listing all
elements in the IN clause. For the case where you don't have enough
real
elements to fill the array, just repeat the last element till your reach
the
max.
I decided to go the Dynamic SQL route. I'd like to do something like this
http://code.midrange.com/index.php?id=4b02152c13. However, I can't seem
to get the parameter marker version to work - I always get a
SQLSTATE=02000, no rows selected. I've found some posts suggesting that
other people have tried the same thing, but no solutions. Is this
possible if I find the right incantation?
On the other hand, this
http://code.midrange.com/index.php?id=c32e3b297a
works just fine.
In this case, I'm expecting to only PREPARE statement once per job, so the
difference between using a parameter marker and concatenating in the
values shouldn't be appreciable, as far as I understand it. It would be
nice to know for future reference if this type of query is possible with a
parameter marker.
Attention:
The information contained in this message and or attachments is
intended only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon, this
information by persons or entities other than the intended recipient is
prohibited. If you received this message in error, please contact the sender and
delete the material from any system and destroy any copies. Thank you for your
time and consideration.
Attention:
Le contenu de ce message et(ou) les fichiers ci-joints s?adressent
exclusivement à la personne ou -entité à laquelle ils sont destinés. Ils peuvent
contenir de l?information confidentielle, protégée et(ou) classifiée. Il est
strictement interdit à toute personne ou entité autre que le(la) destinataire
prévu(e) de ce message d?examiner, de réviser, de retransmettre ou de diffuser
cette information, de prendre une quelconque action en fonction ou sur la base
de celle-ci, ou d?en faire tout autre usage. Si vous avez reçu ce message par
erreur, veuillez communiquer avec l?expéditeur(trice), supprimer ce message et
les fichiers ci-inclus de tout système, et en détruire toutes copies, qu?elles
soient électroniques ou imprimées. Nous vous remercions de votre entière
collaboration.
midrange.com
