Jump to content


Photo
- - - - -

Fields included in ProIV SELECT statement


3 replies to this topic

#1 John Robson

John Robson

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 14 January 2003 - 03:57 AM

For some reason I was under the impression that ProIV will only SELECT from an ORACLE the fields that it uses in the function, does anyone know if this is this correct?

I have ran a ProIV trace for a function and it seems that ProIV is SELECTing all the fields from a paticular filedef.
This is causing us a problem because there are more than 255 fields and we are then getting a BUS error.

Has anyone encountered this before? (and found a solution).

Cheers

John

#2 Albert Ho

Albert Ho

    Member

  • Members
  • PipPip
  • 19 posts
  • Gender:Male

Posted 14 January 2003 - 10:36 AM

Yeah PROIV does that, not only to ORACLE but also other databases like DB2 (DB2 driver is available only upto ver. 5.0 anyways). We also did a trace b4. What you can try is to issuing a static SQL statement like the following

SQL
SELECT FIELD1, FIELD2.... FIELDN FROM TABLENAME
ENDSQL

to substitute the following

SQL
SELECT FROM TABLENAME
ENDSQL

or SEL-ONLY(), SEL-PARTIAL(), SEL-RANGE()

it should work :)

#3 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 14 January 2003 - 11:55 AM



John,

I believe ProIV V3 may have done what you suggest. In V3 the Oracle driver was implemented using 'embedded SQL' (Pro*C).

I think this behaviour may have stopped in V4.0 when the driver was rewritten using the Oracle Call Interface (OCI).

I suspect it was not a good 'optimisation' for tables with a 'moderate' number of columns as it probably gave rise to too many different SQL statements (thus defeating some important optimisations within Oracle).

Would it be practical for you to set up some suitable views containing subsets of the columns from your many-column table?

HTH, R.
Nothing's as simple as you think

#4 John Robson

John Robson

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 14 January 2003 - 09:53 PM

After a little more investigaion and a bit more reading, I've found out ProIV only SELECTs fields used in the function for the primary file, for all secondary files all fields are selected.

Looks like we'll have to split the filedefs.

Thanks for your help.

Cheers

John



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users