Re: MV and SQL

<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.

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.

> > 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.

> > > > - 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 ?

> > > > - 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.

> > 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.

> > > 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.
But one can write an entire application using SQL + PL/SQL + JAVA in the database.

> > > > - 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.

> >
> > > > 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.

> >
> > > >
> > > > So what is the advantage of MV systems ?
> > >
> > > Over SQL-relational? Well - because you can use SQL - if you want - or
> > > you can go way beyond what SQL limits you to. I'll be keeping an eye
on
> > > Dawn's blog over the next year or so. I believe it'll go quite a long
> > > way towards answering this question - much further than I can at the
> > > moment.
> >
> > What SQL limits one to ?
> > SQL is only a sublanguage always used together with other general
purpose
> > language.
> > It's like saying that a Pick system is bad because the Pick query
language
> > is very limited (more limited than SQL in fact).
> > If one have an SQL DBMS with unlimited field size and a general purpose
> > language one can develop any database application using only one table
with
> > only one column and only one row. But that doesn't mean he should.
>
> Now you've lost me I'm afraid. Maybe you mean that everything inside
> the Pick DBMS - or more specifically the VME - is seen from outside as
> a single BLOb? This is true. That's the reason a lot of the Pick DBMS
> "flavours" also make use of "external" file systems for "Pick files".
> There's also a lot you can do yourself, as an application developer, to
> use the host OS's files directly - bypassing the Pick DBMS in a way.
> What I mean is - generally, when you access a Pick file, there is a lot
> going on within Pick to do with managing that interaction between
> application code and the physical storage - lock-handling etc., etc..
> You can, if you want, bypass much of this and "reach out and touch" OS
> files and sockets etc. from within your Pick application. There really
> are a lot of options in Pick.
Received on Thu Jan 19 2006 - 17:04:42 CET