revDataFromQuery : size limit of mySQL query ?

[Mark Wieder]

jbv-

Friday, June 6, 2014, 10:18:58 PM, you wrote:

> 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...

Ah. I was basing my thoughts on your original example.

In step 1, I don't think there are limits to postable data. YMMV, but
I recently had to deal with some 84MB posts and responses. Aside from
having to set db network timeouts for the expected data, we had no
problems (mind you, fiddling with the network timeouts was a painful
experience).

>From your description, it seems as though the server and db are on the
same machine. In that case, the LC code on the server would be doing
the database queries directly and there would be no http calls
involved in step 2 (aside from sending the results).

...and I would still use the form
SELECT ref FROM myDB WHERE ref NOT IN (ref1, ref2,...)

-- 
-Mark Wieder
 ahsoftware at gmail.com

This communication may be unlawfully collected and stored by the National 
Security Agency (NSA) in secret. The parties to this email do not 
consent to the retrieving or storing of this communication and any 
related metadata, as well as printing, copying, re-transmitting, 
disseminating, or otherwise using it. If you believe you have received 
this communication in error, please delete it immediately.