revDataFromQuery : size limit of mySQL query ?

[jbv at souslelogo.com]

Mark & Pete

I'll try to be more specific although I'll symplify the problem
to keep my explanations below as simple as possible :

- step 1 : various client apps update a DB via cgi requests by
telling the server what references they have at a specific time

- step 2 : the server checks what's in the DB and returns each
app a list of references that were not in the list it received from
each app

in step 1, some lists can be quite long; so I had to switch from
GET cgi requests such as
   get URL "http://myDomain/submitList.lc?myID_ref1_ref2_ref3"
to POST requests because of the length limits of GET requests
that would truncate the longest of them.
That's why I was wondering if similar problems would occur
in step 2 when the server checks for references in the DB that
aren't in the list submitted by each app.

Of course, I can write the script as follows :
   -- put the refs send by the app in an array myTrefs
   put "SELECT ref FROM myDB into myREQUEST
   put revDataFromQuery(,,theID,myREQUEST,) into myRefs
   repeat for each line j in myRefs
      if myTrefs[j] is empty then
         -- some processing
      end if
   end repeat

But being able to do that with a single mySQL request such as
   put "SELECT ref FROM myDB WHERE ref != ref1 AND ref != ref2 into myREQUEST
   put revDataFromQuery(,,theID,myREQUEST,) into myRefs

would save some processing time, providing that I don't bump
into request size limits as in GET cgi requests...

Last but not least, obviously in that case I can't select what I'm looking
for as Mark suggested. I don't think a LIKE statement would work either
as references are all unique strings of 30 alphanumeric chars...

Thanks for your time.
jbv


> Pete-
>
> Friday, June 6, 2014, 4:35:11 PM, you wrote:
>
>> Ah OK, sorry should have read more closely.
>
>> Don't know the answer to that one but if there is a limit the NOT IN
>> thing
>> I suggested would cut down on the length of the SELECT statement since
>> there are no "AND" operators in it.
>
> Well, the NOT IN clause can select from an embedded SELECT statement
> to further limit the selections, but I would wonder whether such a
> complicated statement would be necessary in the first place. Not that
> I know what jbv has in mind, but I would think that possibly selecting
> on what you're looking for rather than what you're *not* looking for
> might be a shorter select statement. Or selecting on some other
> criterion or using a LIKE selector might do the trick.
>