USE DATABASE from 4D client

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

USE DATABASE from 4D client

4D Tech mailing list
I asked this question some time ago but with the current flurry of interest
in external databases I will have another go. NB no components involved
here.

If I have an external datafile held on the server how do I access it
(purely for lookup) from a 4D client?

What I tried was to use SQL as follows:

Begin SQL
USE DATABASE DATAFILE 'F:\AuthCodeEXT.4DD';
select LocAuthCode_LOOKUP.OSLAUA

from LocAuthCode_LOOKUP
where PCD2 = :cPC_Search
into :acLocAuthCodes;

USE DATABASE SQL_INTERNAL;
End SQL

 and then to release the datafile with AUTO_CLOSE.

This works fine in a very low usage context.

I would like to use it in a busier scenario but am concerned that the
datafile would often be locked.

One suggestion I saw on the NUG was to access it from the server using the
"Execute on Server" property on a method and then apparently the locking is
taken care of by the 4D server.

Has anybody tried this arrangement in a busy database (40-50 users and
additional web cleints)?

David Samson
Manchester
UK
**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
David,

Interesting question about Execute on server, I hope we get a good answer.
I suspect that you might be right. If everything is going through Execute
on server, it's all running on one machine. At that point, I guess you
would not use AUTO_CLOSE and would allow 4D to manage the access. Hmm. I'm
curious to hear more about this.

On another note, is there some reason that you can't or don't want to
download the file to the clients? There probably is (it's not hard to
imagine various good reasons.) If so, is there a way that you can segment
the data and download some of it to the clients? Sometimes a data set has
values that can be shared/duplicated, etc. mixed in with some that can't.
If you've got a situation like that, you could at least pull out the
okay-to-download data into one file for copying to the clients. That might
beat the network traffic and bottleneck of a single shared file on the
server machine.
**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
rather activating the "execute on server" method property,
I would recommend using the REMOTE keyword:

http://doc.4d.com/4Dv15/4D/15/USE-DATABASE.300-2288124.en.html

2017/05/30 23:25、David Samson via 4D_Tech <[hidden email]<mailto:[hidden email]>> のメール:

One suggestion I saw on the NUG was to access it from the server using the
"Execute on Server" property on a method and then apparently the locking is
taken care of by the 4D server.



**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
RE> rather activating the "execute on server" method property,
I would recommend using the REMOTE keyword:

http://doc.4d.com/4Dv15/4D/15/USE-DATABASE.300-2288124.en.html



I don't understand how this would make any difference; the REMOTE database
would still be held open until AUTO_CLOSE was called, or the calling
process ended - so the database would remain locked to other users.

How does REMOTE allow the database to remain open (so there is minimal
overhead to call USE DATABASE) to other users?


Bob Miller
Chomerics, a division of Parker Hannifin Corporation


**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
David Adams wrote:

>On another note, is there some reason that you can't or don't want to
>download the file to the clients?

It is a 28 million record postal address file so segmenting won't work. I
have thought of copying it over to the clients but then the issue of
keeping it updated raises its head.

I would much prefer one central database. At present it is in the main 4D
datafile but bloats it somewhat.

Thank you David,
David
**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Keisuke Miyako wrote:

>rather activating the "execute on server" method property,
>I would recommend using the REMOTE keyword:

Does that allow multiple users to access it simultanously?

Thanks for the reply,
David
**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
> It is a 28 million record postal address file so segmenting won't work. I
> have thought of copying it over to the clients but then the issue of
> keeping it updated raises its head.

This is a perfect example of when to use MySQL (or similar.) For MySQL, use
MyConnect:

http://www.pluggers.nl/product/myconnect-plugin/

Or, alternatively, use 4D as a Web server (NTK-based or the native Web
server) and set up a stand-alone 4D to handle the address lookups. (Or the
server machine, or a client, for that matter.)

Either solution is easy to set up, should run quickly and well and, give
you a lot of flexibility regarding where you stick the data. It's nice
*not* to bloat the main data file with records that need to be rolled
periodically. Get a new data set of 28M records? Load them into an offline
copy of your lookup service, shut it down, swap in the new data, restart.
Depending on setup, you could be off-line for a minute. And you never need
to restart 4D Server. And you never have to compact because of thrashing
28M records in 4D Server.
**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
the exclusive access applies to *other applications*.

an external database on the server side (REMOTE) or client (LOCAL/default) is always accessible from other 4D processes on the same application, just like regular tables.

> 2017/05/31 1:14、David Samson via 4D_Tech <[hidden email]> のメール:
> Does that allow multiple users to access it simultanously?




**********************************************************************
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: USE DATABASE from 4D client

4D Tech mailing list
In reply to this post by 4D Tech mailing list
I might be missing something obvious. Why not simply using SQL login to access external database and the data could be accessed by multiple 4D clients.

SQL LOGIN($externaAddress;$user;$pswd;*)
If (OK=1)
Begin SQL
//do whatever SQL query
End SQL
SQL LOGOUT
End if

Alan Chan

4D iNug Technical <[hidden email]> writes:
>If I have an external datafile held on the server how do I access it
>(purely for lookup) from a 4D client?

**********************************************************************
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]
**********************************************************************