Quantcast

QUERY BY SQL drawbacks

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

QUERY BY SQL drawbacks

Balinder Walia
I have a table called DOCUMENTS with big fat BLOBs.
I would like to just load data for a few fields into the current selection
or into arrays more precisely ignoring the BLOBs as I don't really need
BLOBs data into the memory.
As far as I know the SQL should allow exactly that. For example

SELECT Code,Title,Url FROM DOCUMENTS

But when I try this syntax in QUERY BY SQL([Documents];"SELECT
Code,Title,Url FROM DOCUMENTS") 4D v 11 throws an error. As far I understand
I can only pass the SQL formula into this command, for example:

C_TEXT($query)

$query:="Documents.SiteID=7"

QUERY BY SQL([Documents];$query)

will work fine but it loads the big fat BLOBs too.

So as far I know there is no way to just load data from a few fields.

Any ideas.

Cheers

------------------------
Best Regards

Balinder Singh Walia
Open Source Internet Developer
M: +44 (0) 7944239545
F: +44 (0) 8700512372
E: [hidden email]
W: http://balinderwalia.com

This email and any attached files are confidential and intended only for
the addressee. It may contain confidential and privileged material. Any
review, retransmission, dissemination or reliance upon it, or use of this
information by other than the intended recipient is prohibited. If you have
received this email in error please contact the sender and delete it. Thank
you.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Pat Bensky
Bal,

As I read it ... QUERY BY SQL works just like a regular QUERY except that it
uses the SQL syntax instead of 4D syntax ... so it's going to load the
records just like a QUERY does. You can't include the SELECT part of the SQL
query - just the WHERE clause, as it says in the docs:

"Valid SQL search formula represent-
ing the WHERE clause of the SELECT
query"

Pat


On 27/09/2007, Balinder Walia <[hidden email]> wrote:

>
> I have a table called DOCUMENTS with big fat BLOBs.
> I would like to just load data for a few fields into the current selection
> or into arrays more precisely ignoring the BLOBs as I don't really need
> BLOBs data into the memory.
> As far as I know the SQL should allow exactly that. For example
>
> SELECT Code,Title,Url FROM DOCUMENTS
>
> But when I try this syntax in QUERY BY SQL([Documents];"SELECT
> Code,Title,Url FROM DOCUMENTS") 4D v 11 throws an error. As far I
> understand
> I can only pass the SQL formula into this command, for example:
>
> C_TEXT($query)
>
> $query:="Documents.SiteID=7"
>
> QUERY BY SQL([Documents];$query)
>
> will work fine but it loads the big fat BLOBs too.
>
> So as far I know there is no way to just load data from a few fields.
>
> Any ideas.
>
> Cheers
>
> ------------------------
> Best Regards
>
> Balinder Singh Walia
> Open Source Internet Developer
> M: +44 (0) 7944239545
> F: +44 (0) 8700512372
> E: [hidden email]
> W: http://balinderwalia.com
>
> This email and any attached files are confidential and intended only for
> the addressee. It may contain confidential and privileged material. Any
> review, retransmission, dissemination or reliance upon it, or use of this
> information by other than the intended recipient is prohibited. If you
> have
> received this email in error please contact the sender and delete it.
> Thank
> you.
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Pat Bensky
In reply to this post by Balinder Walia
P.S.
I wonder if something like this would work:
Begin SQL
SELECT Code,Title,Url FROM DOCUMENTS INTO #temptable
End SQL

temptable would then be a temporary table containing the columns
Code,Title,Url.

I'm not sure what you would do with that temporary table ... but maybe this
would be something to look at?

Pat


On 27/09/2007, Balinder Walia <[hidden email]> wrote:

>
> I have a table called DOCUMENTS with big fat BLOBs.
> I would like to just load data for a few fields into the current selection
> or into arrays more precisely ignoring the BLOBs as I don't really need
> BLOBs data into the memory.
> As far as I know the SQL should allow exactly that. For example
>
> SELECT Code,Title,Url FROM DOCUMENTS
>
> But when I try this syntax in QUERY BY SQL([Documents];"SELECT
> Code,Title,Url FROM DOCUMENTS") 4D v 11 throws an error. As far I
> understand
> I can only pass the SQL formula into this command, for example:
>
> C_TEXT($query)
>
> $query:="Documents.SiteID=7"
>
> QUERY BY SQL([Documents];$query)
>
> will work fine but it loads the big fat BLOBs too.
>
> So as far I know there is no way to just load data from a few fields.
>
> Any ideas.
>
> Cheers
>
> ------------------------
> Best Regards
>
> Balinder Singh Walia
> Open Source Internet Developer
> M: +44 (0) 7944239545
> F: +44 (0) 8700512372
> E: [hidden email]
> W: http://balinderwalia.com
>
> This email and any attached files are confidential and intended only for
> the addressee. It may contain confidential and privileged material. Any
> review, retransmission, dissemination or reliance upon it, or use of this
> information by other than the intended recipient is prohibited. If you
> have
> received this email in error please contact the sender and delete it.
> Thank
> you.
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
*************************************************
CatBase - The Database Publishing Solution
tel: +44 (0) 1462 454522
fax: +44 (0) 1462 454566
w: http://www.catbase.com
skype: pat.bensky
*************************************************
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Steve Simpson
In reply to this post by Balinder Walia
At 10:22 AM 9/27/2007, Balinder Walia wrote:

>I have a table called DOCUMENTS with big fat BLOBs.[snip]
>So as far I know there is no way to just load data from a few fields.

If these are truly big and fat blobs, I'd suggest storing the
information about each blob/document in 4D (including searchable
keywords, etc) but store the actual documents (blobs) on disk. One
field in the record would be the path to the blob, so you could load
the document and deal with it on your own terms any time and any how
you need to. This gives you the ability to store the documents on
very large drives elsewhere, which is how we handle all our
multimedia projects, some of which consist of multiple terabytes of
multimedia "blobs" (documents). Placement of these files, naming of
files, manipulation of files are all handled by 4D. Users never know
the big fat blob data is not actually stored within the 4D data file.
But everything from backing up to database and disk management to
record memory loading is made way easier.

That said, I thought I read in the manual that v11 loaded ONLY data
needed on the form being displayed, not the entire record, for this
very reason.


<><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
Steve Simpson, Cimarron Software, Inc.
voice: (813) 264-2706    fax: (813) 264-4072  

**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Balinder Walia
Thanks Pat and Steve for your wiser suggestions. Steve I have already
started to save BLOBs on the disk but there so many other parts in the
system where I have a table with nearly 200 fields and I only want to
display 3 or 4. So I was curious to know more about it that's all. I tested
the code on a form as well without form all the field data is loaded
Cheers

On 27/09/2007, Steve Simpson <[hidden email]> wrote:

>
> At 10:22 AM 9/27/2007, Balinder Walia wrote:
>
> >I have a table called DOCUMENTS with big fat BLOBs.[snip]
> >So as far I know there is no way to just load data from a few fields.
>
> If these are truly big and fat blobs, I'd suggest storing the
> information about each blob/document in 4D (including searchable
> keywords, etc) but store the actual documents (blobs) on disk. One
> field in the record would be the path to the blob, so you could load
> the document and deal with it on your own terms any time and any how
> you need to. This gives you the ability to store the documents on
> very large drives elsewhere, which is how we handle all our
> multimedia projects, some of which consist of multiple terabytes of
> multimedia "blobs" (documents). Placement of these files, naming of
> files, manipulation of files are all handled by 4D. Users never know
> the big fat blob data is not actually stored within the 4D data file.
> But everything from backing up to database and disk management to
> record memory loading is made way easier.
>
> That said, I thought I read in the manual that v11 loaded ONLY data
> needed on the form being displayed, not the entire record, for this
> very reason.
>
>
> <><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
>
> Steve Simpson, Cimarron Software, Inc.
> voice: (813) 264-2706    fax: (813) 264-4072
>
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
------------------------
Best Regards

Balinder Singh Walia
Open Source Internet Developer
M: +44 (0) 7944239545
F: +44 (0) 8700512372
E: [hidden email]
W: http://balinderwalia.com

This email and any attached files are confidential and intended only for
the addressee. It may contain confidential and privileged material. Any
review, retransmission, dissemination or reliance upon it, or use of this
information by other than the intended recipient is prohibited. If you have
received this email in error please contact the sender and delete it. Thank
you.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: QUERY BY SQL drawbacks

Jean-yves-3
In reply to this post by Balinder Walia
******
>From the upgrade manual, page 43.
In 4D v11, data contained in fields that can store large volumes (such
as the BLOB, Picture and Text) are now stored outside of the
records themselves. This accelerates database execution, in particular
during queries: any voluminous data contained in these fields is no
longer systematically loaded into memory when 4D accesses the host
records. This data is actually only loaded when necessary, for example
once the record being searched for has been found.

 This new operation is automatic and does not require any
modification of the existing code - except for the fact that it makes
any code set up by the developer to store data in a separate table
pointless.
In fact, for optimization reasons, most 4D developers manage BLOB,
Picture and Text fields using a dedicated table that is related to the
main table by a One-to-One relation. This trick still works perfectly
well in version 11 but it is no longer necessary.
*******

Hope that helps..

J-Yves.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: QUERY BY SQL drawbacks

Chip Scheide
J
how does this affect queries on the contents of text
 
On Thu, 27 Sep 2007 09:58:00 -0700, Jean-Yves wrote:

> ******
>> From the upgrade manual, page 43.
> In 4D v11, data contained in fields that can store large volumes (such
> as the BLOB, Picture and Text) are now stored outside of the
> records themselves. This accelerates database execution, in particular
> during queries: any voluminous data contained in these fields is no
> longer systematically loaded into memory when 4D accesses the host
> records. This data is actually only loaded when necessary, for example
> once the record being searched for has been found.
>
>  This new operation is automatic and does not require any
> modification of the existing code - except for the fact that it makes
> any code set up by the developer to store data in a separate table
> pointless.
> In fact, for optimization reasons, most 4D developers manage BLOB,
> Picture and Text fields using a dedicated table that is related to the
> main table by a One-to-One relation. This trick still works perfectly
> well in version 11 but it is no longer necessary.
> *******
>
> Hope that helps..
>
> J-Yves.
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
-------------
Chip Scheide

You are a Fluke of the universe...
You have no right to be here...
Whether you can hear it or not,
the universe is laughing behind your back...

Deteriorata
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

mikey-2
In reply to this post by Jean-yves-3
Just for reference, is "DOCUMENTS with big fat BLOBs" a valid table name in v11?

Just checking.
Going back to sleep now.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: QUERY BY SQL drawbacks

Jean-yves-3
In reply to this post by Balinder Walia
>> how does this affect queries on the contents of text

I really don't understand the question. If the field is required during a
query, it will be of course loaded if the field is not indexed. Same for
pictures if you execute query by formula with Picture size for example. This
data is actually only loaded when necessary.

J-Yves.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Chris Visaya
In reply to this post by Balinder Walia
> I have a table called DOCUMENTS with big fat BLOBs.
> I would like to just load data for a few fields into the current selection
> or into arrays more precisely ignoring the BLOBs

You could try a variation of below:

ARRAY TEXT($code;0)
ARRAY TEXT($title;0)
ARRAY TEXT($url;0)
Begin SQL
        SELECT Code FROM Documents INTO <<$code>>;
        SELECT Title FROM Documents INTO <<$title>>;
        SELECT Url FROM Documents INTO <<$url>>;
        SELECT Code, Title, URL FROM Customers INTO <<some_listbox>>;
End SQL

The first three SELECTS will load the contents of your three fields into
arrays $code, $title and $url and conveniently line up their indexes.
You can access them that way.

The last SELECT could be done separately and will put the contents of
your three fields into the listbox, some_listbox.

Kind Regards,

Chris Visaya

--
Chris Visaya
Technical Support Engineer
4D, Inc.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Balinder Walia
Thanks you Jean for clarification and others for suggestions.

On 27/09/2007, Chris Visaya <[hidden email] > wrote:

>
> > I have a table called DOCUMENTS with big fat BLOBs.
> > I would like to just load data for a few fields into the current
> selection
> > or into arrays more precisely ignoring the BLOBs
>
> You could try a variation of below:
>
> ARRAY TEXT($code;0)
> ARRAY TEXT($title;0)
> ARRAY TEXT($url;0)
> Begin SQL
>         SELECT Code FROM Documents INTO <<$code>>;
>         SELECT Title FROM Documents INTO <<$title>>;
>         SELECT Url FROM Documents INTO <<$url>>;
>         SELECT Code, Title, URL FROM Customers INTO <<some_listbox>>;
> End SQL
>
> The first three SELECTS will load the contents of your three fields into
> arrays $code, $title and $url and conveniently line up their indexes.
> You can access them that way.
>
> The last SELECT could be done separately and will put the contents of
> your three fields into the listbox, some_listbox.
>
> Kind Regards,
>
> Chris Visaya
>
> --
> Chris Visaya
> Technical Support Engineer
> 4D, Inc.
> ************************************************************ **********
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:   http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto: [hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> ****************************** ****************************************
>



--
------------------------
Best Regards

Balinder Singh Walia
Open Source Internet Developer
M: +44 (0) 7944239545
F: +44 (0) 8700512372
E: [hidden email]
W: http://balinderwalia.com

This email and any attached files are confidential and intended only for
the addressee. It may contain confidential and privileged material. Any
review, retransmission, dissemination or reliance upon it, or use of this
information by other than the intended recipient is prohibited. If you have
received this email in error please contact the sender and delete it. Thank
you.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Koen Van Hooreweghe
Hi,

Isn't this piece of code potentially dangerous?
First of all, can you ever be sure that 3 separate sql statements  
will 'conveniently' line up the array elements? In which order will  
4D fill the text arrays if you don't explicitly add an order by clause?

What if another process (or another client in CS) is busy doing a  
massive import into your documents table. Or a delete of records just  
in between the execution of the separate statements!
The first array may contain a different number of elements than the  
last one!

Koen

Op 28-sep-07, om 10:26 heeft Balinder Walia het volgende geschreven:

>> You could try a variation of below:
>>
>> ARRAY TEXT($code;0)
>> ARRAY TEXT($title;0)
>> ARRAY TEXT($url;0)
>> Begin SQL
>>         SELECT Code FROM Documents INTO <<$code>>;
>>         SELECT Title FROM Documents INTO <<$title>>;
>>         SELECT Url FROM Documents INTO <<$url>>;
>>         SELECT Code, Title, URL FROM Customers INTO <<some_listbox>>;
>> End SQL
>>
>> The first three SELECTS will load the contents of your three  
>> fields into
>> arrays $code, $title and $url and conveniently line up their indexes.
>> You can access them that way.

--------------------
Compass bvba
Koen Van Hooreweghe
Kloosterstraat 65
9910 Knesselare
Belgium
[hidden email]


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Rob Laveaux

On 28-sep-2007, at 10:44, Koen Van Hooreweghe wrote:

> Isn't this piece of code potentially dangerous?
> First of all, can you ever be sure that 3 separate sql statements  
> will 'conveniently' line up the array elements? In which order will  
> 4D fill the text arrays if you don't explicitly add an order by  
> clause?

Actually it should read like this:

ARRAY TEXT($code;0)
ARRAY TEXT($title;0)
ARRAY TEXT($url;0)
Begin SQL
         SELECT Code, Title, Url FROM Documents INTO <<$code>>, <<
$title>>, <<$url>>;
End SQL

If you really need consistency of data between multiple lines of  
code, you should start a read only transaction. But I don't know if  
4D supports that.

This is used when for example you want to do one or more reports and  
want the reports to be based on consistent set of data and not take  
modifications made in between by other users into account.

Can anyone from 4D tell whether it supports something like Multiple  
Version Concurrency Control as PostgreSQL does?


- Rob Laveaux

--------------------------------------------------------
Pluggers Software
Bleriotlaan 62
2497 BM  Den Haag
The Netherlands

Email: [hidden email]
Website: http://www.pluggers.nl

--------------------------------------------------------


**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Balinder Walia
I am trying Begin SQL way now:
C_STRING(80;vIdentifierStr)
vIdentifierStr:="2.7.j.0"

Begin SQL
 SELECT * FROM Documents WHERE Identifier= :vIdentifierStr
End SQL

Above statement doesn't load anything in the current selection where as I
have thousands of docs with this ref.

Am I being stupid or missing something?

Thanks in advance

On 28/09/2007, Rob Laveaux <[hidden email]> wrote:

>
>
> On 28-sep-2007, at 10:44, Koen Van Hooreweghe wrote:
>
> > Isn't this piece of code potentially dangerous?
> > First of all, can you ever be sure that 3 separate sql statements
> > will 'conveniently' line up the array elements? In which order will
> > 4D fill the text arrays if you don't explicitly add an order by
> > clause?
>
> Actually it should read like this:
>
> ARRAY TEXT($code;0)
> ARRAY TEXT($title;0)
> ARRAY TEXT($url;0)
> Begin SQL
>          SELECT Code, Title, Url FROM Documents INTO <<$code>>, <<
> $title>>, <<$url>>;
> End SQL
>
> If you really need consistency of data between multiple lines of
> code, you should start a read only transaction. But I don't know if
> 4D supports that.
>
> This is used when for example you want to do one or more reports and
> want the reports to be based on consistent set of data and not take
> modifications made in between by other users into account.
>
> Can anyone from 4D tell whether it supports something like Multiple
> Version Concurrency Control as PostgreSQL does?
>
>
> - Rob Laveaux
>
> --------------------------------------------------------
> Pluggers Software
> Bleriotlaan 62
> 2497 BM  Den Haag
> The Netherlands
>
> Email: [hidden email]
> Website: http://www.pluggers.nl
>
> --------------------------------------------------------
>
>
>
> **********************************************************************
> 4D v11 SQL - A 21st Century database for 21st Century developers
> http://www.4d.com/products/new.html
>
> 4th Dimension Internet Users Group (4D iNUG)
> FAQ:  http://www.4d.com/support/faqnug.html
> Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
> Unsub:  mailto:[hidden email]
> Post: mailto:[hidden email]
> Options: https://lists.4d.com/mailman/listinfo/4d_tech
> **********************************************************************
>



--
------------------------
Best Regards

Balinder Singh Walia
Open Source Internet Developer
M: +44 (0) 7944239545
F: +44 (0) 8700512372
E: [hidden email]
W: http://balinderwalia.com

This email and any attached files are confidential and intended only for
the addressee. It may contain confidential and privileged material. Any
review, retransmission, dissemination or reliance upon it, or use of this
information by other than the intended recipient is prohibited. If you have
received this email in error please contact the sender and delete it. Thank
you.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Brendan Coveney
Statements between Begin SQL and End SQL don't affect the current  
selection. You have load the data into variables and arrays.

Regards

Brendan
On Oct 2, 2007, at 2:38 PM, Balinder Walia wrote:

> I am trying Begin SQL way now:
> C_STRING(80;vIdentifierStr)
> vIdentifierStr:="2.7.j.0"
>
> Begin SQL
>  SELECT * FROM Documents WHERE Identifier= :vIdentifierStr
> End SQL
>
> Above statement doesn't load anything in the current selection  
> where as I
> have thousands of docs with this ref.
>
> A
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Balinder Walia
Thanks Brendan I got it now
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: QUERY BY SQL drawbacks

Owen Watson-2
In reply to this post by Brendan Coveney
Brendan:

I'm looking thru the online 4D-SQL tutorial and thinking it would be
nice to have a small guide to the philosophical (?) differences
between 4D and sql queries. For example, why does one form of query
seem to require calling ODBC LOAD RECORD after it's made?

On 03/10/2007, Brendan Coveney <[hidden email]> wrote:
> Statements between Begin SQL and End SQL don't affect the current
> selection. You have load the data into variables and arrays.
**********************************************************************
4D v11 SQL - A 21st Century database for 21st Century developers
http://www.4d.com/products/new.html

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:[hidden email]
Post: mailto:[hidden email]
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************
Loading...