Re: revDataFromQuery : size limit of mySQL query ?

2014-06-07 Thread 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, j...@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@lists.runrev.com
 Please visit this url to subscribe, unsubscribe and manage your
 subscription preferences:
 http://lists.runrev.com/mailman/listinfo/use-livecode

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-07 Thread 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
 ahsoftw...@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.


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-06 Thread Peter Haworth 
I haven't come across a limit and I've certainly created some very large
SELECT results but I guess there must be one at some point.
If you run into one, you can probably get round it by using
revQueryDatabase to create a cursor although that would probably mean
rewriting a lot of your code.

One other unrelated suggestion.  I don't know if it's any more/less
efficient but you might try NOT IN ('John','Ted,...) in your SELECT
statement

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 12:52 PM, j...@souslelogo.com wrote:

 Hi list,

 Does anyone know if there is a limit in the length of the sql query
 when using revDataFromQuery ?
 According to what I found on the web, there doesn't seem to be
 any problem on mySQL side (default value is apparently 1 Mb),
 but I was more concerned by a possible buffer between LC and
 mySQL where the query could be truncated...

 Furthermore, I am fully aware that very long queries are hard
 to maintain and might lead to slow execution...
 What I have in mind in some rather simpler stuff like :
 SELECT name FROM myDB WHERE name != 'John' AND name != 'Ted'...
 where the list of names can be quite long...

 Thanks,
 jbv


 ___
 use-livecode mailing list
 use-livecode@lists.runrev.com
 Please visit this url to subscribe, unsubscribe and manage your
 subscription preferences:
 http://lists.runrev.com/mailman/listinfo/use-livecode

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-06 Thread jbv 
Thanks fr the reply.
Actually I'm pretty confident about the results of the query, as I have
created very large results myself.
My concern was more with the query itself...

jbv

 I haven't come across a limit and I've certainly created some very large
 SELECT results but I guess there must be one at some point.
 If you run into one, you can probably get round it by using
 revQueryDatabase to create a cursor although that would probably mean
 rewriting a lot of your code.

 One other unrelated suggestion.  I don't know if it's any more/less
 efficient but you might try NOT IN ('John','Ted,...) in your SELECT
 statement

 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 12:52 PM, j...@souslelogo.com wrote:

 Hi list,

 Does anyone know if there is a limit in the length of the sql query
 when using revDataFromQuery ?
 According to what I found on the web, there doesn't seem to be
 any problem on mySQL side (default value is apparently 1 Mb),
 but I was more concerned by a possible buffer between LC and
 mySQL where the query could be truncated...

 Furthermore, I am fully aware that very long queries are hard
 to maintain and might lead to slow execution...
 What I have in mind in some rather simpler stuff like :
 SELECT name FROM myDB WHERE name != 'John' AND name != 'Ted'...
 where the list of names can be quite long...

 Thanks,
 jbv


 ___
 use-livecode mailing list
 use-livecode@lists.runrev.com
 Please visit this url to subscribe, unsubscribe and manage your
 subscription preferences:
 http://lists.runrev.com/mailman/listinfo/use-livecode

 ___
 use-livecode mailing list
 use-livecode@lists.runrev.com
 Please visit this url to subscribe, unsubscribe and manage your
 subscription preferences:
 http://lists.runrev.com/mailman/listinfo/use-livecode




___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-06 Thread Peter Haworth 
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.


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 3:06 PM, j...@souslelogo.com wrote:

 Thanks fr the reply.
 Actually I'm pretty confident about the results of the query, as I have
 created very large results myself.
 My concern was more with the query itself...

 jbv

  I haven't come across a limit and I've certainly created some very large
  SELECT results but I guess there must be one at some point.
  If you run into one, you can probably get round it by using
  revQueryDatabase to create a cursor although that would probably mean
  rewriting a lot of your code.
 
  One other unrelated suggestion.  I don't know if it's any more/less
  efficient but you might try NOT IN ('John','Ted,...) in your SELECT
  statement
 
  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 12:52 PM, j...@souslelogo.com wrote:
 
  Hi list,
 
  Does anyone know if there is a limit in the length of the sql query
  when using revDataFromQuery ?
  According to what I found on the web, there doesn't seem to be
  any problem on mySQL side (default value is apparently 1 Mb),
  but I was more concerned by a possible buffer between LC and
  mySQL where the query could be truncated...
 
  Furthermore, I am fully aware that very long queries are hard
  to maintain and might lead to slow execution...
  What I have in mind in some rather simpler stuff like :
  SELECT name FROM myDB WHERE name != 'John' AND name != 'Ted'...
  where the list of names can be quite long...
 
  Thanks,
  jbv
 
 
  ___
  use-livecode mailing list
  use-livecode@lists.runrev.com
  Please visit this url to subscribe, unsubscribe and manage your
  subscription preferences:
  http://lists.runrev.com/mailman/listinfo/use-livecode
 
  ___
  use-livecode mailing list
  use-livecode@lists.runrev.com
  Please visit this url to subscribe, unsubscribe and manage your
  subscription preferences:
  http://lists.runrev.com/mailman/listinfo/use-livecode
 



 ___
 use-livecode mailing list
 use-livecode@lists.runrev.com
 Please visit this url to subscribe, unsubscribe and manage your
 subscription preferences:
 http://lists.runrev.com/mailman/listinfo/use-livecode

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-06 Thread Mark Wieder 
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.

-- 
-Mark Wieder
 ahsoftw...@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.


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: revDataFromQuery : size limit of mySQL query ?

2014-06-06 Thread jbv 
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@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode