berlin@bu-cs.BU.EDU (David K. Fickes) (05/18/88)
Just a quick question for you Ingres hackers (RTI that is..) I'm attempting to port a VERY old dataset that has no delimiters on the fields... Most of the 50,000 or so records are in good order now and I'd like to run a few replace statements as follows: replace p(pages=int1(right(squeeze(p.note6),2))) where p.note6="PP ?" Unfortunately.. a handful of these records can't convert to integers either because the last two characters aren't numbers or the field is longer than it should be.. ect.. (we took the usual cases when breaking the file into fields and didn't include a few exceptions..) I'd love for the program to do as many as possible and then give me a count of all the records that cannot be converted.. Is this possible? For this database, I'll be doing quite a few of thes conversions and it seems to come up periodically with the type of work I do... thanks, david ps: has anyone looked into Metamorph software by EPI in Ohio? -- ============================================================================== David K. Fickes Center for Einstein Studies/Einstein Papers Project UUCP: ...harvard!bu-it!berlin Boston University OTHERWISE: berlin@bu-it.bu.edu 745 Commonwealth Avenue PHONE: (617) 353-9249 (617) 277-9741 Boston, MA 02215
cpf@wdl1.UUCP (Carl P. Furner) (05/19/88)
>/ wdl1:comp.databases / berlin@bu-cs.BU.EDU (David K. Fickes) / 4:21 pm May 17, 1988 / >Just a quick question for you Ingres hackers (RTI that is..) >============================================================================== >David K. Fickes Center for Einstein Studies/Einstein Papers Project >UUCP: ...harvard!bu-it!berlin Boston University >OTHERWISE: berlin@bu-it.bu.edu 745 Commonwealth Avenue >PHONE: (617) 353-9249 (617) 277-9741 Boston, MA 02215 > >---------- You may try to use and EQUELC program or some other preprocessed language to access the flat file. Stuff the converted values or convert them yourself into a variable and then put them into a database. If you have any questions feel free to call. ============================================================================== Carl P. Furner FORD AEROSPACE PHONE: (415) 852-6586 Ask for Robin, Dave or Carl. Ford Aerospace Corp. Western Development Laboratories cpf@ford-wdl1.arpa ...{sgi,sun,ucbvax}!wdl1!cpf All opinions stated here are my own, not my employer's. -----
irddba@wdl1.UUCP (Sandy Darby) (05/20/88)
</ wdl1:comp.databases / berlin@bu-cs.BU.EDU (David K. Fickes) / 4:21 pm May 17, 1988 /
<Just a quick question for you Ingres hackers (RTI that is..)
Dave this is the query we discussed that converts the 3,4 position in
string to integer if the characters are in the range [0-9].
retrieve (table1 = int2(left(shift(table.fld1,-2),1) +
left(shift(table.fld1,-3),1)))
where left(shift(table.fld1,-2),1) = "[0-9]" and
left(shift(table.fld1,-3),1) = "[0-9]"
IF you want to find the ones that are not there then:
where left(shift(table.fld1,-2),1) != "[0-9]" and
left(shift(table.fld1,-3),1) != "[0-9]"
Good-Luck,
Carl P. Furner
Ford Aerospace Corp.
Western Development Laboratories
cpf@ford-wdl1.arpa
...{sgi,sun,ucbvax}!wdl1!cpf
All opinions stated here are my own, not my employer's.progsoft@altger.UUCP (progsoft) (05/28/88)
In article <22714@bu-cs.BU.EDU> berlin@buita.bu.edu (David Fickes Einstein Project) writes: >Just a quick question for you Ingres hackers (RTI that is..) > >I'm attempting to port a VERY old dataset that has no delimiters on >the fields... Most of the 50,000 or so records are in good order now >and I'd like to run a few replace statements as follows: > >replace p(pages=int1(right(squeeze(p.note6),2))) where p.note6="PP ?" > >Unfortunately.. a handful of these records can't convert to integers >either because the last two characters aren't numbers or the field is >longer than it should be.. ect.. (we took the usual cases >when breaking the file into fields and didn't include a few >exceptions..) > >I'd love for the program to do as many as possible and then give >me a count of all the records that cannot be converted.. Is this >possible? For this database, I'll be doing quite a few of thes >conversions and it seems to come up periodically with the type of work >I do... > No, in the current Ingres version Ver 5.0 it's not possible to do a conversion on a whole table if some rows fail. In this case Ingres will abort with an error and back out the whole transaction. But your problem will be solved by RTI with the next Ingres Version (6.0) , than there will be a possibilty to say "convert all rows that match and report the one's that doesn't " But u can write a program in esqlc or osl that makes the conversion in the way u want it , but it will have a real poor performance, because u must then select all rows that u want to convert , (in a table field or into a cursor) , and explicit convert them this requires in osl another select (pseudo-select) and than if the conversion not failed do the update on the effected row. I know that isn't nice , but in my opinion the only way to do such a conversion. Good luck, Uli >-- Uli Zug Bang : ..!unido!altger!althh!tourist A+S Computerhaus GmbH D-4600 Dortmund 1 Voice: + 231/551409 ----------------------------------------------------------------------------
jkrueger@daitc.ARPA (Jonathan Krueger) (05/30/88)
>>I'd like to run a few replace statements as follows: >>replace p(pages=int1(right(squeeze(p.note6),2))) where p.note6="PP ?" >>Unfortunately.. a handful of these records can't convert to integers See the INGRES DBA Guide. The support for the operation you want to do is indeed poor in 5.0; you can however find problem rows with queries like: retrieve (p.all) where p.note6="PP ?*[A-Z]*" Note that this only finds uppercase alphas; to rule out other nondigits requires more tests. If INGRES supported regular expressions as well as pattern matching we could write this as one test: ... where p.note6 = "PP ..*[^0-9].*" But what we really want is the support Uli says 6.0 provides, allow a bulk load to survive type conversion failures and flag problem rows. -- Jon
JKMJJ@CUNYVM.CUNY.EDU (08/02/89)
I am conceptually evaluating a proposal that mentions the Ingres data base and I have some questions about Ingres (if I may) Can the Ingress database be accessed from something other than 4GL? Like Progress? Or Oracle? Or C? Or Basic? Or Cobol? Is 4GL compilable? What is the status of the Remote Data Access standard? What is the status of RTI's Global Communications Architecture? ------- Jack Meth John Jay College of Criminal Justice New York, NY 10019 BITNET JKMJJ@CUNYVM
JKMJJ@CUNYVM.CUNY.EDU (08/02/89)
Can relations (in Ingres) be defined on the fly? ------- Jack Meth John Jay College of Criminal Justice New York, NY 10019 BITNET JKMJJ@CUNYVM -------
robf@squid.rtech.com (Robert Fair) (08/02/89)
>JKMJJ@CUNYVM.CUNY.EDU writes: >I am conceptually evaluating a proposal that mentions the Ingres >data base and I have some questions about Ingres (if I may) >Can the Ingress database be accessed from something other than >4GL? >Oracle? >Or C? >Or Basic? >Or Cobol? INGRES has ESQL and EQUEL interfaces for: - C - Fortran - ADA - COBOL - Pascal - BASIC - PL/1 >Is 4GL compilable? In INGRES 5.0 4GL is fully compiled [4GL->C->executable] In INGRES 6.2 upwards 4GL can be compiled or interpreted, depending on the user's preference. > >What is the status of RTI's Global Communications Architecture? The GCA actually stands for General Communications Architecture and is in full use in INGRES release 6, including both the GCN (Name service facility) and GCC (Communications service facility) as well as the documented application level and other protocols. [For people who have wondered, this is what the IIGCN and IIGCC processes are] >------- >Jack Meth >John Jay College of Criminal Justice >New York, NY 10019 Robert Fair Technical Support Relation Technology, Inc Disclaimer: availability of products in different environments may vary.
robf@squid.rtech.com (Robert Fair) (08/02/89)
Jack Meth writes: >Can relations (in Ingres) be defined on the fly? Yes. CREATE [TABLE|VIEW|INDEX] can be used at any time, including: - Interactively in the terminal monitor. - In embedded SQL routines. - In 4GL frames and procedures. There is also an interactive fill-in-the-blanks utility for creating tables, designed for people who don't know SQL/QUEL too well. Relations can also be created from command scripts. INGRES remains up and fully available when a new relation is being created - CREATE can be run just like SELECT or INSERT. > >------- >Jack Meth >John Jay College of Criminal Justice >New York, NY 10019 >BITNET JKMJJ@CUNYVM Robert Fair Technical Support Relational Technology, Inc
bg0l+@andrew.cmu.edu (Bruce E. Golightly) (08/02/89)
Depends on what you mean by access. Ingres instructions from wither the QUEL or SQL set can be embedded in a large number of 3GLs, including C. BASIC and COBOL, which you named. I would not recommend bypassing the DBMS to operate directly on the Ingres data files, if that's what you have in mind. That defeats the purpose of using a relational DBMS. I don't know if any other venders provide tools to access Ingres data. RTI has been working on a set of "gateways" to provide access to other data storage facilities from Ingres. I'm not sure what the currently available set of those tools is. Bruce
dennism@menace.rtech.COM (Dennis Moore (x2435, 1080-276) INGRES/teamwork) (08/03/89)
Jack -- INGRES is a wonderful product. You may think, "of course he says so, he works there." My response is "that's why I *CAME* here." INGRES can be accessed from more languages than any other database I know, including C, COBOL, Fortran, Pascal, BASIC, ADA, PL/1, and our 4GL (OSL). By access, I mean embedded SQL or QUEL statements in the host language. INGRES can also run straight SQL scripts. In addition, several other companies provide tools to access INGRES data using other 4GLs, and I believe Progress is one of them, although you'll never go back to another 4GL after OSL. As far as RDA goes, I don't think that this standard has been approved at any meaningful implementation level yet. RTI is actively, with the support of many vendors, pursuing many standardization efforts, including RDA and GCA, IRDS (Information Resource Dictionary System -- data dictionary standard), OPEN SQL, OSF, and others. GCA is an RDA implementation, and GCA exists now. As part of the Open Desktop product announcement (ODT is a product available from SCO integrating Motif, networking, UNIX/386, and an embedded database engine -- INGRES), several database vendors announced that they would support ODT, which means that they will communicate with the DBMS (INGRES) using GCA. I can get more detailed information if you like. This is all from memory, and is not my specialty. -- Dennis Moore, my own opinions etc.
dennism@menace.rtech.COM (Dennis Moore (x2435, 1080-276) INGRES/teamwork) (08/03/89)
Jack -- Relations can be defined on the fly, if I understand your question correctly. If you mean "can I create tables or views in a program and drop them when I am done," or even more succinctly, "can I use SQL or QUEL DDL in an OSL or embedded host language program," the answer is yes. You can create tables, views, indices, permissions, etc. in programs or directly in SQL in our SQL terminal monitor or Interactive SQL utilities, or using a special TABLES utility. Jack, if you like, you could e-mail me directly. I am curious -- can you e-mail me the name of your sales rep? -- Dennis Moore, my own opinions etc.
tsao@helios.tcad.ee.ufl.edu (Tsao) (01/29/91)
To those who have reponded my previous posting: thank you all very much. The problem is that I did not exec COMMIT after SQL insert. I don't have a user's guide and reference manual for embedded SQL/C ( just ordered one ), and I misinterpreted the example in the Companion guide for C.