Anyone know of a Pick to SQL based solution?
Tom Griffin
existing BASIC programs can be used but the data files reside on an
SQL-based host, like Oracle or SQLServer, etc.
TIA,
Tom Griffin
American Fire Equipment, Inc.
Gerald Feeney
"Tom Griffin" <spam...@127.0.0.1> wrote in message
news:38EE032E...@127.0.0.1...
Tom Griffin
jBASE does not do this.
They provide some information to assist you in writing your own interface but
I am looking for a solution that can be more or less dropped in. One needs a
way to replicate active select lists, READNEXT functionality, etc., in
addition to exploding/imploding data records when reading/writing.
Multivalued data must be exploded out into multiple SQL tables and the
reverse done when going back to your programs, which expect to see a Pick
style delimited data record. Then, of course, there are virtual fields and
the need for a seamless query capability.
Tom
Tom Griffin
Tom
Dave Bryant
jBASE does do what you say, but I agree there does have to be some
customization done to achieve complete integration. The amount of work
really depends on the complexity of your data.
For example if your Pick data were flat then it would just drop in, as you
say. I realize that this is not likely to be true, but i say it to make a
point.
We have customers live on Oracle and DB2 and several others working in
databases including SQL Server.
jBASE does all you say because our jEDI architecture abstracts the data so a
readnext simply gets data, but does not know the source, it could be a jBASE
file, or an Oracle file or in fact any data source.
I would be happy to discuss this quite complex topic with you, but it is
difficult without knowing exactly what you are trying to do.
Please either send me email at da...@jbase.com or give me a call at (508)
628-4072 during business hours or (617) 965-2564 outside - i would be happy
to discuss your requirements and how we can help.
Regards
Dave Bryant
jBASE Software, Inc.
Tom Griffin <spam...@127.0.0.1> wrote in message
news:38EE5DCD...@127.0.0.1...
Ken Wallis
>> Gerald Feeney wrote:
>> > Yes. http://www.jbase.com
>> > "Tom Griffin" wrote:
>> > > Does anyone know of a Pick to SQL based middleware solution whereby
the
>> > > existing BASIC programs can be used but the data files reside on an
>> > > SQL-based host, like Oracle or SQLServer, etc.
>> I appreciate the reply, and I am a huge jBASE fan, but as far as I can
tell,
>> jBASE does not do this.
>> They provide some information to assist you in writing your own interface
but
>> I am looking for a solution that can be more or less dropped in. One
needs a
>> way to replicate active select lists, READNEXT functionality, etc., in
>> addition to exploding/imploding data records when reading/writing.
>> Multivalued data must be exploded out into multiple SQL tables and the
>> reverse done when going back to your programs, which expect to see a Pick
>> style delimited data record. Then, of course, there are virtual fields
and
>> the need for a seamless query capability.
Tom, I have direct experience of two pieces of technology which allow you
to do what you are asking (jBASE's jEDI and UniData's OFS). Beyond that
I've gathered a fair deal of detailed information about a third mechanism
(UniData's SQLator) and I've heard references to a fourth (mvON), but those
references have only been from people trying to sell the product so I'm
treating them with suspicion at this point. If someone who has worked with
mvON to make a working solution wants to post their experiences I'd be very
interested to hear them.
As you say Tom, jBASE's jEDI provides a fundamental capability which can be
used to build an external SQL RDBMS interface, but it isn't a 'drop-in'
product. UniData's OFS is the same, though it has more limitations. Both
these APIs allow you to disassociate your BASIC programs from the intrinsic
MV database and plug their OPEN, CLOSE, READ, WRITE statements into user
defined pieces of code without even a recompile. The first problem however
is with the phrase 'user defined': this means you have to do it, or find
someone who already has. jBASE provides some useful sample code and some
inbuilt jBC compiler switches to support interaction with various SQL RDBMS
packages embedded SQL precompilers, and it allows you to develop 'driver'
routines on a file by file basis in BASIC, while UniData simply publishes a
'C' API and leaves the rest to you. Neither of these approaches actually
gets you far down the track. Fortunately other folks have picked up the ball
from there and run a little with it.
For UniData there is a company called Tesseract which has implemented OFS
plug-in gateways for Oracle, Informix and Sybase SQL Server. These
libraries get built into Unidata and then allow you to set up VOC pointers
which redirect I/O from BASIC programs across to the appropriate database.
Such READs and WRITEs are then mapped into appropriate SELECTs and UPDATEs
to the SQL database, and dictionary accesses get diverted into the products
own metadata which is built from a combination of imported schema info from
the RDBMS side and user configured stuff which allows you to map associated
multivalues onto sub-tables etc. Three major things are lacking in the OFS
approach: 1) support outside UniBASIC - PROCs, PAragraphs, UniQuery
sentences all fail completely to cope with FX VOC entries so you have to
replace these ECL verbs with BASIC wrapper programs which understand how to
deals with your RDBMS; and 2) tools to assist in creating SQL RDBMS data
structures which map your existing MV structures. Fundamentally the OFS
approach works well if you have a set of eg. Oracle tables that you need to
push data into, or pull data out of from BASIC programs: Tesseract's data
access modules can automatically build an MV style dictionary from the
Oracle schema associated with the tables you access, but they cannot do the
reverse; finally 3) there is no Transaction boundary support in the
published OFS API. this means that the 'plug-ins' have to treat each WRITE
as a transaction - if that isn't what you want then tough...
UniData never marketed OFS for this purpose, but used it to support their
database distribution technology NFA. Indeed from UniData 4.1 the published
API fails to work without a little additional effort to work around the fact
that they privately modified the OFS API to support some proprietary
extensions they implemented to get around some of these problems for a
concept they bought with System Builder. SQLator is a toolkit which Ardent
focuses at top end VARs who need to deploy their apps against other RDBMS
technology - specifically Oracle to date, but I'd be surprised if there
weren't Informix thoughts going on in there somewhere now ;^). It uses
these proprietary extensions to the published API to overcome the
transaction boundary problem and to provide a hook for UniQuery to interact
with foreign data. SQLator comes with a lot of baggage to assist in
overcoming the metadata problem. Working out how to map your MV data onto a
normalised RDBMS structure is a real bugger (if you'll excuse the
terminology). SQLator provides GUI tools which make this process much
simpler. Certainly they eliminate a whole heap of error prone typing of SQL
DML statements. As I understand it the SQLator approach extracts virtual
fields from dictionaries and assists in the process of creating Oracle
PL/SQL stored procedures which return the same results. I also understand
that it comes with a significant price tag.
On the jBASE front, I've been involved in a project now for somewhere close
to twelve months building, benchmarking and refining a toolset built on the
jEDI API to allow my client to take one of their major applications and
deploy it on jBASE with the data under Oracle. If you go to the jBASE User
Conference in Orlando next month you'll be able to hear first hand from the
other major project contributor about the work we've done and the approach
we've taken. Fundamentally the jEDI API does support all the technical
necessities for such an interface including that ALL I/Os are mapped through
the jEDI whatever part of the MV environment they arise in, and that
transaction boundaries DO form part of the published API. At that level we
established through application specific benchmarking that the closer we put
the SQL code to the data the faster it went. So we moved away from running
SQL sentences embedded in BASIC to extract all the relevant data to a
specific MV 'record' and building up a dynamic array in jBASE to generating
Oracle stored procedures to do dynamic array assembly and disassembly for
us. Independent of the process of devising and building the IO technology,
our biggest problem has always been metadata - how do we map MV structures
into Oracle ones, and even what are the MV structures we need to map? We
have built data cleansing tools to examine our own data and help us correct
our application dictionaries. From these we then devise higher level
dictionaries in which we can add extra information, and from these 'master'
dictionaries we use tools to generate stored procedures for each MV 'file'.
Clearly the simplest thing we can do is store an MV file as an Oracle table
with a key and a LONG RAW (BLOB) record field containing all our delimiters.
For some files this is appropriate since it allows all the persistent data
to live in Oracle, and be managed through Oracle transactions. However such
tables are almost useless for ad-hoc query purposes under Oracle. Parameter
tables are good candidates for this sort of treatment, but not major data
entities. Beyond that we have also done a lot of work on how to materialise
nested sub-tables from an I/O efficiency perspective.
We have now also eliminated the BASIC subroutine interface to these stored
procedures by writing our own 'C' jEDI driver. Accessing Oracle through the
OCI provides performance efficiencies and also allows us to do interesting
things with transactions across multiple Oracle databases (test and live for
instance).
At this point the focus of our project remains the deployment of my clients
vertical applications, but the more we do with it, the more we realise that
it has general applicability.
E-mail me offline Tom if you need more details, or go to jBASE's user
conference and make sure you catch Peter Falson's presentation.
Best Regards,
Ken Wallis
Empower Data Solutions Pty Limited, ACN 079 955 196
Envision, enable, enhance... empower
Mark Brown (at home)
Use Picks OSFI server and make super-qPointers to all your files on say SQL
Server or MSAccess. You can use your existing programs, because they will
just open the q-pointers like local files. It will be a bit slow,
non-pick-based software being what it is and all... but it could work.
Mark
Tom Griffin wrote in message <38EE032E...@127.0.0.1>...
>Does anyone know of a Pick to SQL based middleware solution whereby the
>existing BASIC programs can be used but the data files reside on an
>SQL-based host, like Oracle or SQLServer, etc.
>
patric...@yahoo.com
ability. But I am going to throw out this alternative idea again.
Look at perl and DBI. Perl has a simple DBI interface and all the
picks have the simple ability to execute local os programs. You are
not going to find any product that does all the mapping work for you,
but perl can quickly do this for you.
001 SUBROUTINE WHIRLER.DO.SQL(OPT,CMND,RESULT)
002 ** THIS ROUTINE WILL GENERATE A PERL SCRIPT ON THE FLY
003 RESULT=''
004 OPEN 'unix:/tmp/scripts/' TO SCRIPT.FILE ELSE RETURN
005 CODE='use Pg;'; * replace this with specific database driver
006 DATABASE=OPT<1>
007 CODE<-1>="$conn = Pg::connectdb('dbname=":DATABASE:"');"
008 Q="'"
009 CODE<-1>='$result = $conn->exec("':CMND:'");'
010 IF INDEX(CMND,'SELECT',1) THEN
011 * DOING A SELECT STATEMNET
012 DOING.SELECT=1
013 CODE<-1>='while (@d = $result->fetchrow) {'
014 CODE<-1>=" print join('|', @d);"
015 CODE<-1>=' print "\n";'
016 CODE<-1>='}'
017 CODE<-1>='exit;'
018 END ELSE
019 CODE<-1>='$r = $result->resultStatus;'
020 CODE<-1>='print $r;'
021 CODE<-1>='print "\n";'
022 CODE<-1>='exit;'
023 END
024 ID=SYSTEM(19)
025 WRITE CODE ON SCRIPT.FILE, ID
026 EXECUTE '!perl /tmp/scripts/':ID CAPTURING RESULT
027 DELETE SCRIPT.FILE, ID
028 CONVERT '|' TO CHAR(253) IN RESULT
029 RETURN
Now this script connects to Postgres sql database. It outputs all SQL
Select statements as | delimited files.
Now I have used this in pick in the manner you have asked as such.
* Emulate select statement
CMD='SELECT invoice_no FROM invoice_file;'
CALL WHIRLER.DO.SQL('',CMD,RESULT.LIST)
* This is just like a result list, lets show
OPEN 'POINTER-FILE' TO LIST.FILE ELSE STOP
WRITE RESULT.LIST ON LIST.FILE, 'SQL.LIST'
EXECUTE 'GET-LIST SQL.LIST'
EOF=0
LOOP
READNEXT ID ELSE EOF=1
UNTIL EOF DO
CMND='SELECT * FROM invoice_file WHERE ID = "':ID:'";'
CALL WHIRLER.DO.SQL('',CMND,RETURN.REC)
* Each value is a a sql column item in return.rec
REPEAT
- Patrick
Sent via Deja.com http://www.deja.com/
Before you buy.
Paul Stanley
Could you explain how to get at data held in an MS Access database from D3?
Paul
Mark Brown (at home) <drw...@flash.net> wrote in message
news:naeI4.21270$hh2.3...@news.flash.net...
Paul Stanley
sh*te!
Paul.
Paul Stanley <pauls...@btinternet.com> wrote in message
news:8ctm2v$743$1...@plutonium.btinternet.com...
Paul Stanley
Matti Lamprhey
> Hold that - just found some of your replies via Deja. My news feed is
bloody
> sh*te!
And in both directions, Paul!
Matti
Dave Johnstone
We have a product just about to be released as a production version, but we
do not have the latest info on our web site until after this week-end. Visit
www.pixieware.com to at least see what PixieEngine(tm) is about. PixieEngine
is supplied with:
· PixieMassage, for converting legacy DATABASIC programs
· PixieEditor which can convert data from PICK and UNIVERSE databases.
Regards, Dave Johnstone.
Tom Griffin <spam...@127.0.0.1> wrote in message
news:38EE032E...@127.0.0.1...
joseba real de asua
SERVER but it should work everywhere, it uses SQL and the SQL PLUS
Oracle program.
1st. From D3 to ORACLE
Very easy:
1.st D3 to Oracle
eof=0
loop
readnext cliente else eof=1
until eof=1 do
READ BFA_BORRAR FROM VFA_BORRAR,CLIENTE THEN
frase="UPDATE ALBARAN SET ALB_CODCLI = ":BFA_BORRAR<1>:" WHERE
ALB_CODCLI = ":CLIENTE:";"
CAMBIA.SQL=""
CAMBIA.SQL<1>=FRASE
CAMBIA.SQL<2>="COMMIT WORK;"
CAMBIA.SQL<3>="EXIT;"
WRITE CAMBIA.SQL ON VFDOS,"CAMBIA.SQL"
EXECUTE "!CAMBIA.BAT"
frase="UPDATE CABECERA SET FTR_CODCLI = ":BFA_BORRAR<1>:" WHERE
FTR_CODCLI = ":CLIENTE:";"
CAMBIA.SQL=""
CAMBIA.SQL<1>=FRASE
CAMBIA.SQL<2>="COMMIT WORK;"
CAMBIA.SQL<3>="EXIT;"
WRITE CAMBIA.SQL ON VFDOS,"CAMBIA.SQL"
EXECUTE "!CAMBIA.BAT"
As you can see the program generate SQL sentences based in D3 data and
write them on an NT file (cambia.sql)
After that it execute a bat NT process
edit cambia.bat
> sqlplus user/password "@cambia.sql"
>
This execute the SQL with the ORACLE sqlplus with the SQL sentence
written on cambia.sql
Quite easy and works well
2.nd From oracle to D3
A little bit more complicated:
I have a phantom process ruuning each 5 minutes with the program:
> loop
> until 1=2 do
> open "master_109,customer," to vfcustomer
> open "dos:d:/clientes/" to vfclientes
> delete vfclientes,"myfile.txt"
> execute "!exportar.bat" capturing kk
> read bf from vfclientes,"myfile.txt" else bf=""
> n=dcount(bf,@am)
> eod=0
> nl=25
> a=0
> loop
> bf=delete(bf,nl)
> nl=nl+24-1
> if nl>n then eod=1
> until eod=1 do
> repeat
> n=dcount(bf,@am)
> for i=2 to n step 5
> bfcliente=""
> cliente=trim(bf[1,9])
> bfcliente<1>=trim(bf[11,40])
> bfcliente<2>=trim(bf[52,40])
>
> bfcliente<3>=trim(bf[93,5])
> bfcliente<4>=trim(bf[1,40])
> bfcliente<5>=trim(bf[42,40])
> bfcliente<6>=trim(bf[83,15])
> bfcliente<7>=trim(bf[1,15])
> bfcliente<8>=trim(bf[17,40])
> bfcliente<9>=trim(bf[58,40])
> bfcliente<10>=trim(bf[1,15])
> read bfcustomer from vfcustomer,cliente then
> gosub 100;*actualizar
> end else
> bfcustomer=""
> gosub 100;*actualizar
> call defecto(cliente,bfcustomer)
> end
> if cliente#"" then
> write bfcustomer on vfcustomer,cliente
> end
> next i
> close vfcustomer
> close vf_clientes
> sleep 300
> repeat
> stop
>
Let' s see the exportar.bat file:
> sqlplus user/password "@exportar.sql"
>
And the exportar.sql file
> set heading off
> spool d:\clientes\myfile.txt
> select * from D3_Clientes;
> spool off
> set heading on
> exit
>
As you can see D3 execute each five minutes an SQL predefined capturing
the output in a file using Oracle SQLPLUS. The the basic program
'understand' that file in order to update D3 data
Not 'new' technology, but it works fine
Hope this help
joseba real de asua
frel...@sarenet.es
tedh...@my-deja.com
form, allows UniData BASIC programs to access an Oracle database. Some
additional variations of SQLator were supposed to be produced, possibly
including some that would work with UniVerse BASIC as well as
connecting to other databases besides Oracle. You might want to
contact Ardent/Informix and find out the latest on SQLator.
-- Ted Hatch (retired from Ardent)
In article <38EE032E...@127.0.0.1>,
Tom Griffin <spam...@127.0.0.1> wrote:
> Does anyone know of a Pick to SQL based middleware solution whereby
the
> existing BASIC programs can be used but the data files reside on an
> SQL-based host, like Oracle or SQLServer, etc.
>
> TIA,
> Tom Griffin
> American Fire Equipment, Inc.
>
>