We have an archaic solution here using D3/NT with VME (I hope I have the
terminology here). Our sister companies all have better ways of creating
reports (via ODBC, etc) using Microsoft Access and Crystal Reports. We are
stuck with reports that are built in Pick to the point we have PCL codes
embedded in them. This makes it so that only a Pick programmer can make new
reports for us if we cannot pull the data off with wIntegrate.

I know D3/NT has an ODBC interface. How well does this work with any ODBC
capable software like Access, Crystal Reports, ASP/PHP/web, etc?

I noticed there were some issues with ODBC creating sessions and not
releasing them thus using up all the licenses. Has this been resolved? Is
there any other downside to even suggest to migrate some of our tasks
through to a ODBC-enabled solution?

If you have a current support contract, upgrade to the latest 7.4.5
release & give it a rip. I understand 7.5 is just around the corner,
and may have some new stuff on this front.

There are also 'native', non-ODBC solutions that can give you Crystal
like results, and depending on the application, data warehouses can
also have a HUGE impact. We have replaced >400 'traditional' reports
with a single cube !

All comes down to $, and also the value you put on your own time in
"stuffing around" - if you aren't a programmer, then even with ODBC
route you would probably need some input to make current
dictionaries/files available to ODBC

I have a client who wanted to pull out data via ODBC; so we plugged it in.

The hardest thing is that for the files & definitions you want to have
access to; you have to create the ODBC entries from the d3/NT side.

In other words; you are back the the age-old problem of needing to know the
question you want to have answered, and how that relates to your database
schema before you start.

But, once created it works well.

One problem that we found was that because ODBC compliant programs are so
picky [excuse the pun] about data typing; if they found unusual data in d3;
the odbc tables would stop filling.

Good luck

David
"Joe S Moore" <joe (AT) joeiscool (DOT) com> wrote

David, I'm not negating your comments, just disagreeing with some of
the implied spin.

"David Knight" <davidk (AT) matash (DOT) com.au> wrote:
This is all part of a simple sql-create-table statement. Hardly a
show stopper. You're right that this is the "hardest part" only
because it may take some time to do this, but you generally only do it
once - or until you get it right.


Not really. In any relational database the schema has to be defined
up front before the data is even inserted. We Pickies have the luxury
of being able to defined views after the data and apps are in place.
You can create multiple views depending on the needs of the client.
It's better to consider that a feature rather than a problem - and
that's not spin, that's really a benefit of the platform.

Also, depending on the size of the site, using software like MITS they
can build a hypercube which will have "questions pre-answered" so to
speak, so that management just needs to figure out what questions they
want to ask.


Despite what appears to be support on my part above (I'm just trying
to be fair), personally I don't like the D3 ODBC implementation, it's
been a series of headaches for many sites for years. That's only my
opinion, Mark Brown has had great success with it - beauty is truly in
the eyes of the beholder.


Well, that's another feature. Data typing is a serious thing in
flatland and if your MV data is dirty then having some external tool
validate it can be very helpful. If your primary data isn't type-safe
then it may be better to create a separate file which is only used for
ODBC queries, build your SQL TABLEs off of that, and don't expose your
primary data to the outside world, which I personally think is a very
bad thing to do anyway. This process is sort of what ETL is all about
-- Extraction (from MV), Transformation (cleaning it up), and Loading
(to somewhere else).


Joe, your D3 NT system is very capable of doing lots of amazing things
in terms of reporting and sharing data with other environments. If
you want to access your data from outside there are many ways to do
so, but someone there needs to (1) define exactly what the
end-objective is and (2) know how to achieve that objective. We can
handle that second part and help you to pull data out of D3 in a
number of ways including VB, PHP, CR, and ADO.NET - all without ODBC,
though as I said above some people consider that an excellent option
as well. I'll be happy to discuss your needs with you but if you're
looking for free solutions then be prepared to do a lot of homework.

Good luck from here too.
Tony, Nebula R&D
TG@ removethisNebula-RnD
..com

"Tony Gravagno" mentioned my name

<snip>

Beauty? Are you talking about ME or ODBC?

If you're talking about ODBC, it works fine.

If you're talking about me, you had too much to drink the other night.

Mark

Hey Tony,
Think we are basically saying the same thing, didn't mean to imply any spin.

I realise I may have made an [invalid] assumption: which is that the writer
does not know the names/use of the various dictionary items; or his way
around mv. In my experience with any db system be it mv or not; is that
users want "report writers" because "I may want to report on something".
Many tools exist in all environments. However the tool does not give them
the knowledge of the schema to the degree where they understand what field
to use where to achieve a given result. Unless you are a "power user".

In my field I often get calls asking for reports, where the question is
vague. I agree that the sql-create-table command is not difficult; my point
was: how much do you expose of each file? It does take someone versed in mv
to interpret the fields and flatten it out where necessary, especially
associated mv sets. I guess you could expose all of it. That is what I meant
by the "age-old" bit. I agree with you, multiple view is a good feature of
mv; I was not having a go at it; merely making a comment about db systems in
general vis-a-vis adhoc reporting. Putting this another way: just because
the schema can appear to Crystal etc does not mean his problems are solved.
That was what I was getting at...

Cubes are great also, but even they require a degree of pre-defining the
reports you want, as you point out.

The data typing created a problem because although it would "validate" [sort
of], it was difficult to find the point in the database or the reason for
the data-type problem. I cannot remember the specifics now; but one problem
encountered was because of data that was legal/valid etc in d3 in accordance
with the schema; was not valid according to strict "typing" rules in
SQL/ODBC. Wierd. Modified the value to something else and it all flowed.
This is not a "fault" of either side, mv or OBDC, but was a "difficulty" in
the process.

I guess your final comment is the important one: if you are looking for
free, be prepared to do a lot of homework. I agree, and that is what I
meant: ODBC can work, but is it the solution to the problem?

Cheers from downunda...

David

"Tony Gravagno" <g6q3x9lu53001 (AT) sneakemail (DOT) com.invalid> wrote

Yup, no disagreement. It's funny how these rules of thumb keep coming
back. I just got off the phone with a new prospect. She's had the
typical lecture from relational guys about what a featureless black
box Pick is and how much better MS Access is (!!). After assuring her
that her Pick DBMS is as capable as the other stuff, and that we can
exchange her data with anything they have, we discussed options for
integration with Excel, MS Access, web sites, and "the real world".
She then asked me for an estimate to make it all work. My response
was "how high is up?" Without knowing anything about their
application and with no specs for an actual deliverable, how can I
possibly give an estimate? We'll exchange some notes this week to
assess needs and goals and then meet next week to see what they have
to start with.

Just as a further anecdote, it's amazing that the solution being
proposed to replace the MV application in question is referred to as
MS Access - all they discuss is the database, there's been no real
discussion that I could discern about whether the application tied to
Access will adequately replace their existing app which they've been
running for over 20 years. Many of my questions focused around
whether the new proposed solution will really help them to run the
business. This remains to be seen. What I am understanding so far is
that the user will be required to learn MS Access and maybe do her own
SQL queries - even though she's quite comfortable with English/Recall.
So the new "solution" is really just another tool to replace the
existing tools, which actually have been a solution for many years.
It'll be interesting to see where this goes.

It's just like this situation with Joe the OP, the tools are
available, but these sites just need people like us (fellow CDPers)
who are capable of properly wielding the tools.

T

"David Knight" <davidk (AT) matash (DOT) com.au> wrote:

I have ribbed you here about your devotion to the Redmond folks.
There is an issue involved about which I have hinted but haven't
taken the time to properly put together. Your anecdote mirrors
many of my experiences and provides a nearly perfect example of
this underlying issue.

While I am top posting, your words are below if anyone wishes to
reference them.

Please consider the conversations that have led to the decision to
consider replacing the PICK application with the Micro$oft Access
non-application:

The owner/division manager/IT manager/whomever (hereafter referred
to as Person Who Controls the Money or 'PWCM') has dealt with you
or someone like you for many years. You have provided solutions that
work, that get the job done and they have no real complaints about
the current MV based application.

PWCM's 19yo nephew Jim joins the company as an intern to fill the
summer between his freshman and sophomore years at state university.
Jim knows nothing about business and his knowledge of computer systems
is limited to that which he gained from friends and the basics from
100 level college courses.

Being interested in computers (he is considered nothing short of a
full fledged hacker by his gaming buddies), Jim takes a look at the
mission critical application upon which the company relies daily.
His reaction is a heartfelt 'Ewwwwwwwwwwwwwwwww, this thing is older
than Grandma!!!'. Given this knowledge, he approaches Uncle PWCM with
a plea to modernize the computer system.

Since PWCM _has_ had occasional difficulty getting information he
needed from the PICK system into his Excel spreadsheet, Jim is most
likely correct ... so let's ask him for suggestions.

'Well if you used a Microsoft DBMS, it would easily interface to all
of the Microsoft applications that Tony G. has sold you over the
years.'

PWCM contacts you asking your advice on this and you tell him that
you can make the PICK system do virtually anything short of flying
the Space Shuttle (which isn't much of an exaggeration).

I will omit any negative reaction that a flippant 'How high is up?'
reply to a cost estimate request might leave but:

When PWCM consults his trusted assistant on this, assistant applies a
bit of critical thinking and suggests:

'Well, you know that Tony has touted Microsoft products for years, so
they must be what we need to accomplish the computing tasks.'

PWCM then asks trusted assistant why you were against using the
Microsoft DBMS (non) solution. The reasonable answer:

'He is tied to that old system, it is the only thing he knows and he
is the only one who knows anything about it. Seems a bit odd that all
of the _other_ Microsoft software is great but the one that competes
with his is substandard???'

And exactly why would PWCM want to hire those who are capable of
properly wielding the tools when any high school kid is capable of
wielding the Microsoft Access tools (at least in the eyes of Uncle PWCM)?

'We have to call Tony every time that we have a new client who needs
to get data through e-mail. Since Access comes from the provider of
all of our other software, we can just plug it in.'

Why would PWCM wish to purchase the expensive per-seat licenses
required by PICK solutions?

In other threads you speak of political decisions. Your little drama
is a great example of how these politics play out in the real world.

You observed that one of the arguments against PICK was that it was a
'black box'. Think about that: To the PWCM and his company, they all
are black boxes. Why shouldn't they use the one that was designed to
play easily with all of their other black boxes?

Thanks for the great example!
-Tom


Tony Gravagno wrote:

Argh, sorry for not responding earlier (combination of internet down and
work). Thanks for all the replies. Yes I want a more robust system and
something like an ODBC would work great if it can actually work. From the
posts here I'd say it can but with a lot of cavaets. Now why do I want ODBC?
Well to make it plain and simple, I understand it, I like making SQL queries
that will work without knowing anything about D3/NT. I can build
applications that can be ported easily to another database when necessary.

What is at stake here is that we are at the mercy of a very few people being
able to provide solutions. If a D3/NT system is severely handicapped to a
point that it requires a lot of finessing to get it to work then it is not a
viable solution at all.

Back to the question at hand, is ODBC on D3/NT realible enough to be
trusted? Well I'm not afraid to get my hands dirty into D3/NT, any pointers
on where to start would be greatly appreciated! So far I have got references
to non-ODBC tools like MITS and something about a cube, as well as some sort
of ODBC entries you need to provide on the server side.

Thanks all!

Joe.


"Tony Gravagno" <g6q3x9lu53001 (AT) sneakemail (DOT) com.invalid> wrote

Oh my, I Have been pegged as an Microsoft clone. It seems people forget my
rhetoric about "right tools for the task" and "tools are irrelevant".
I advocate what fits the client, their politics, goals, technology
choices, and internal skillset - not what's good for me. I've made a
career of being able to work with pretty much whatever comes along, so
it's very tough for me to be staunchly on one side of a fence or
another because I can easily switch debating teams and still carry on
the argument. Tom, I think you and I would do much better chatting
over a beer or lunch than in this medium where the "gray" areas get
lost in out of context sound bites.

For the record, this particular end-user that was referred to me was
not my client and I've never worked with them before. Note the word
"prospect". Their original VAR who left them as orphaned used to hold
the app together but maintained that Pick doesn't do ODBC or web or
Excel integration, etc, so after hearing that for years they were
compelled to consider an alternative solution. (That's unfortunately
another real typical scenario that I've been fighting for years.)
Only after they found another prospective solution did they start
looking for someone to assist in a migration, and that's when they
were referred to Nebula R&D.

Next, your story about PWCM and nephew Jim is indeed the way a lot of
sites manage their IT requirements. But I've been in this business
long enough to recognize that scenario, and I'm cautious to avoid the
story playing out as you've described.

Regarding "how high is up", you can't seriously think I'd drop a
comment like that and leave it there. In context, the phrase made the
contact realize that she was asking for an estimate to do "something"
which was un-defined, from someone who knows nothing about what she
has or what she wants. In conversation this wasn't flippant but
actually brought some levity to an otherwise serious discussion. This
also lead to a proposal that I make a pro bono courtesy call to see
what they have and see what can be done, with or without my services.

I see I didn't mention this here, but if the judge asks I can prove it
in court - I've already told the prospect in writing that my
recommendation may indeed be that they migrate rather than integrate
with the new proposed app/database. Based on their business needs and
budget requirements, staying with Pick may not be the best thing for
them, and adopting something more mainstream which is standard in
their industry may save them money and a lot of hassle. This remains
to be seen - right tools for the job - tools are irrelevant - I don't
walk in trying to save Pick, I try to provide what the client needs.

Anyway, I don't think you've taken any pot shots at the way I do
business but it's obvious that you're coming to conclusions without
all of the info and I thought it required some response. Again, this
medium doesn't allow all the facts to come through as we might hope,
but people respond to what they see anyway. Have a little faith, I
don't actually conduct business with the same attitude or knee-jerk
response mechanisms we see in this forum. Maybe a couple of my
clients will confirm that for us, I dunno.

Regards,
Tony

"Tom deL" <ted (AT) blackflute (DOT) com> wrote: