Re: How to import/export D3-NT data to MS SQL?

Hi
I would have thought that they would make money from the fact that D3 became
vastly more acceptable to the world at large. We know it is a great
database environment but things like Excel interfacing add the selling
sizzle. That particular point is topical for me as I am fiddling around
re-inventing the wheel on that very point at the moment. I have not got
that far in the new docs but I will.
Peter McMurray
"Tony Gravagno" <g6q3x9lu53001@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:i48ld2pbrftd3re9fkatv6bus0l9tgq8m9@xxxxxxxxxx
Once again, my big bro Mark has used the term OSFI in reference to the
OpenDB product which uses the free and built-in OSFI to allow queries
on a remote relational environment.
http://rainingdata.com/products/connect/opendb/index.html

Check the D3 v7.5 docs and you'll see more info on OSFI and a lot of
distinctions made between NT and *nix commands and syntax.

The OSFI can be used for a great many things as it is, including
direct access into remote D3 systems, which isn't documented.

It could be used for much more (get it? Open???), but unfortunately RD
has chosen to limit what it can do because they lack imagination. For
example, it could serve as a direct interface into Excel or OpenOffice
documents:
OPEN "EXCEL:C:FILE.XLS" TO F.DOC ELSE STOP
READ WORKSHEET FROM F.DOC,"Sheet1" ELSE STOP
WORKSHEET<COL,ROW,C.DATA> = 123.45
WORKSHEET<COL,ROW,C.COLOR> = "RED"
WRITE WORKSHEET ON F.DOC,"Sheet1"

Nice huh? RD doesn't get it.

It could be used to translate XML through middle-tier filters:
OPEN "XML:C:DOC.XML" TO F.DOC ELSE STOP
X.QUERY = "//ROOT/CUSTOMERS/CUSTOMER/1022" ; * or similar
READ NODE FROM F.DOC,X.QUERY ELSE STOP
NODE = NEW.NODE
WRITE NODE ON F.DOC,X.QUERY

Nice huh? RD doesn't get it.

It could allow direct read/write access to websites and web services:
OPEN "HTTP:":DOMAIN:SERVICENAME TO F.SERVICE ELSE STOP
WRITE LOGIN ON F.SERVICE,LOGIN.TRANSACTION
READ RESPONSE FROM F.SERVICE,NEW.RESPONSE ELSE ... login failed?
FUNCTION = "GET.WEATHER"
OPTIONS = "LOS ANGELES,09-AUG-2006"
WRITE OPTIONS ON F.SERVICE,FUNCTION
READ RESPONSE FROM F.SERVICE,NEW.RESPONSE ELSE ... bad request?
CRT "Weather for ":RESPONSE<1>:" on ":RESPONSE<2>:
CRT "is ":RESPONSE<3>

Nice huh? RD doesn't get it.

Let your imagination run wild. I hope you have better luck convincing
RD the value of this than I have. (Then again, it doesn't really
matter any more I guess.) I was told very directly that they don't
see a way for making money with such changes so they won't do it.
dumb dumb dumb dumb dumb

T

"Excalibur" wrote:

Hi Mark
I'm flabberghasted. This is obviously brilliant, talk about hiding one's
light under a bushel. I thought that OSFI referred to the ability to
write
to a flat file - a feature poorly documented and obscured by hiding the NT
features under the heading Unix includes. I have checked the Raining Data
site and can find nothing about this. Please expand.
Peter McMurray
"Mark Brown" wrote in message
news:Ts8Cg.10830$Ta6.730@xxxxxxxxxxxxxxxxxxxxxxx
While I was still with Pick Systems ('99 or '00) I helped out the
American
Bible Society. They had a million donar database and a 1.5M donation
database. Every night they were trying to update SQL with the data from
Pick. 1.5M joins = 1 query to get primary keys; 1 query to get seconday
keys; .15M queries like "select * from donar where id = "1" or id = "2"
or
id = "3" etc in 10-row chunks. It was taking 40 hours to do his nightly
update.

Call Raining Data. They'll be more than happy to sell you and help you
install OSFI. Then you can create a "super-q-pointer" to point directly
into the SQL database and write from a Pick Basic program.

Then write one big update query to get all the current data onto SQL.

Then write a file-time trigger (CALLX) on the D3 files applicable to
update
SQL when something on Pick changes. Or write changes into a local Pick
file
for temporary storage (flattened out) so you can do a simple UPDATE
query.
Then clear the transaction work file do it all again.

We did this for ABS and their 40 hour process dropped down to 30
seconds.

Mark Brown


"VNVJeep" <mvanzwieten@xxxxxxxxx> wrote in message
news:1155067256.255293.163050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi All,

Due to some heavy, heavy reporting needs, neither the ODBC server or
PDP for .Net solution for D3 are not performing as good as we had
hoped
for... Some of our reports chug for 4-6hrs before giving us any
results, while others don't even make it that far before just
completely bombing out due to the number or complexity of queries we
have in the report to make it all happen. We are using Crystal Reports
XI on Windows XP. D3 7.4.4 is running on a Win2k3 SP1 Dual Xeon
3.2ghz,
2gig RAM, Raid 1 (OS), Raid 5 (D3 data) with 10k RPM drives.

In our desperation, we are hoping to bounce this question off of you
all with the hopes of there being a solution out there that will help
us resolve this issue!

Are there any methods, or 3rd party solutions out there that are able
to transfer data from D3 into MS SQL server continously, or perhaps in
batches, based on changed data? Is it possible to export changed data
automatically over to a SQL database? If not, is it possible to use a
tool to import data into SQL from D3? Are there any tools out there
that monitor D3 database activity, and are able to export these
changes
out for SQL to pick up and import?

Any creative suggestions are welcome! Thank you in advance for
writing!

Take care,
Mike







.

Relevant Pages

  • Re: Faircom c-Tree Server vs. Relational DBMS
    ... Our products were very reliable in terms of how the database ... SQL was used for building and upgrading the ... to process reports that take several hours to run. ... what database tools to use) that are dictated by management ...
    (comp.databases)
  • Re: How to import/export D3-NT data to MS SQL?
    ... OpenDB product which uses the free and built-in OSFI to allow queries ... dumb dumb dumb dumb dumb ... They had a million donar database and a 1.5M donation ... Every night they were trying to update SQL with the data from ...
    (comp.databases.pick)
  • Re: How to import/export D3-NT data to MS SQL?
    ... dumb dumb dumb dumb dumb ... They had a million donar database and a 1.5M donation ... Every night they were trying to update SQL with the data from ... We are using Crystal Reports ...
    (comp.databases.pick)
  • Re: View or Not Views
    ... SQL Server use the tempdb database to store temporary results and do the required calculations, but its a per-session "table", and you can't access or use these result directly. ... If you really want to access the database in your reports and you always access aggregated data, then create pre-aggregated tables to store the result and use these tables instead of you 500Gb table. ...
    (microsoft.public.sqlserver.olap)
  • Re: Database choice?
    ... access the database using something like Crystal Reports. ... reports if they are somewhat proficient at using a report writer. ... Does GemStone/S accept standard SQL commands if being accessed from the ... If I were to release my retail software app commercially then it might ...
    (comp.lang.smalltalk)