HDTenEightyP/NetNews huggingface dataset:
From: drcoles@somnet.sandia.gov (Dwight R Coles)
Subject: Re: Dynamic FRS
Organization: National Radio Astronomy Observatory
Date: Mon, 17 Aug 1992 21:54:03 GMT

>  (Mark Jaeger replies)
> (Ron Haukenfrers) writes:
> > 
> > Does anyone know of a way to dynamically create a field in an ingres form?
> > I would like to create a field at runtime because it's datatype, length
> > and attributes are all derived at runtime.
>
> This question has certainly come up before, and the sad answer is...no. 
> With standard character-cell-based FRS, you just can't do it.  You might
> be able to reverse engineer the forms system catalogs (ii_object,
> ii_forms, ii_fields, and ii_trim), and create a form on the fly that
> way, but it's probably a whole lot of work and not certain to succeed.
> 
Yep, this CAN be done!  I reverse engineered the FRS system catalogs
enough to build a dynamic QBF capability for engineering data display,
where I didn't know until run time what the user wanted to display.
It is a lot of work, but surprisingly, it ran quickly.  In addition, it
had the capability of printing nicely formatted reports, based upon the
same meta-data about the fields/columns.  
 
You don't get to use compiled forms, because you end up writing new
form definition data into the system catalogs (win some, loose some).

If you need the reverse-engineering information about what is where, 
let me know.

Dwight Coles    drcoles@sandia.gov
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Aggregates Updates in SQL ?
Organization: 
Date: Tue, 18 Aug 1992 22:57:46 GMT

In article <1992Aug18.135102.14295@jupiter.sun.csd.unb.ca>, 
leath@atlas.cs.upei.ca (Stephen Leath) writes:
> 
> How does one update a table with an aggregate function and a group by clause
> in sql ? I would like to sum a column in one table by another column and
> update a table with the result. 
> 
> In quel it would be:
>           replace table_1(x=sum(table_2.y by table_2.z 
>                             where table_1.z = table_2.z)) 
>             where table_1.z = table_2.z
> 
> In oracle sql :
>            update table_1 a
>                set x = (select sum(y) 
>                            from table_2 b
>                            where a.z = b.z
>                            group by z);
> 
> Thanks in advance 
> Steve
> leath@upei.ca

I don't think you can use "group by" directly in INGRES updates.  You
should be able to do it, however, if you define a view:

	create view view_2 as 
		select z , sum_y = sum(y) 
			from table_2 
			group by z 
		;

	update table_1 a
		from view_2 v
		set x = v.sum_y
		where a.z = v.z
		;

If you're dealing with massive amounts of data, then performance might
be terrible.  If this is the case, then I would suggest running the
update first with "set optimizeonly;" to see what the server thinks it's
going to have to do.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.
From: kmchylla@vms.macc.wisc.edu
Newsgroups: comp.databases.ingres
Subject: Re: ESQL Bug
Date: 20 Aug 92 15:24:07 GMT
Organization: University of Wisconsin Academic Computing Center

>kmchylla@vms.macc.wisc.edu writes:
> 
>|I wrote an embedded SQL program using C that seems to go into an
>|infinite loop when I have too many records to process, and
>|I can't figure out why.  The program simply sets up a
>|cursor to read each record, take a character field (storing
>|a gregorian date) and convert it to a date.  It updates another 
>|field in the same record (a date field), and fetches the next record.   
> 
>Richard Begg (richard@asis.unimelb.edu.au) answers:
>Let me see if I've got this right...
> 
>You have a cursor on which selects rows from a table, performs
>some calculation then updates the row.
> 
That is correct, sir! :-)

>Have you declared the cursor for update?
Yup!

>How large is this table?
~150,000 records

>How far does it get before it loops?
On the 6610, the program will work if I cut the table up into smaller
chunks of about 70,000 records.  On the 4000, the table must be
less than 20,000.

> 
>Just a suggestion:  This sort of operation is *VERY* inefficient in a
>relational database (we found out the hard way!).  If it is at all possible
>to perform this operation in raw SQL operations rather than
>using a cursor loop, then I would go for it, especially if the data set is
>large.
> 

I'd love to, but don't know of a way to take an unconventional
gregorian date and convert it to a regular mm/dd/yy kind of date.
Besides, I really am getting curious as to why a program would 
start looping without giving any SQL errors, any errors in the 
Ingres errlog, or VMS errors.

Thanks for your questions!

Kathy Chylla
Newsgroups: comp.databases.ingres
From: mmr@asgserver.canberra.edu.au (Micheal Rainford-Watson)
Subject: Re: FAQ
Organization: University of Canberra
Date: Fri, 14 Aug 92 02:29:15 GMT

In article <27258.9208101855@central1.lancaster.ac.uk> Mrs C Snape <carole@central1.lancaster.ac.uk> writes:
>
>Please excuse my ignorance........but what is FAQ ?
>
>--Carole Snape
>INGRES Consultant/Training Officer
>Lanccaster University

That's Ok Carole, because this is a frequently asked question.

-- 
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
|Micheal Rainford-Watson        _--_|\   E-Mail: mmr@asgserver.canberra.edu.au|
|Administrative Systems Group  /      \  Phone : +61 6 201 2397               |
|University of Canberra        \_.--.*/  Mail  : PO Box 1 BELCONNEN ACT 2616  |
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: A question and a Request
Organization: 
Distribution: na
Date: Tue, 18 Aug 1992 22:27:11 GMT

In article <1992Aug17.142452.24812@itm.uucp>, danny@itm.uucp (Danny Cox) writes:
> We get many requests for the 'top N' rows from
> a table.  How does one accomplish this in SQL?  Sorting is no problem,
> but how does one request the first N rows?  Obviously one could do an
> 'endloop' to stop once N is reached in 4gl/ESQLC, but is there a better way?

You can do this directly in a single SQL statement, if your table has a
column of unique values that defines the order.  I've attached a simple
example.  I won't say it's better, and in fact using a select or cursor
loop will probably give better performance in general.

> I saw today an article requesting regular expressions in SQL.  I 
> quite agree.  I'd like a 'conditional expression' as C has, as well.  
> I could certainly use one currently!

I think I know what Ingres might say about regular expressions: buy the
Object Manager and write your own!  You can get limited additional
functionality by using the QUEL extensions to the LIKE predicate (using
ESCAPE and the class operator).  As far as "conditional expression"
goes, you had better talk to the ANSI committee about this.  8^)

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

================================================================================
select * from t order by id ;
+------+
|id    |
+------+
|    10|
|    20|
|    30|
|    40|
|    50|
|    60|
|    70|
|    80|
+------+

select * from t a where 3 >= ( select count(*) from t b where b.id <= a.id ) ;
+------+
|id    |
+------+
|    10|
|    20|
|    30|
+------+

select * from t a where 3 >= ( select count(*) from t b where b.id >= a.id ) ;
+------+
|id    |
+------+
|    60|
|    70|
|    80|
+------+

Newsgroups: comp.databases.ingres
From: Cristyn Emmett <Cristyn.Emmett@newcastle.ac.uk>
Subject: IPM (and thanks)
Organization: National Radio Astronomy Observatory
Date: Mon, 17 Aug 1992 17:53:53 GMT

I have said to several people that we don't have IPM here with our
Ingres 6.3/01.
Well, I have recently discovered that we do (all the help files are
empty, but the executable is there) so all I have to do now is sit
down with the documentation and try to understand it :-)

I appreciate this group - so useful - just wish I could contribute
a bit more.

Thanks to all you helpful people,

	Cristy

Also known as:       
cristyn.emmett@newcastle.ac.uk
******************************
Newsgroups: comp.databases.ingres
From: croft@csusac.csus.edu (Steve Croft)
Subject: Re: Transactions
Organization: California State University, Sacramento
Distribution: usa
Date: Fri, 14 Aug 92 14:46:05 GMT

You'll remember from last time when I wrote:

>When SQL statements update a database (before a commit statement),
>is the database actually updated?  This would mean that the tx
>log would hold info to rollback the changed info from the database,
>right?
>
>For some reason, I thought the tx log contained the new info, which
>would update the database upon commit.  Can someone clarify?
> from the logfile... is that correct?

Well, I found out that INGRES updates the database immediately upon any
type of modification instruction (update, insert, delete, etc.)  The log
contains info to restore the database upon rollback, if needed.  I would
like to thank Steve Causwell of The Palmer Group for clearing this up
for me.  Steve presented a paper at INGRES World '92 about locking;
it mentioned a little about rollbacks which led to my original question.  
I want to thank Steve for clearing this up a bit for me.


Steve Croft
stevec@water.ca.gov
From: kerry@Ingres.COM (Kerry Kurasaki)
Newsgroups: comp.databases.ingres
Subject: Re: SQL 'like' command with case insensitivity?
Date: 12 Aug 92 17:37:14 GMT
Reply-To: kerry@Ingres.COM (Kerry Kurasaki)
Organization: Ask Computer Systems Inc., Ingres Division, Alameda CA 94501

In article <1992Aug12.002043.1970@candle.uucp> root@candle.uucp (Bruce Momjian) writes:
>I need to do an SQL statement where I need pattern matching that is
>case-insensitive.  In both SQL and 4GL, this statement is illegal:
>
>	select *
>	from tablea
>	where uppercase(col) like '%YES%';
>
>It complains about the 'like'.  I have called Ingres.  The tech first
>thought it was an easy solution, then he talked to some people and
>basically says it's a limitation.
>
>The only two workarounds I can think of are to add another column that
>always has 'col' as uppercase, or in 4GL to attach a block of code to
>the select and for every row, uppercase the field, and then compare with
>a select.
>

Unfortunately, the grammar for the LIKE predicate is:

	<column name> LIKE <expression>

Another method is to create a view with the uppercase() function.
You will not be able to update it though.

Kerry
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Newsgroups: comp.databases.ingres
Subject: Re:  Looking for a USABLE database
Date: 14 Aug 92 23:47:05 GMT

In article <4474@disuns2.epfl.ch>, baechler@liasun2.epfl.ch 
(Emmanuel Baechler) writes:
> In article <9208031347.AA07101@tpghq>, sfc%tpghq@uunet.UU.NET 
> (Steve Caswell) writes:
> |> I agree that the inability to add/remove columns from a table is a severe
> |> limitation.  There are people out there who have written software to do
> |> what you suggest.  INGRES comes with an unsupported utility which does this.
> 
> Where can we get this utility?

I think that Steve was referring to the thing in
ii_system:[ingres.sig.schemadef] (or $II_SYSTEM/ingres/sig/schemadef). 
It's an ABF application written in 4GL and ESQL/C.  I have attached an
extract from the read.me file.

I put together a COM file (shell script) that does much the same thing
(the altertable part) using "copydb", but it still has the problem that
it doesn't preserve all those other back-end objects that get defined on
a table: views, procedures, and rules.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

================================================================================
Schemadef is an ABF application that assists with creation, copy, alter,
rename, and load of database tables.

The source files, help files, and copyapp output are all provided in
this  directory.  You can build your own executable image using the
steps outlined below.  (Building schemadef requires ABF and ESQL/C, and
a C compiler, of course).

The "schemadef" application currently consists of five frames and four
ESQL/C procedures.  

Menu options are as follows:

Create(1)	Allows you to create a table or view.

Drop(2)		Allows you to drop a table or view.  Provides a confirmation 
		message to prevent dropping the wrong object.

Alter(3)	Allows for changing column names, datatypes, lengths, and null 
		and default options on tables, whether or not they contain 
		data.  If the table contains data, makes all appropriate 
		datatype conversions.  Does not preserve permits, grants, 
		views, storage structures, or indexes.  Does not apply to views.

Copy(4)		Prompts for a new name and makes a copy of an existing table.  
		If a view is selected for copying, the copy is still a new 
		table.  Does not copy permits, grants, views, storage 
		structure, or indexes.

ReName(5)	Prompts for a new name which replaces the old table name.  
		Does not preserve permits, grants, views, storage structure, 
		or indexes.  Does not apply to views.

Load(6)		Facilitates loading a table from a flat file.

Exit(PF4)	Returns user to the operating system level.

Help(PF2)	Invokes this help file.
Newsgroups: comp.databases.ingres
From: kerry@Ingres.COM (Kerry Kurasaki)
Subject: Re: FAQ
Reply-To: kerry@Ingres.COM (Kerry Kurasaki)
Organization: Ask Computer Systems Inc., Ingres Division, Alameda CA 94501
Date: 14 Aug 92 18:55:34 GMT

In article <1992Aug14.022915.24137@csc.canberra.edu.au> mmr@asgserver.canberra.edu.au (Micheal Rainford-Watson) writes:
>>
>>Please excuse my ignorance........but what is FAQ ?
>>
>
>That's Ok Carole, because this is a frequently asked question.
>

Just to update the readers, I'm still working on the FAQ, but since it
is the first one, it's not yet in shape to post.  It will be a few more
weeks so please be patient.  (I've gotten busy recently).

Kerry
From: hbowser@dvorak.amd.com (Horace Bowser)
Newsgroups: comp.databases.ingres
Subject: Re: Ingres on Solaris 2.0
Date: 14 Aug 92 21:24:05 GMT
Organization: Advanced Micro Devices, Inc.; Austin, Texas

Hey:

Does anyone know if Ingres 6.4 runs/will run under Solaris 2.0?
If not, any idea if/when it will?

Horace
horace.bowser@txqis.amd.com
Newsgroups: comp.databases.ingres
From: sergio@Ingres.COM (Sergio L Aponte)
Subject: Just a test
Organization: Ingres, an ASK Company, Alameda CA 94501
X-Newsreader: Tin 1.1 PL5
Distribution: ba
Date: 12 Aug 92 23:49:53 GMT

	Pardon the test

--
===============================================================   _|||_
Sergio L. Aponte, SMTS @ Ingres, an ASK Company                   <*,*>
Internet : sergio@coqui.ingres.com                                [`-']  Keko
UUCP     : {sun,mtxinu,pyramid,pacbell}!ingres!coqui!sergio       _"_"_  Jones
From: salnick@dejavu.spk.wa.us (There is no substitute for displacement...)
Newsgroups: comp.databases.ingres
Subject: Re: Ownership problem. ABF application
Date: 21 Aug 92 03:49:42 GMT
Organization: Not Organized

In article <1992Aug20.161118.18014@emr1.emr.ca> kasvand@ccrs.emr.ca (Tony Kasvand) writes:
>
>  I have an image (and running executable) application which won't let other 
>users execute and use this program. They keep receiving this error at the
>beginning:  
>
>xlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
>xx SELECT on table special_users_table: no GRANT or GRANT compatible permit   x
>xx exists. (E_US0DAE)                                                         x
>xx (Tue Aug 18 09:02:21 1992)                                                 x
>xx                                                        [PRESS RETURN]      x
>xmqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj

You are on the right track.  Tables owned by the DBA of the database can 
be made available to the world for select, update, insert, delete... but 
are not created that way by default (dba memory fail safe).

On the other hand, ONLY the owner of the tables (the dba) can grant 
permits on them...  putting the grant statement into a piece of 
application code will have the following two problems:

   1.  if it is not the dba who is running the code, it will fail
   2.  permits only need to be granted once per universe lifetime...
       granting at each execution of the application code is a waste
       of time.
       
As the dba, go into ISQL and enter

create permit select on <table name> to all;

and hit do.

bob
       
--
RW Salnick, Spokane,WA  |  Home: salnick@dejavu.spk.wa.us
  Amiga 1000, WB 1.3    |  Work: mfgsys!salnick@kaiser.spk.wa.us
      WA9BVE            |
Newsgroups: comp.databases.ingres
From: jaa@world.std.com (Jerry A Abramson)
Newsgroups: comp.databases.ingres
Subject: Ingres 6.2 Versus Ingres 6.4 on SCO Unix 3.2.2 Versus SCO Unix 3.2.4
Keywords: 6.4 SCO ODT Open Desktop
Date: 14 Aug 92 18:03:55 GMT
Article-I.D.: world.BszJIK.Fyq
Organization: The World Public Access UNIX, Brookline, MA

We are currently evaluating moving from Ingres 6.2 (part of the Open Dekstop
1.0 and 1.1 product) to Ingres 6.4.  There are numerous new features, bug fixes
in 6.4 that we would like to take advantage of.  Before we begin the 
transition, has any one out there already made the transition, and if so, any
input would be greatly appreciated.

Additionally, we currently have some 6.2 Ingres code, I would assume we need
to recompile with 6.4 libraries.  If anyone has experienced any problems with
the new libraries or the 6.4 ESQLC preprocessor, input would be greatly 
appreciated.

One last question, does Ingres support database rules (more than table-only
integrity rules).  We are trying to embed the database rules within the db,
not within the applications only.


							Thankyou,


							Jerry Abramson
							Voicetek Corp.
							jaa@world.std.com
							1 (508) 250-7957
From: jarnot@kin.lap.upenn.edu (Kevin J. Jarnot)
Newsgroups: comp.databases.ingres
Subject: Case sensitivity in LIKE clause in SELECT statements
Date: 21 Aug 92 20:14:43 GMT
Organization: University of Pennsylvania, Language Analysis Center

Is it possible to turn off case sensitivity in a SELECT statement when
using the LIKE clause?  I want to search for a string using a template,
something like "wom%".  This template should be able to match "Wombat",
"wombat", "WoMbAtS", etc...I currently convert the template to 
"\[Ww\]\[Oo\]\[Mm\]%", but the SELECT statement takes forever...

Any ideas?


Kevin

-- 
Kevin J. Jarnot (jarnot@kin.lap.upenn.edu)      |  
Lead Programmer/Analyst/Keyboardist             | "The monkey-boys are evil -
Univ. of Pennsylvania Language Analysis Center  |  Lord Whorfin is supreme..."
3700 Market St, Suite 202  Phila, PA 19104      | 
Newsgroups: comp.databases.ingres
From: Cristyn Emmett <Cristyn.Emmett@newcastle.ac.uk>
Subject: Deadlocks and slave processes
Organization: National Radio Astronomy Observatory
Date: Mon, 17 Aug 1992 13:53:21 GMT

Thanks to all who responded to my query.
The answers seem to be:
1. Do nothing about slave processes (although increasing the number might help 
   in a general way)
2. Set READLOCK = NOLOCK for each session. I have done this and so far :-)
   I have had no deadlocks. The problem arose because I have heavy
   concurrent use of two or three (btree) tables in an ESQL/C application,
   plus a read-only ABF application. 

It may be the case that the secondary indexes which I've added to speed up 
the ABF's response times may be slowing-down the ESQL/C.
Unfortunately I don't have use of IPM so it's difficult to see exactly
what is going on. Maybe 6.4 will help........

Thanks again,

	Cristy

Also known as:       
cristyn.emmett@newcastle.ac.uk
******************************
Newsgroups: comp.databases.ingres
From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: ESQL Bug
Organization: 
Date: Thu, 20 Aug 1992 21:24:13 GMT

In article <1992Aug19.211421.8332@macc.wisc.edu>,
kmchylla@vms.macc.wisc.edu writes:

> I wrote an embedded SQL program using C that seems to go into an
> infinite loop when I have too many records to process, and
> I can't figure out why.  The program simply sets up a
> cursor to read each record, take a character field (storing
> a gregorian date) and convert it to a date.  It updates another
> field in the same record (a date field), and fetches the next record.
>
> This seems easy enough, but if my table gets too large it seems
> to get "stuck" on a record and processes that same record until I
> stop the process.  The strange thing is, depending on the Ingres
> installation (production vs development) the program will run longer
> before looping.  (The production machine can handle larger tables
> than the development.)
>
> Our current work around for the problem is to chop the table into
> smaller bits.  This seems to work, but I'd love to know what the
> real problem is.  Ingres never gives us an SQL error or VMS
> error message to track so we have decided it is not in our code
> or data.

A guess: maybe your transaction log file is filling up.  A cursor has to
be run inside a transaction, and if you are updating each row after you
read it in, then INGRES has to log your entire table (before image and
after image pages) to the INGRES transaction log file before you commit
the transaction.

This diagnosis is not entirely consistent with your symptoms, however,
because you state that you get no error message from INGRES.  When your
program starts looping, try looking at the end of ii_config:errlog.log
to see if it shows log file full.  Also, use "show process/continuous"
to look at the front end, the dbms server, and the recovery process
(DMFRCPxx or II_RCPxx) to see which one is active.  This may give a clue
to where things are stuck.

Are you doing error checking in your C program?  Are you sure that your
cursor "fetch" loop terminates on any error, or are you just looking for
the "no rows found" condition?  Try adding these lines at the beginning
of your C program:

	exec sql WHENEVER sqlerror call sqlprint ;
	exec sql WHENEVER sqlwarning call sqlprint ;

You could also try running your program after doing this:

	$ define ii_embed_set "printgca"

and looking at the file "iiprtgca.log" that gets created.  It may
provide a clue to where front-end/back-end communication breaks down.

Hope this helps.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.

Newsgroups: comp.databases.ingres
From: drcoles@somnet.sandia.gov (Dwight R Coles)
Subject: Re:  how to add a new location ?
Organization: National Radio Astronomy Observatory
Date: Mon, 17 Aug 1992 21:39:18 GMT

> From: ofer@chen.bgu.ac.il (Ofer Reshef)
> Subject: how to add a new location ?
> Hi All,
>   I've tried to add a new db location using the AccessDb utility.
>   But it didn't add a new location despite all my attempts.
>   How do you add a new location ?
> 
Ofer, you didn't mention if you had set up an "ingres root directory"
on the new location you were defining, so I'll venture a guess that
you haven't.  From the INGRES account, create a directory "ingres"
where the new location will be rooted.  This must be in place, correctly
owned and permissioned, before you try to define a location in 
ACCESSDB.

Otherwise, tell us more... alternate locations definitely DO work!

Dwight Coles   drcoles@sandia.gov