/ Site Map / About Us / General Products / MV Products / Outsourcing / Home page /
|
Data Extract From PICK Plus Info for Non-PICKies |
/ Site Map / About Us / General Products / MV Products / Outsourcing / Home page /
Data Extract From PICK Plus Info for Non-PICKies
Extracting Data From PICK With PixieWeb
Other than web serving, there are some other things that PixieWeb can do. Don't let the "Web" name deceive you. While PixieWeb started as an enhancement for the IIS Webserver it is a more universally applicable COM .dll.
Included with the Pixieweb package is an example of using PixieWeb as a component (PixieWeb.dll) for a VB Client App. The free app. uses MS Excel as a front-end but it demonstrates clearly the interrelationships of the various components. In the case where the client is VB, PixieWeb will communicate by "Telnet" through a continuous connection for the session which makes the server-side programming far simpler. Parts of the example MS Excel code may be re-used for other VB examples.
Telnet has been the rock solid interface to PICK systems since they were first developed and Telnet will be available until the end.
With this kind of Pixieweb use, non-web serving, PixieWeb is a client side app with no need for a web server. PixieWeb acts as a connectivity component in its own right. Pixieweb is not an "odbc driver", but is a more direct Active-X interface in a VB-friendly way to PICK. It is smaller and faster than odbc because it carries less baggage inappropriate to PICK-like data. It is easier to install on clients as you don't need to set up DSNs. It returns records as delimited strings or arrays rather than recordsets but it is relatively easy to wrap it in a VB-dataprovider-class.
If you need to extract PICK data you can write a VB program that will initiate a data extract session on the PICK system, receive the data back from the PICK, and write the data directly to a SQL/Access datastore. The VB program can be a Basic Macro written for MS Access. The VB program may also be placed in "Scheduler" for automated unattended operation.
If you need to pass the data to a different PICK datastore, then the same principle applies, however another instance of PixieWeb is used to pass the data to the other PICK from the VB program. This still only requires one PixieWeb licence.
For best results it is suggested that you place the PixieWeb back-server program into the target PICK system then the data passed back will be easier to manage. Otherwise you will have to rely on TCL queries using LIST and SORT commands which will create more work to unravel the multivalue attributes (fields).
Further Information for Non-PICKIES
This section is for Non-PICK programmers/technicians who may wish to use the PixieWare tool-kit to modernisation or extract data from PICK-like databases.
In this document the term PICK is used to cover all PICK-like derivatives such as D3, Universe, Unidata, mvBase, jBase, Reality,UltPlus, etc..
To describe PixieWare software in terms of solutions we have to split the software between those products that are used to build objects to connect PICK to modern environments (e.g. PixieWeb and PixieEngine), and those tools used for migrating data across to SQL (e.g. PixieEditor, PixieExcel, and PixieWeb).
To describe PICK software in SQL terms PICK was once an operating system in its own right, and today's versions are still something of a separated virtual machine which is something of a world apart. There are ODBC drivers for some of the variants but the catch is that special detailed and skilled configuration is needed to build "views" that are SQL-like.
PixieWare accesses PICK via the "telnet" TCP/IP port, usually number 23. This gives a consistent universal behaviour for lots of different PICK configurations.
PixieWare supplies a backserver programs as PICKBASIC source code which gathers and prepares PICK data and schema to move into the SQL dimension, in a digestible form. In the PixieEditor case you get data delivered in its raw or dictionary-processed state (like a "view") into an SQL database. In the PixieWeb case the data is pre-processed for easy insertion into a SQL table, that is multivalues expanded.
Some rough SQL to PICK translations:
Database = Account
Table = File
View/stored procedure(for read-only) = Dictionary
Record = Item
Field = Attribute
For those who may go as far as downloading an evaluation copy of PixieWeb, you will find an example Access database: "testpxe1.mdb". Here can see what happens when MS Access is setup to mimic PICK, although this example mostly warps the PICK 2-column thing to be more useful in the SQL world.
File/Table effectively has only 2 columns:
Column 0 (A0, also called @ID by some PICK-like imitators) is like a CHAR(25) PRIMARY KEY.
The ITEM is like a MEMO/TEXT field. It contains one string with Chr(254) delimiters used to separate it into a type of column system known as "attributes". This means that every Item/Record may have any number of columns and various Items in the same File/Table and may have different numbers of attributes/fields.
Fortunately in practice they are fairly consistent. The whole thing is something like XML but the single character tags make it faster, although more cryptic.
Much is made of the "multivalues" but they are really no big deal. The attributes/columns can be subdivided by Chr(253) and even further divided by Chr(252) or Chr(251). This means that INNER JOIN etc. are not required in PICK although many designers use the equivalent anyway. Take the typical Purchase Order with header and detail info. The "testpxe1.mdb" shows the purist "PICK" approach with it all in one file/table. Many PICK designers will use 2 tables in a schema that is probably quite familiar to SQL designers. But, you can't collectively define a multi column primary key so this type of thing happens:
File/Table ORDHEADER has Header records with ID's of 1000, 1001, etc.
File/Table ORDDETAILS has detail records with ID's of:
1000*1
1000*2
1000*3
For the 3 lines of order 1000. Such systems are very PICKBASIC code-dependent for dealing with this.
On direct translation to MS-SQL etc we write SQL queries like:
SELECT * FROM ORDD INNER JOIN ORD ON ORDD.A0 LIKE ORD.A0 + "*%" ...
If you need us to help you extract data from PICK datastores, we would need to know:
What is the host OS (Unix, Linux, NT)
What variation of PICK are you dealing with?
We need to get a copy of our PICKBASIC backserver program into your PICK environment. Once there, we can help you extract data. If you are not familiar with PICK and need to install one of the PixieWare products let us help you. For US$100 we will load and install and get running any of the PixieWare software products. This is a phone-call support service offered to assist with conducting software trials. This service is mostly used by those wishing to extract data from PICK.
Email: sales@pixieware.com
/ Site Map / About Us / General Products / MV Products / Outsourcing / Home page /