Hi. I am looking for information on how to connect to an ADP/PIC
database with SQL. I am a webdeveloper recently hired by an aut
dealer. I am quite new to the pick world, and for that matter, almos
all database programming outside of using PHP with mysql

All of the dealerships data is stored in a pick database. I am charge
with making a website that is able to display that data online.

One possible route has been exporting the pick data to csv files an
putting them on the linux side and then grabbing them from there an
importing them into sql. this seems to be overly complicated
however. ideally, i could like to connect to the database via sql an
interact with it directly

could someone help me get a good start on doing this? is it eve
possible? i played around with software like pixieware, but it seeme
like it wasn't up to the task. i tried several different connectio
configuration files, but they all errored out

any ideas? any help is very much appreciated

Hi,
You are in a fairly common situation.
Honestly you have 2 options:
1. Partner with someone who is accomplished in appropriate Pick tools (to
expose your data to a web site). Personally, I recommend Tony Gravagno and
FlashConnect.

2. Spend many months trying to do this yourself, and in all likelihood fail
(or produce sub-par results).

Note: We have been working with Tony and it has been a blessing.
Jeff


"drogers" <drogers (AT) quirkcars-dot-com (DOT) no-spam.invalid> wrote

Some additional information, like which version of "Pick" this
particular ADP dealer is using would be "useful", as this will impact
your options. You may be able to use a 3rd party ODBC driver, but IIRC
on this group there has been references to the "interesting" and
"undocumented" (well, undictionaried?) file structures.

Like the other posters have suggested, find a "local" who understands
things like "pick" and "databases", otherwise your lack of knowledge
will doom the project before you start

As Jeff mentioned, you need help from a third party. Fortunately for you,
there are several to choose from.

Herb Rubin, Pathfinders Software, www.pfinders.com

Mel Soriano, Eagle Rock Information Systems, Web Wizard,
http://www.eriscorp.com

Luke Bucklin, Sierra Bravo, http://www.sierra-bravo.com/

Ross Ferris, Visage

Raining Data, Flashconnect, http://www.rainingdata.com

Doug Demitru, Coyote, http://www.modsoft.com

And many others. (Sorry for not listing you.)

I know Herb and Luke both use PHP. They may be the best place for someone
with your background to start.

Jeff

"Jeff Caspari" <FDFDFDFD (AT) sneakernet (DOT) com.invalid> wrote

If ADP Pick is Advanced Pick you have a problem. It is out of date. The
product today is D3. It is easy to migrate but i think you cannot offer
that to the customer. If you have Advanced Pick, it can be native, pro
or under some kind of unix. If it is native or pro, your only have one
posible solution, Doug Demitru and Sam Anderson at www.easyco.com. I do
not know if they still work with AP, but Coyote can do it (very,very
old versions so). If it is AP/nix may be something can be done. Then,
Tony can help you for sure, www.nebula-rnd.com. May be somebody at Via
Systems (www.via.com) can help you, they had products like via ODBC or
Winlink that worked with AP, but again, very, very old..

But, bad notices, the true way is to migrate to D3 (or any other but
from AP to D3 it is full compatible). Find somebody at
www.rainingdata.com. With D3 you can use whatever you want, ASP, java,
PHP, etc..

Hope this help

drogers (AT) quirkcars-dot-com (DOT) no-spam.invalid (drogers) wrote in
news:FbOdnRgR9er9ULreRVn_vA (AT) giganews (DOT) com:

You basically have two choices:

a) Hook into their system via their web service and extract data in
real time from their system. You and/or the client will have to speak
directly with ADP support for details on how to call their web service
and request specific data sets such as repair order data, invoice
data, etc.

b) Study the documentation for the file relationships (you'll have to
know how/where to obtain data from different files to compile a
complete data set), then write a series of data extract programs that
create XML or flat files at the UNIX level, then ftp the files to your
server and parse them there.

Regards,
Joe

drogers wrote:

ADP - you mean Microsoft's Access Data Project?

A Pick database is much more than simply data. The Pick DBMS has its
own application development tools imbedded within it. Many people
without any background in IT have developed application software using
Pick's PickBasic, Access Query Language, and the various other tools in
the Pick box. If ADP bypasses both Jet and ODBC and allows raw queries
through to the Pick DBMS then it might be possible to extract the data
you're after by passing AQL (Pick) queries through to a listening
process on the Pick server - I don't know. You can also interact with
Pick via ODBC (although ODBC appears to have had its day without
winning many friends within the Pick community), or use OLEDB via VB
(which might be of more interest if you're coming at this from a Microsoft
Access direction).

You could export the data from Pick and transform it and load it into a
different database. Probably a lot more trouble than it's worth - imo.
If you want to use SQL (the language) to connect directly to Pick then
you're using ODBC. Possible.

I really think the best place to start is to firmly grasp that the Pick
DBMS is much, much more than just data. Think of the web pages as the
presentation layer (ultra thin client), the web server as a mere
conduit (ultra thin web server), and the Pick DBMS as both application
server and database combined. When you see both the client and web
server as ultra-thin, and if you're prepared to take it as read that
the Pick DBMS is ultra efficient and yet surprisingly simple to use,
you will have the best start I could give you. You can then build on
this framework - smarts on the client/browser, tools on the web-server
and a whole load of smarts on the Pick side - if you like.

I would concur with others here who have suggested FlashCONNECT - as
one of many options. It comes in two parts - one part you load on the
web server, the other on the Pick application server (which can, in
fact, both be on the same physical box or, alternatively, you can share
many web servers with many Pick application servers). You would have to
write some PickBasic code to deal with requests from the web server.
Ask here for help with that if you need it - or search the archives for
tips.

Cheers
Mike.

(Thanks for the nod Jeff)

It's either weird or amazing coincidence that I'm having the same
conversation with someone else right now who's been doing a front-end
to ADP and R&D over Reality and some other flavor of Pick.

If this is the ADP that I think it is, you've got a special situation
that many people may now know about. To paraphrase Dan Rather, some
of those older systems are locked down "tighter than Rush Limbaugh's
bike shorts". Just getting to TCL can be a challenge.

You've seen some suggestions here. With FlashCONNECT for D3 you can
use a PHP front-end/wrapper. There's also a freeware project called
jD3 which has a PHP client interface. There are excellent .NET
solutions that run over any MV platform - I'll be merciful to our
colleagues here and only beat the drum if asked.

If you're on ADP you might be running Reality, they have their own SQL
processor, but then so do most of the other MV variants.

You need to tell us more about your environment before anyone can
really help:
- Which "flavor" of Pick do you have?
D3? jBASE? Universe? Unidata? Reality? Advanced Pick? Something else?
- Who is your VAR? Who supports that Pick system? What do they say
about a SQL interface?
- You ask for a SQL interface but are you just looking for a plug or
do you really need SQL? Can you tell us why? I find people often say
they "need" something but that's only because they don't know what
else is available.

Please feel free to contact me off-list. Like I said, this sounds
strangely familiar, and there are some nice solutions.

Tony
Nebula R&D
TG@ removethisNebula-RnD .com


drogers (AT) quirkcars-dot-com (DOT) no-spam.invalid (drogers) wrote:

"the linux side" ??


ah! perhaps there is light...

if you can get down to the command line (known as TCL in Pick), and you
have rights to edit things...

without purchasing 3rd party kit you ain't gonna deal directly - pick
has a database structure much like Novell even tho' it is within a
linux container.

there is an odbc client for windows, dunno if there's the same for
linux.

however, it's not that difficult to bypass the problem.

Data flow
-------------
Php creates a file in directory /poll containing request
or data
file name = data0001.req

Pick/Basic program polls /poll directory
- when file found, it is read and parsed,
- data extract program called
rename to data0001.pro (in process)

Program extracts desired data from file, creates csv file, writes
to /poll
file name = data0001.csv
deletes data0001.pro (process complete)

Php reads csv file, builds html, or xml
see d3mvwww project at sourceforge

php to resubmit if no reponse - deleting .pro items first

Hi Drogers,

There is a direct way to do this that is actually pretty uncomplicated.
In the past I have done this many times and the above approach hints at
a good method.

Placing the web site's data on a separate server is a good idea because
you can keep the production server buried within the corporate security
layers (firewalls, etc.).

Instead of exporting files then parsing them into mySQL, you can then
remotely connect to the web server from your PICK/BASIC programs by
shelling queries to the mysql client on the production server, directly
updating the mysql database on the web server.

Something else that you might consider is going to openQM on the web
server so that you can simply copy the data structures that you need. I
am currently working on a php4 interface to openQM that to the extent
possible mimics the php/mysql interface. Within the next week or so I
hope to have it in a 'stable beta' situation but it is fully functional
now if you would like to experiment with it. Since you are familiar
with the php/mysql setup, this might offer the ideal solution.

HTH,
-Tom