[PHP-DB] OCIPLogon

[Sapporo]

Hi,

I'm trying to avoid excessive database connections within my app.

From the docs, I figured that the following code would insert 2 rows of 
data into table Dummy, since the connection would be reused and the 
transactions aren't isolated by using OCINLogon() (error handling left 
out for readability):

$conn = OCIPLogon(scott, tiger, testdb);
$statement = OCIparse($conn, INSERT INTO Dummy VALUES ('A'));
OCIexecute($statement, OCI_DEFAULT);
$conn = OCIPLogon(scott, tiger, testdb);
$statement = OCIparse($conn, INSERT INTO Dummy VALUES ('B'));
OCIexecute($statement, OCI_DEFAULT);
OCICommit($conn);

But it turns out that only the second row gets written to the database. 
So, what difference would it make to use OCINLogon()? Is this the 
expected behaviour?

How does everyone else handle this? Is it possible to store database 
connections in a session?

TIA,
-sapporo.
BTW, I'm using PHP 4.3 on Linux talking to Oracle9i.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Problems with OCIFetchStatement()'s $skip and $return?

[Sapporo]

Hi,

I'm using OCIFetchStatement()'s undocumented parameter $return 
(http://de.php.net/manual/de/function.ocifetchstatement.php) to limit 
the number of rows fetched from a result set. Unfortunately, I 
sometimes get far less rows than expected.

Before I further investigate the issue, can anyone confirm problems 
with using OCIFetchStatement()'s $skip and $return parameters?

TIA,
-sapporo.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] [plain SQL Problem] Group By

[Sapporo]

Hi,

sorry if this is off topic. I hope you don't mind a plain SQL question 
here.

I know I can do what I want using subqueries (I'm on Oracle9i here), 
but I would really like to avoid them for performance reasons. Here's 
my example data:

  DESC dept
 NameType
 -
 DEPT_ID  NUMBER(10)
 DEPT_NAME  VARCHAR2(20)
  SELECT * FROM dept;
 DEPT_ID DEPT_NAME
 --- 
   1 sales
   2 marketing
  DESC emp
 Name Type
 --
 EMP_ID NUMBER(10)
 EMP_DEPT_ID NUMBER(10)
 EMP_AGE NUMBER(2)
 EMP_NAME VARCHAR2(20)
  SELECT * FROM emp;
EMP_ID EMP_DEPT_IDEMP_AGE EMP_NAME
 - --- -- 
 1   1 32john
 2   1 19   mike
 3   1 40  alex
 4   2 42 norman
 5   2 33 jason
 6   2 20bert
And here's the result I'd like to produce:

  ???
 DEPT_NAME YOUNGEST_AGE YOUNGEST_NAME OLDEST_AGE OLDEST_NAME
 -  - -- ---
 marketing   20  bert 42  norman
 sales   19  mike 40alex
Here's what I have so far:

  SELECT D.dept_name, MIN(E1.emp_age) AS YOUNGEST_AGE, 
MAX(E2.emp_age) AS OLDEST_AGE
   FROM dept D, emp E1, emp E2
   WHERE E1.emp_dept_id = D.dept_id AND E2.emp_dept_id = D.dept_id
   AND E1.emp_id  E2.emp_id
   GROUP BY D.dept_name

 DEPT_NAMEYOUNGEST_AGE   OLDEST_AGE
   --
 marketing  2042
 sales  19
40
What is missing ist the name of the younges and oldest employee, since 
I can't use aggregate functions to get at them.

Any help would be greatly appreciated!

Thanks,
-sapporo.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php