4D SQL Question

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

4D SQL Question

4D Tech mailing list
SQL newbie here...

What is the correct syntax for deleting a number rows in a SQL database?  Is it possible to use a 4D array which contains the values used by the WHERE clause?

Or, do we have to delete each row individually?

Thanks!


Steve

*********************************************
  Stephen J. Orth                                                
  The Aquila Group, Inc.         Office:  (608) 834-9213
  P.O. Box 690                           Mobile:  (608) 347-6447
  Sun Prairie, WI 53590

  E-Mail:  [hidden email]
*********************************************


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
DELETE FROM users
WHERE id in [1,2.3];

would delete the three users with one of those ids (or none at all, if the ids didn’t exist.)

DELETE FROM users
WHERE last_name=“Smith”;

would delete all users with the last_name of Smith.

> On Jan 22, 2018, at 9:06 AM, Stephen J. Orth via 4D_Tech <[hidden email]> wrote:
>
> SQL newbie here...
>
> What is the correct syntax for deleting a number rows in a SQL database?  Is it possible to use a 4D array which contains the values used by the WHERE clause?
>
> Or, do we have to delete each row individually?
>
> Thanks!
>
>
> Steve
>
> *********************************************
>  Stephen J. Orth                                                
>  The Aquila Group, Inc.         Office:  (608) 834-9213
>  P.O. Box 690                           Mobile:  (608) 347-6447
>  Sun Prairie, WI 53590
>
>  E-Mail:  [hidden email]
> *********************************************
>
>


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: 4D SQL Question

4D Tech mailing list
Lee,

Thanks...

I also found a "BETWEEN" condition, so I'm thinking this is also a possibility.

Best,


Steve


-----Original Message-----
From: Lee Hinde [mailto:[hidden email]]
Sent: Monday, January 22, 2018 11:17 AM
To: [hidden email]; 4D iNug Tech <[hidden email]>
Subject: Re: 4D SQL Question

DELETE FROM users
WHERE id in [1,2.3];

would delete the three users with one of those ids (or none at all, if the ids didn’t exist.)

DELETE FROM users
WHERE last_name=“Smith”;

would delete all users with the last_name of Smith.


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
While you are on the subject...

I am a big fan of:

$tSQL:="SELECT * FROM "+$tTableName+" INTO : "+$tLBox

Because it creates dynamic Listbox columns automatically.

Is there a clever way to select nothing into a Listbox so that an
empty listbox with all the columns defined? I could then fill the
listbox "manually". What I do now is

$tSQL:="SELECT * FROM "+$tTableName+" WHERE "+$tFieldName+" = "+
MYNon-ExistantValue+" INTO : "+$tLBox


Not very elegant:)

--
Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
Not so dissimilar from your idea but you could do

Begin SQL

 SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox;

End SQL


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
Just make sure you have no object fields in that table, or 4D will give you a runtime error and fail.

--
Jeffrey Kain
[hidden email]




> On Jan 22, 2018, at 1:30 PM, Keisuke Miyako via 4D_Tech <[hidden email]> wrote:
>
> Not so dissimilar from your idea but you could do
>
> Begin SQL
>
> SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox;
>
> End SQL

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
In reply to this post by 4D Tech mailing list

> Le 22 janv. 2018 à 18:48, Jim Dorrance via 4D_Tech <[hidden email] <mailto:[hidden email]>> a écrit :
>
> [...]Is there a clever way to select nothing into a Listbox so that an
> empty listbox with all the columns defined? I could then fill the
> listbox "manually". What I do now is
>
> $tSQL:="SELECT * FROM "+$tTableName+" WHERE "+$tFieldName+" = "+
> MYNon-ExistantValue+" INTO : "+$tLBox
>
> Not very elegant:)

Hi Jim,
see here:
<http://forums.4d.com/Post/FR/19537969/1/19537970#19537970 <http://forums.4d.com/Post/FR/19537969/1/19537970#19537970>>
As far I as understand, when there is no data found, all columns are here but all of the text type. I have to turn around (quite heavily). Something's missing in "into listbox"  :-(

To fill "manually", see here, the last paragraph:
<http://forums.4d.com/Post/FR/19409974/1/19413742#19413742 <http://forums.4d.com/Post/FR/19409974/1/19413742#19413742>>
Briefly, after SQL into listbox, duplicate one column, do what you want with that new column.

--
Arnaud de Montard



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Thank you. Perfect.

On Mon, Jan 22, 2018 at 7:30 PM, Keisuke Miyako via 4D_Tech <
[hidden email]> wrote:

> Not so dissimilar from your idea but you could do
>
> Begin SQL
>
>  SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox;
>
> End SQL
>
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>



--
Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Anybody have some hints how I could I fill a list box progressively (
LIMIT? OFFSET),
or a demo DB...

Thanks,
Jim



On Mon, Jan 22, 2018 at 7:30 PM, Keisuke Miyako via 4D_Tech <
[hidden email]> wrote:

> Not so dissimilar from your idea but you could do
>
> Begin SQL
>
>  SELECT * FROM [myTable] LIMIT 0 into LISTBOX :myListbox;
>
> End SQL
>
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>



--
Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
What I am doing now is

Begin SQL

 SELECT * FROM [myTable] LIMIT 10000 into LISTBOX :myListbox;

End SQL

if Records in table > 10000 I then fill the rest of the arrays manually
starting at goto record (10000) on successive outside calls

Any other ideas?
--
Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
I do something like this with Postgres: If the query returns more than X records, these rows become the starting cache. Then a second query is performed to determine the total number of rows that would be returned. The list box is a selection based listbox for a simple 1 field table where I can quickly create a selection with the number of rows in the query. The columns are filled with the query results from the cache (using formula columns). If the user scrolls passed the cached values, the listbox automatically refills the cache with new records based on the displayed row number.

It is somewhat complicated to implement, but provide a seamless interface for scrolling large record sets without loading everything into memory.

John DeSoi, Ph.D.



> On Jan 23, 2018, at 7:22 AM, Jim Dorrance via 4D_Tech <[hidden email]> wrote:
>
> What I am doing now is
>
> Begin SQL
>
> SELECT * FROM [myTable] LIMIT 10000 into LISTBOX :myListbox;
>
> End SQL
>
> if Records in table > 10000 I then fill the rest of the arrays manually
> starting at goto record (10000) on successive outside calls
>
> Any other ideas?

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: 4D SQL Question

4D Tech mailing list
Thanks, I'll try that...

Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************