revDataFromQuery : size limit of mySQL query ?

[Peter Haworth]

Thanks jbv.  Can't think of any better ways to do it so I guess just hope
there isn't a size limitation.

If you do run into a size limitation, another possibility might be to use a
temporary table to hold the names to be excluded then use a SELECT
something like:

SELECT name FROM Names LEFT JOIN tempnames ON tempnames.name=Names.name
WHERE tempnames.name IS NULL

But that would entail adding rows to the temp table and the processing time
that goes along with that.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Fri, Jun 6, 2014 at 10:18 PM, <jbv at souslelogo.com> 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...
>
> 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.
> >
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>