I've got a project of batch uploading some data into an ingres database.
I'm not familiar with a UNIX environment in general, as well as ingres
specifically...

I've been emailed some basic instructions on how to make this work, but I
would like to understand what I'm doing rather than simply "black-boxing"
it. I've tried to find some basic documentation on ingres but have been
unable to locate any. It looks like we're running Version II 2.6/0305 if
this is any help...

At this point I would like to know of some quick and dirty way of viewing
data in a table. We're using an xterm window as an interface to the UNIX
system from our windows desktops...

Hopefully this is enough info to get a dialogue going. Please HELP.

Jeff

"Jeff Perreault via DBMonster.com" <forum@nospam.DBMonster.com> wrote in
message news:bc02ec2d1cd24628a7750a704efa6e53@DBMonster.co m...
> I've got a project of batch uploading some data into an ingres database.
> I'm not familiar with a UNIX environment in general, as well as ingres
> specifically...
>
> I've been emailed some basic instructions on how to make this work, but I
> would like to understand what I'm doing rather than simply "black-boxing"
> it. I've tried to find some basic documentation on ingres but have been
> unable to locate any.

You can find some at ftp://ftp.ca.com/CAproducts/ingres/docs/Ingres_26/.
First stop will probably be SQLREF.PDF, to read about the non-standard
Ingres SQL "COPY" statement.

>It looks like we're running Version II 2.6/0305 if
> this is any help...
>
> At this point I would like to know of some quick and dirty way of viewing
> data in a table. We're using an xterm window as an interface to the UNIX
> system from our windows desktops...

Well without knowing what you are familiar with, it is hard to suggest
something you are sure to like. Personally I like the bare-bones Ingres sql
command, run from a PC client in a DOS window with scrollbars. Or you might
prefer the isql monitor within a VTn00 emulator like Reflection (which I
don't like at all). Or you can use the Ingres VDBA tool (if you can take
enough time to wrestle it to the ground and find out where it hides all its
Easter eggs). Or you can set up Ingres as an ODBC data source and use
anything like MS Access or Excel. Or you can use any of the gazillions of
JDBC-enabled database browsers (e.g. DBVisualizer, see
http://www.dbvis.com/products/dbvis/).

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"

At 3:48 AM +0000 6/2/2005, Jeff Perreault via DBMonster.com wrote:
>...
>
>At this point I would like to know of some quick and dirty way of viewing
>data in a table. We're using an xterm window as an interface to the UNIX
>system from our windows desktops...

You could try qbf. You may have to fool around a bit to find a
TERM_INGRES setting that works for you. If you can convince your
terminal emulator to send vt100-style function key sequences, you
could use TERM_INGRES=vt100f.

Karl

"Roy Hann" <specially@processed.almost.meat> writes:
>Personally I like the bare-bones Ingres sql command, run from a PC
>client in a DOS window with scrollbars.
me too... except that i run it in an emacs window... nothing like
being able to have good scroll back, ability to cut/edit/macro process
the data, etc.

Emacs, it's not just a tool, it's a way of life.



--
be safe.
flip
Ich habe keine Ahnung was das bedeutet, oder vielleicht doch?
Remove origin of the word spam from address to reply (leave "+")

"Philip Lewis" <flip+spiced_ham@andrew.cmu.edu> wrote in message
news:qvj4qcgpxvb.fsf@unix42.andrew.cmu.edu...
> "Roy Hann" <specially@processed.almost.meat> writes:
> >Personally I like the bare-bones Ingres sql command, run from a PC
> >client in a DOS window with scrollbars.
> me too... except that i run it in an emacs window... nothing like
> being able to have good scroll back, ability to cut/edit/macro process
> the data, etc.
>
> Emacs, it's not just a tool, it's a way of life.

I think we've had this conversation before! :-)

Roy

"Roy Hann" <specially@processed.almost.meat> writes:
>"Philip Lewis" <flip+spiced_ham@andrew.cmu.edu> wrote in message
>> Emacs, it's not just a tool, it's a way of life.
>I think we've had this conversation before! :-)
well... no doubt i've had the conversation before, as with all good
acolytes, i like to proselytize.

--
be safe.
flip
Ich habe keine Ahnung was das bedeutet, oder vielleicht doch?
Remove origin of the word spam from address to reply (leave "+")

First and foremost, thanks to Roy and all the others that have posted
responses to this request...

I need to toss out the additional caveat that I'm not familiar with
terminal emulation in general. I remember something about vt100 in some
dusty corner of my gray matter, but that's about it! I've been set up to
use Reflection (unfortunately it sounds like) as an interface...

Please continue to bear with me... I am a neophyte (as you all can tell!).
Here's a synopsis of where I am:

I've inherited a task of batch uploading some historical data into a table
in an Ingres database. The paper source is being keyed into an Excel
spreadsheet where it is being desk checked. Step 2 will be to save the
spreadsheet data as a .csv file for upload (merged) into the Ingres
database. Step 3 will be to execute a series of commands to first enter the
Ingres environment, and then to merge (insert) the data into an existing
table. The commands are:

SQL [database]
CREATE TABLE new_table (fields and their attributes);commit;\p\g
COPY new_table (field list) FROM [.csv file];commit;\p\g

perform some validation, then...

INSERT INTO [real_table] (target field list)
SELECT (source field list)
FROM code_table a, new_table b
WHERE a.cd1=b.cd1 and a.cd2=b.cd2;commit;\p\g

again validate, then...

DROP TABLE new_table;commit;\p\g
\q

I've made the above as generic as possible while trying to keep it complete
enough to allow everyone to evaluate the steps.

So as of now I've got a few questions:

1) how do I set up a path designation so that the .csv file can be found?
2) why is there a "commit" clause following every command?
3) how can I "view" the data in new_table generically?

One of the responses targeted 3) above, but I'm not sure how to implement
the advice. Please remember that I'm a newbie! I don't think I know enough
to even be considered dangerous...

Again, thanks to all of you for your responses. Please continue to give me
advice on how to proceed with this task...

Jeff

ps. there's another part of my old gray cells that recalls something about
commit control and rollback. I'm guessing that this is what the "commit"
clause is referring to, but if that's the case then what's the value of
putting it in the script after the CREATE command? So I'm wondering if
there's more to this then I'm recalling... I'm also guessing that the WHERE
clause is doing some sort of "join" function, and that the FROM clause is
not only defining source tables for the INSERT but also providing a sort of
table identification shorthand because the fields in the SELECT clause are
all prefaced by the single letters associated with the respective tables in
the FROM clause ("a" and "b" in my example). Again, if there's more to this
than meets my eye please let me know. Although this is a "quick and dirty"
task, we think it may be something that we'll use more than once, and so
therefore want to know something about what we're doing. (!) -j

--
Message posted via http://www.dbmonster.com

"Jeff Perreault via DBMonster.com" <forum@DBMonster.com> wrote in message
news:b2a40db940b347eabd78a8b03faa8243@DBMonster.co m...
> First and foremost, thanks to Roy and all the others that have posted
> responses to this request...

Welcome.

[snip]
> SQL [database]
> CREATE TABLE new_table (fields and their attributes);commit;\p\g
> COPY new_table (field list) FROM [.csv file];commit;\p\g
>
> perform some validation, then...
>
> INSERT INTO [real_table] (target field list)
> SELECT (source field list)
> FROM code_table a, new_table b
> WHERE a.cd1=b.cd1 and a.cd2=b.cd2;commit;\p\g
>
> again validate, then...
>
> DROP TABLE new_table;commit;\p\g
> \q
>
> I've made the above as generic as possible while trying to keep it
complete
> enough to allow everyone to evaluate the steps.
>
> So as of now I've got a few questions:
>
> 1) how do I set up a path designation so that the .csv file can be found?

You can't set up a path within an sql script. Either you keep the csv file
in the directory from which you invoked the sql monitor, in which case you
give just the file name, or you give the fully qualified path, complete with
drive letter if needed. However if you are using a unix, you can embed your
SQL in a shell script as follows, and the usual variable substitutions will
be made:

export FILEPATH=[filepath]
sql [database] << EoSQL
CREATE TABLE new_table (fields and their attributes);commit;\p\g
COPY new_table (field list) FROM $FILEPATH/[.csv file];commit;\p\g
....
\q
EoSQL

> 2) why is there a "commit" clause following every command?

Odds are because someone long ago was told, "if in doubt, commit". But
there might also be a good reason.

> 3) how can I "view" the data in new_table generically?

Since you've got Reflection, let's go with isql. You are unlikely to find
it as restrictive today as you will once you know more about what you are
doing. Start Reflection and log in. Run isql [database], and enter SELECT
* FROM new_table, then choose "Go" from the menu. (That will usually mean
pressing Num Lock--no, really--and typing "go" followed by enter, or more
mysteriously still, hold down control and press F6. Pure looney tunes; an
entire generation has entered the workforce since that stopped making any
kind of sense. But I digress.)

[snip]
> ps. there's another part of my old gray cells that recalls something about
> commit control and rollback. I'm guessing that this is what the "commit"
> clause is referring to, but if that's the case then what's the value of
> putting it in the script after the CREATE command? So I'm wondering if
> there's more to this then I'm recalling...

There is *lots* more to it. I very nearly wrote a paper for this year's
Spring UK IUA conference that was going to be called, "COMMIT: The least
understood statement in SQL". Your points about locks above are not
incorrect, but that is not really the point of COMMIT. In a nutshell,
COMMIT tells the server that you assert that you have restored the database
to a logically consistent state. That has the side-effect of revealing your
updates to other users. In Ingres (and many other DBMSs) that is done by
releasing locks. Unfortunately the kind of locks that are used are a
makeshift solution that only approximate what is required, and they have
some undesirable and disruptive side-effects that tend encourage people to
COMMIT a bit too eagerly. I could go on. For instance, I could ask how Joe
Average Programmer is supposed to know that he's restored the database to a
consistent state? But I digress again.

>I'm also guessing that the WHERE
> clause is doing some sort of "join" function,

Actually no. The WHERE clause is purely a restriction; it only tells the
server how to discard meaningless combinations of rows.

> and that the FROM clause is
> not only defining source tables for the INSERT but also providing a sort
of
> table identification shorthand because the fields in the SELECT clause are
> all prefaced by the single letters associated with the respective tables
in
> the FROM clause ("a" and "b" in my example).

That is true, but it is also "doing" the join of the tables. You can think
of it as causing all possible combinations of rows from all the named tables
to be generated and passed to the WHERE clause for restriction. (Of course
that is not what really happens, but that is how you are supposed to think
about it.) This will be important if you ever find yourself having to use
the ANSI outer join syntax.

> Again, if there's more to this
> than meets my eye please let me know. Although this is a "quick and dirty"
> task, we think it may be something that we'll use more than once, and so
> therefore want to know something about what we're doing. (!)

Yes.

Roy Hann (rhann at rationalcommerce dot com)
Rational Commerce Ltd.
www.rationalcommerce.com
"Ingres development, tuning, and training experts"