Re: MV and SQL

<michael_at_preece.net> wrote in message news:1137715461.014286.193420_at_o13g2000cwo.googlegroups.com...
> x wrote:
>
> > <michael_at_preece.net> wrote in message
> > news:1137679787.138095.286740_at_g44g2000cwa.googlegroups.com...
> > >
> > > x wrote:
> > >
> > > > <michael_at_preece.net> wrote in message
> > > > news:1137637589.943828.10850_at_z14g2000cwz.googlegroups.com...
> > > > >
> >
> > > >
> > > > Are you sure that a column in Pick is equivalent to a column in an
SQL
> > table
> > > > ?
> > >
> > > Well - not exactly equivalent obviously. Take, for instance, a
> > > ProductNumber field. Without defining it as any particular data type,
> > > you might have been using it for years to store numbers. They can be
> > > pure numerics of a fixed length - or of variable length, it doesn't
> > > matter. You can then start using that same field to store
> > > ProductNumbers with a suffix - as in 12345 & 12345-01 & 12345-02.
Then,
> > > later, the company using this file/table might undergo some change -
as
> > > in a takeover or something, and have a need to include ProductNumbers
> > > of a totally different "type". Say the new company has a similar
> > > product range and you want to include references to those products
> > > using the alternative codes. No problem - just start including product
> > > "numbers" 1234ABC-XYZ-01 in the same "column". Pick - or the Pick
> > > applications using the data - won't bat an eye.
> >
> > With an SQL DBMS you can also change the type from numeric to varchar
and
> > use that.
> > You can also change the type from varchar to array if your SQL DBMS
support
> > (out of the box or by writing an extension) arrays.
> >
> > I don't think that a Pick application that assumed that the column
contain
> > numbers and performed numerical operations with the data from that
column
> > would continue to work after such changes.
> >

> You do not have to attach any "type" to any field in Pick. You can go
> ahead and use any field in which ever way you want. If you at any time
> in future want to change the "type" of data you use a field for - you
> can go right ahead - while the file is in use, without any interuption
> at all to the running DBMS and/or application code. There simply is no
> data type to change - from varchar to array or anything else. I feel I
> am repeating myself - but it is something I see as illustrating a clear
> difference between the rigidity of SQL and the freedom of Pick.

And some application may or may not crash at the moment you enter a data of other "type".

> Now - if you want to add one field to another then obviously they have
> to contain numeric data. This is one of the clever things about Pick.
> Although any field can contain any kind of data - what *is* significant
> is the way the data is used. If you want to do something silly - like
> add one ProductNumber to another then there is nothing at all to stop
> you doing that. The only objection Pick might have is to raise a
> runtime warning if one of the ProductNumbers contains non-numeric data
> - in which case it treats the non-numeric data as zero and continues
> (unless you have error trapping switched on, in which case it throws
> you into the debugger). This is generally a bad thing to have happen -
> and usually indicates either a coding error or data corruption. These
> things can and do happen in an imperfect world from time to time. You
> can minimise the chances of this sort of thing happening though by
> ensuring you have adequate data integrity checking and decent
> change-control. You make your own rules.

Or some other user/developper change the rules without telling you.

> > With an SQL DBMS one can use a language that suport the Variant data
type
> > for variables that store the data retrieved from the database.
> > In PL/SQL one can declare a variable of being of the same data type as
some
> > attribute of a relation without saying what it is.
> >
> > > > Are you sure you are not confusing physical storage with logical
> > relations ?
> >
> > > Yes. I never mentioned anything about physical storage. Why? Correct
me
> > > if I'm wrong, but I get the impression that the demarkation between
the
> > > logical and physical in an SQL-relational environment is that anything
> > > that uses SQL is at the logical level and what the SQL engine does is
> > > physical.
> >
> > > There is no such clearly defined demarkation in Pick. You can read and
> > > write data and do absolutely anything with it you want to.
> >
> > Even if everything in a Pick database is a string, after you retrieve
those
> > strings and use them in some way, they are not just strings anymore.
They
> > become product numbers, phones, e-mail addresses, etc. This is the
logical
> > level.
> >

> Yes.

So you don't expect someone to change the usage of a column from "storing" e-mails to "storing" moneys. Good.

> >
> > > > If a column is called phones then it is expected to "store" phones
not
> > > > moneys and phones.
> > > >
> > >
> > > Yes. If you had some bizarre need to do this in a Pick DBMS though,
you
> > > could go right ahead.
> >
> > So you don't have multiple types for one field afterall.
> >

> No. None at all.
Ok. So what is the problem with SQL ? You can always define all fields as varchars (not type at all - just a string).

> > > > > > - have the liberty to define stored procedures for implementing
a
> > domain
> > > > > > specific language
> > > > >
> > > > > I guess you could say that everything in Pick is a kind of stored
> > > > > procedure. Hell - what's an unstored one? Bear in mind here that
we're
> > > > > talking about what goes on within the DBMS. As I said in the post
you
> > > > > responded to, a Pick DBMS application covers any or all of the
layers
> > > > > from client user interface down to file IO. As such, a Pick
procedure
> > > > > can execute SQL commands or be called from dictionary items used
> > within
> > > > > an SQL command. It can also be called from a VB application to
> > retrieve
> > > > > data. It can also be used to fire off an executable or generate
and
> > > > > execute script to perform a function on a server/client/browser...
I'm
> > > > > struggling here... Ask me a specific question about what it can't
be.
> > > >
> > > > I only said that an SQL DBMS can do this too with the exception that
one
> > > > cannot modify the DBMS.
> > >
> > > I'm confused. Could you please clarify this for me.
> >
> > Confused about what ?
>
> The Twighlight Zone, that Bob Dylan song about Mr Jones... oh - and
> what you said an SQL DBMS can do and yet can't do.

What an SQL DBMS can't do when one can do anything with it. If one have a string of infinite length and a general language one can program anything programable with it.

> >
> > > > > > - have the liberty to choose the programming language and
reporting
> > > > tools
> > > > > > used to access the database
> > > > >
> > > > > Ummm.. OK. Here's where I show my ignorance. I thought the only
way to
> > > > > get data into or out of a SQL DBMS is/was through SQL. I thought
that
> > > > > was the only way to access the database. If I'm wrong about that
then
> > > > > please feel free to educate me a little - and have a go at me too
> > while
> > > > > you're at it if you like.
> > > >
> > > > SQL is not the only language. It can be used with other languages
like
> > VB,
> > > > JAVA, C#, proprietary/standard stored procedures languages, C, C++,
> > COBOL,
> > > > ADA, etc. Some of these can be used to write code running "inside"
the
> > > > DBMS. Some of these can be used to extend the base types/indices
> > supported
> > > > by the DBMS. Some of these can be used to write code running
"outside"
> > the
> > > > DBMS.
> > >
> > > I could do with some clarification on this too. I'm quite familiar
with
> > > writing VB code to get data into and out of an SQL DBMS via SQL - but
> > > that's the only way I've ever accessed an SQL DBMS - via SQL. As might
> > > be clear to you and others, I am an SQL tenderfoot and a Pick veteran.
> >
> > SQL is always used with another language when writing an application.
> > That language can be a language implemented in the SQL DBMS such as
PL/SQL,
> > Java, C#, etc.
> > You do the same thing when you use DataBasic with the Pick query
language
> > and the other access functions.
> >

> You're still limited to using the SQLanguage aren't you - if you want
> to actually use (read/write) data in an SQL-relational DBMS? In Pick we
> can use that too - if we want to. Most choose not to.

Why do you hate SQL so much ?

> > > > Besides the query language of Pick (search, list, etc.) what other
ways
> > do
> > > > you have to access the data ?
> > >
> > > Although there might appear to be many, when it comes right down to
it,
> > > it boils down to just the query language (and aspects of it available
> > > through other means - as in the oconv function I alluded to earlier),
> > > the DataBasic programming language, C functions, SQL, and a handful of
> > > other commands you can issue from the command-line, not strictly part
> > > of the query language as such.
> >
> >
> > > > These access methods are for Pick what SQL + proprietary interfaces
are
> > for
> > > > an SQL DBMS.
> > > >
> > >
> > > Yes. The tool-box is quite extensive though. That is why I was saying
> > > it is a mistake for people to simply compare the query languages.
> > > Pick's query language is very cool - but it's by no means the be-all
> > > and end-all.
> >
> > It is also a mistake to compare a Pick system with SQL only.
> >
>
> Yes. I'd rather compare all of the tools available in an SQL-relational
> DBMS for getting data into and out of the database with all of the
> tools available in a Pick DBMS for getting data into and out of the
> database. On one side we have SQL - on the other we have SQL (rarely
> used) and a whole lot more.

No. One one side we have SQL + proprietary interfaces + (C or PL/SQL or something else) and on the other side we have Pick query language + proprietary interfaces + (DataBasic + C or something else)

> > > > > Of course people use all kinds of programming languages and
reporting
> > > > > tools on top of Pick. I, personally, prefer the browser-web
languages
> > > > > but am also using VB - and a lot of people combine aspects of
those
> > two
> > > > > by using ActiveX components. There are also a lot of Pickies out
there
> > > > > who prefer to use Java on top of Pick. There are also a lot of
people
> > > > > that use various reporting tools written in various languages,
some of
> > > > > them not just sitting on top of Pick, but actually written in
Pick -
> > > > > MITS and Datastage for example - which are often used as the sole
Pick
> > > > > application in an otherwise SQL DBMS environment (because they're
> > > > > judged by their users to be better tools than those based on SQL).
> > > >
> > > > I was not saying that Pick cannot do the above. I was saying that
one
> > can do
> > > > the same things using an SQL DBMS.
> > >
> > > You just can't though. You might be able to *use* SQL to do these
> > > things, but you can't write an entire application in SQL like you can
> > > in Pick - without any external add-ons. You can, of course, bolt
> > > anything on top either.
> >
> > You also cannot write an entire application using only the Pick query
> > language.
>
> No.

So why do you hate SQL ?

> > But one can write an entire application using SQL + PL/SQL + JAVA in
the
> > database.
> >
>
> I don't know about PL/SQL, but anything you use to develop an
> application with that uses SQL to access an SQL-relational DBMS can
> also be used to access a Pick DBMS. You can also choose not to use SQL
> at all. You can also choose not to use *anything* outside of the Pick
> DBMS itself and develop a full-blown application.

One can also choose not to use Pick query language or DataBasic when using an SQL DBMS :-)

> > > > > > - have the liberty to use the DBMS out of the box without any
other
> > > > tools
> > > > >
> > > > > If you ever get a DBMS out of the box that can't be used without
any
> > > > > other tools I suggest you send it straight back where it came from
and
> > > > > tell everyone about it.
> > > >
> > > > I was saying than one can describe the tables, enter the data and
> > retrieve
> > > > it whithout having to write various sorts of code like locking code,
IO
> > > > code, code for virtual fields.
> > > >
> >
> > > All that work you - or your DBA - have to do in rigorously defining
> > > every field in every column in every table... you can dispense with
> > > entirely in Pick. I have been a Pick application developer for a very
> > > long time and I have never yet come across a single Pick DBA.
> >
> > It is only an ilusion.
> >
>
> Have you been reading Jonathon Livingston Seagull or something? ;-)

No. I saw Matrix some time ago :-)

> > > >
> > > > > > Using a C compiler one even have the liberty to define its own
> > > > specialized
> > > > > > DBMS for an application domain.
> > > > >
> > > > > Again - I plead ignorance. Sorry. I thought you had to use SQL
with an
> > > > > SQL DBMS.
> > > >
> > > > I was talking about a C compiler not about an SQL DBMS.
> > > > Using a C compiler you have total freedom to develop whatever you
like.
> > You
> > > > don't have to use SQL or an SQL DBMS or even Pick.
> > >
> > > Yes. DataBasic code can be compiled down to C btw. I am still curious
> > > though - are there not "issues" concerning use of C to bypass SQL in
an
> > > SQL-relational DBMS?
> >
> > There is nothing to bypass when you are writing your own DBMS from
scratch.
> >

> S'pose not. (?)

If you want "total freedom" then why you don't use only C and a C compiler ?

If you want an analogy think of a Pick database as a file cabinet filled with folders full of sheets written by hand and of a SQL database as a file cabinet filled with folders full of printed forms filled by hand. Received on Fri Jan 20 2006 - 08:00:52 CET