subject:"Using Oracle"

Re: Using Oracle

2012-05-12 Thread rihad

Most tables in our Oracle database are huge with tens of columns, have
no single-column primary keys, or have multi-column keys. Can CakePHP
2.1 make sense of it all?

-- 
Our newest site for the community: CakePHP Video Tutorials 
http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others 
with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php

Re: Using Oracle

2012-05-12 Thread John Hardy

IMO with a cluttered schema i would rely on stored procedures for the
abstraction and keep the code complexity at a minimum
On May 11, 2012 11:54 PM, rihad ri...@mail.ru wrote:

 Most tables in our Oracle database are huge with tens of columns, have
 no single-column primary keys, or have multi-column keys. Can CakePHP
 2.1 make sense of it all?

 --
 Our newest site for the community: CakePHP Video Tutorials
 http://tv.cakephp.org
 Check out the new CakePHP Questions site http://ask.cakephp.org and help
 others with their CakePHP related questions.


 To unsubscribe from this group, send email to
 cake-php+unsubscr...@googlegroups.com For more options, visit this group
 at http://groups.google.com/group/cake-php


-- 
Our newest site for the community: CakePHP Video Tutorials 
http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others 
with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php

Re: Using Oracle

2012-05-11 Thread stork

Watch, comment, and/or help with this ticket:
http://cakephp.lighthouseapp.com/projects/42648/tickets/2232-oracle-11g-datasource-for-cakephp-20

-- 
Our newest site for the community: CakePHP Video Tutorials 
http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others 
with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php

Re: Using Oracle

2012-05-11 Thread rihad

Thanks, stork. I've followed this little guide:
http://www.hassanbakar.com/2012/01/09/using-oracle-in-cakephp-2-0/comment-page-1/#comment-38958
and could successfully migrate dbo_oracle.php from 1.3.15 to 2.1,
allowing Cake's index.php to pass all its preliminary tests. Is it a
bit early to be singing my praises? I'll be using nothing fancy, plain
multi-table selects, simple updates, no deletes or inserts.

-- 
Our newest site for the community: CakePHP Video Tutorials 
http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others 
with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php

Using Oracle

2012-05-10 Thread rihad

Hi, folks. Can I use CakePHP 2.1.2 with Oracle database? I looked in
Model/Datasource/Database/ there only seems to be support for Mysql,
Postgres, Sqlite, Sqlserver. But working with an existing Oracle
database is my job's requirement. I would be thankful for any tips.
Thank you.

-- 
Our newest site for the community: CakePHP Video Tutorials 
http://tv.cakephp.org 
Check out the new CakePHP Questions site http://ask.cakephp.org and help others 
with their CakePHP related questions.


To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at 
http://groups.google.com/group/cake-php

Complex find() using Oracle

2010-02-18 Thread TonyFugere

I've search this group and Google for a solution without any luck.

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:

$this-find('all',
  array(
'fields' = array(
  'ServiceData.stat_date',
  'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
  'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
),
'conditions' = array(
  'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
  'SUBSTR(ServiceData.stat_parameter, 1, 4)' = $active_schema,
  'ServiceData.stat_server LIKE' = 'PRODUCTION%',
  'ServiceData.stat#' = 999,
  'ServiceData.stat_date' = $load_date,
),
'group' = array(
  'ServiceData.stat_date',
  'SUBSTR(ServiceData.stat_parameter,1,4)',
  'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' = array('SUBSTR(ServiceData.stat_parameter,19,2)')
  )
);

This is the resulting query:

SELECT
  ServiceData.stat_date
  , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
  , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
  SERVICE_DATA ServiceData
WHERE
  SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
  AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
  AND ServiceData.stat_server LIKE 'PRODUCTION%'
  AND ServiceData.stat_number = 999
  AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
  ServiceData.stat_date
  , SUBSTR(ServiceData.stat_parameter,1,4)
  , SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
  SUBSTR(ServiceData.stat_parameter,19,2) ASC

This results in data returning in this form:

array(43) {
  [0]=
  array(3) {
[ServiceData]=
array(4) {
  [stat_date]=
  string(19) 2010-02-17 09:00:02
  [schema]=
  string(4) SCHEMA_A
  [state]=
  string(2) ()
  [statvalue]=
  string(3) 116
}
[SUM(DECODE(SUBSTR(ServiceData]=
array(1) {
  [stat_parameter,22)]=
  string(3) 107
}
[0]=
array(3) {
  ['service1']=
  string(2) 40
  ['service2']=
  string(3) 116
  ['service3']=
  string(2) 17
}
  }
}

Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
'service1' (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???

If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:

array(43) {
  [0]=
  array(1) {
[ServiceData]=
array(3) {
  [stat_date]=
  string(19) 2010-02-17 09:00:02
  [schema]=
  string(4) SCHEMA_A
  [state]=
  string(2) ()
}
  }
}

I have tried the afterFind solution posted by TekNoid, but it did not
work.
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/

Any hints or ideas on how I can overcome the issues of having such a
complex SUM DECODE of a SUBSTR?

Check out the new CakePHP Questions site http://cakeqs.org and help

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta

CakePhp is not correctly parsing the alias for the decode fields, thats 
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data 
missmatch.


Regards

MARTIN



On 02/18/2010 02:38 PM, TonyFugere wrote:

I've search this group and Google for a solution without any luck.

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:

$this-find('all',
   array(
 'fields' =  array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
   'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
 ),
 'conditions' =  array(
   'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
   'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  $active_schema,
   'ServiceData.stat_server LIKE' =  'PRODUCTION%',
   'ServiceData.stat#' =  999,
   'ServiceData.stat_date' =  $load_date,
 ),
 'group' =  array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4)',
   'SUBSTR(ServiceData.stat_parameter,19,2)'
 ),
 'order' =  array('SUBSTR(ServiceData.stat_parameter,19,2)')
   )
);

This is the resulting query:

SELECT
   ServiceData.stat_date
   , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
   , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
   SERVICE_DATA ServiceData
WHERE
   SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
   AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
   AND ServiceData.stat_server LIKE 'PRODUCTION%'
   AND ServiceData.stat_number = 999
   AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
   ServiceData.stat_date
   , SUBSTR(ServiceData.stat_parameter,1,4)
   , SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
   SUBSTR(ServiceData.stat_parameter,19,2) ASC

This results in data returning in this form:

array(43) {
   [0]=
   array(3) {
 [ServiceData]=
 array(4) {
   [stat_date]=
   string(19) 2010-02-17 09:00:02
   [schema]=
   string(4) SCHEMA_A
   [state]=
   string(2) ()
   [statvalue]=
   string(3) 116
 }
 [SUM(DECODE(SUBSTR(ServiceData]=
 array(1) {
   [stat_parameter,22)]=
   string(3) 107
 }
 [0]=
 array(3) {
   ['service1']=
   string(2) 40
   ['service2']=
   string(3) 116
   ['service3']=
   string(2) 17
 }
   }
}

Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
'service1' (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???

If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:

array(43) {
   [0]=
   array(1) {
 [ServiceData]=
 array(3) {
   [stat_date]=
   string(19) 2010-02-17 09:00:02
   [schema]=
   string(4) SCHEMA_A
   [state]=
   string(2)

Re: Complex find() using Oracle

2010-02-18 Thread TonyFugere

I don't follow what you mean by Try using expression for fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radosta martinrado...@gmail.com wrote:
 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:

  I've search this group and Google for a solution without any luck.

  I can read (SELECT) data, but cannot change this database at all. To
  get the data I run the following find:

  $this-find('all',
     array(
       'fields' =  array(
         'ServiceData.stat_date',
         'SUBSTR(ServiceData.stat_parameter,1,4) AS
  ServiceData.schema',
         'SUBSTR(ServiceData.stat_parameter,19,2) AS
  ServiceData.state',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
  ServiceData.stat_value,0)) AS ServiceData.service1',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
  ServiceData.stat_value,0)) AS ServiceData.service2',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
  ServiceData.stat_value,0)) AS ServiceData.service3',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
  ServiceData.stat_value,0)) AS ServiceData.service4',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
  ServiceData.stat_value,0)) AS ServiceData.service5',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
  ServiceData.stat_value,0)) AS ServiceData.service6',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
  ServiceData.stat_value,0)) AS ServiceData.service7',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
  ServiceData.stat_value,0)) AS ServiceData.service8'
       ),
       'conditions' =  array(
         'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
  'customer_lookup',
         'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  $active_schema,
         'ServiceData.stat_server LIKE' =  'PRODUCTION%',
         'ServiceData.stat#' =  999,
         'ServiceData.stat_date' =  $load_date,
       ),
       'group' =  array(
         'ServiceData.stat_date',
         'SUBSTR(ServiceData.stat_parameter,1,4)',
         'SUBSTR(ServiceData.stat_parameter,19,2)'
       ),
       'order' =  array('SUBSTR(ServiceData.stat_parameter,19,2)')
     )
  );

  This is the resulting query:

  SELECT
     ServiceData.stat_date
     , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
     , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
  ServiceData.stat_value,0)) AS ServiceData.service1
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
  ServiceData.stat_value,0)) AS ServiceData.service2
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
  ServiceData.stat_value,0)) AS ServiceData.service3
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
  ServiceData.stat_value,0)) AS ServiceData.service4
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
  ServiceData.stat_value,0)) AS ServiceData.service5
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
  ServiceData.stat_value,0)) AS ServiceData.service6
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
  ServiceData.stat_value,0)) AS ServiceData.service7
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
  ServiceData.stat_value,0)) AS ServiceData.service8
  FROM
     SERVICE_DATA ServiceData
  WHERE
     SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
     AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
     AND ServiceData.stat_server LIKE 'PRODUCTION%'
     AND ServiceData.stat_number = 999
     AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
  DD HH24:MI:SS')
  GROUP BY
     ServiceData.stat_date
     , SUBSTR(ServiceData.stat_parameter,1,4)
     , SUBSTR(ServiceData.stat_parameter,19,2)
  ORDER BY
     SUBSTR(ServiceData.stat_parameter,19,2) ASC

  This results in data returning in this form:

  array(43) {
     [0]=
     array(3) {
       [ServiceData]=
       array(4) {
         [stat_date]=
         string(19) 2010-02-17 09:00:02
         [schema]=
         string(4) SCHEMA_A
         [state]=
         string(2) ()
         [statvalue]=
         string(3) 116
       }
       [SUM(DECODE(SUBSTR(ServiceData]=
       array(1) {
         [stat_parameter,22)]=
         string(3) 107
       }
       [0]=
       array(3) {
         ['service1']=
         string(2) 40
         ['service2']=
         string(3) 116
         ['service3']=
         string(2) 17
       }
     }
  }

  Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
  'service1' (this is the decode value in single ticks). Also the
  value to service1

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta


Check the text case in this commit for an example:

http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

let me know if that fix the problem, if not, you can prepare a test case 
and report the issue.




On 02/18/2010 03:21 PM, TonyFugere wrote:

I don't follow what you mean by Try using expression for fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:
   

CakePhp is not correctly parsing the alias for the decode fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data
missmatch.

Regards

MARTIN

On 02/18/2010 02:38 PM, TonyFugere wrote:

 

I've search this group and Google for a solution without any luck.
   
 

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
   
 

$this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
   
 

This is the resulting query:
   
 

SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
SERVICE_DATA ServiceData
WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4)
, SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
SUBSTR(ServiceData.stat_parameter,19,2) ASC
   
 

This results in data returning in this form:
   
 

array(43) {
[0]=
array(3) {
  [ServiceData]=
  array(4) {
[stat_date]=
string(19) 2010-02-17 09:00:02
[schema]=
string(4) SCHEMA_A
[state]=
string(2) ()
[statvalue]=
string(3) 116
  }
  [SUM(DECODE(SUBSTR(ServiceData]=
  array(1) {
[stat_parameter,22)]=
string(3) 107
  }
  [0]=
  array(3) {
['service1']=
string(2) 40

Re: Complex find() using Oracle

2010-02-18 Thread Tony Fugere

It does not fix the problem. Am I doing it correctly?

$dbo = $this-getDataSource();
$expression =
$dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1');
$fields = array('ServiceData.stat_date',
 $expression);
$this-find('all', array($fields,...etc, etc.)

Still results in the ugly data.

HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
test case and report the issue of DECODE failing to function properly.

Thanks for your help Martin!

-Tony

On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta martinrado...@gmail.comwrote:

 Check the text case in this commit for an example:


 http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

 let me know if that fix the problem, if not, you can prepare a test case
 and report the issue.




 On 02/18/2010 03:21 PM, TonyFugere wrote:

 I don't follow what you mean by Try using expression for fields. Can
 you clarify or give me an example?

 On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:


 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:



 I've search this group and Google for a solution without any luck.




 I can read (SELECT) data, but cannot change this database at all. To
 get the data I run the following find:




 $this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
 ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
 ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
 ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
 ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
 ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
 ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
 ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
 ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
 ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
 'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
 );




 This is the resulting query:




 SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
 ServiceData.stat_value,0)) AS ServiceData.service1
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
 ServiceData.stat_value,0)) AS ServiceData.service2
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
 ServiceData.stat_value,0)) AS ServiceData.service3
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
 ServiceData.stat_value,0)) AS ServiceData.service4
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
 ServiceData.stat_value,0)) AS ServiceData.service5
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
 ServiceData.stat_value,0)) AS ServiceData.service6
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
 ServiceData.stat_value,0)) AS ServiceData.service7
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
 ServiceData.stat_value,0)) AS ServiceData.service8
 FROM
SERVICE_DATA ServiceData
 WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
 DD HH24:MI:SS')
 GROUP

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta


I would only change:

$this-find('all', array('fields' = $fields,...etc, etc.)

Would be great if you post the test case (or even better, a patch) to 
avoid other people waste time with the same issue.


Bye

MARTIN



On 02/18/2010 05:39 PM, Tony Fugere wrote:

It does not fix the problem. Am I doing it correctly?

$dbo = $this-getDataSource();
$expression = 
$dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 
\'service1\',

ServiceData.stat_value,0)) AS ServiceData.service1');
$fields = array('ServiceData.stat_date',
 $expression);
$this-find('all', array($fields,...etc, etc.)

Still results in the ugly data.

HOWEVER, I did notice the use of CASE WHEN statements in the test 
case. So, I switched my DECODE to CASE WHEN and it works as expected. 
I will draw up a test case and report the issue of DECODE failing to 
function properly.


Thanks for your help Martin!

-Tony

On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta 
martinrado...@gmail.com mailto:martinrado...@gmail.com wrote:


Check the text case in this commit for an example:


http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

let me know if that fix the problem, if not, you can prepare a
test case and report the issue.




On 02/18/2010 03:21 PM, TonyFugere wrote:

I don't follow what you mean by Try using expression for
fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com
mailto:martinrado...@gmail.com  wrote:

CakePhp is not correctly parsing the alias for the decode
fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should
also fix the data
missmatch.

Regards

MARTIN

On 02/18/2010 02:38 PM, TonyFugere wrote:


I've search this group and Google for a solution
without any luck.


I can read (SELECT) data, but cannot change this
database at all. To
get the data I run the following find:


$this-find('all',
   array(
 'fields' =array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
   'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
 ),
 'conditions' =array(
   'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
   'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  
 $active_schema,

   'ServiceData.stat_server LIKE' ='PRODUCTION%',
   'ServiceData.stat#' =999,
   'ServiceData.stat_date' =$load_date,
 ),
 'group' =array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4)',
   'SUBSTR(ServiceData.stat_parameter,19,2)'
 ),
 'order' =  
 array('SUBSTR(ServiceData.stat_parameter,19,2)')

   )
);


This is the resulting query:

Re: Complex find() using Oracle

2010-02-18 Thread Tony Fugere

Do I post those here? If so, here goes... I only have time for a quick test
case

/**
 * test that fields() will accept objects made from DboSource::expression
 *
 * @return void
 */
function testFieldsWithExpression() {
$expression =
$this-testDb-expression(SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\Sample.case_col\);
$result = $this-testDb-fields($this-Model, null, array(id,
$expression));
$expected = array(
'`TestModel`.`id`',
SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\Sample.case_col\
);
$this-assertEqual($result, $expected);
}

One could argue that I shouldn't be using DECODE anyways as it is Oracle SQL
specific. If this app were to change databases, I would have to rewrite a
lot of custom fields that don't have cross-platform SQL. Is that a valid
argument IYO?

On Thu, Feb 18, 2010 at 1:51 PM, Martin Radosta martinrado...@gmail.comwrote:

  I would only change:

 $this-find('all', array('fields' = $fields,...etc, etc.)

 Would be great if you post the test case (or even better, a patch) to avoid
 other people waste time with the same issue.

 Bye

 MARTIN




 On 02/18/2010 05:39 PM, Tony Fugere wrote:

 It does not fix the problem. Am I doing it correctly?

 $dbo = $this-getDataSource();
 $expression =
 $dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
 \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1');
 $fields = array('ServiceData.stat_date',
  $expression);
 $this-find('all', array($fields,...etc, etc.)

 Still results in the ugly data.

 HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
 I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
 test case and report the issue of DECODE failing to function properly.

 Thanks for your help Martin!

 -Tony

 On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta 
 martinrado...@gmail.comwrote:

 Check the text case in this commit for an example:


 http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

 let me know if that fix the problem, if not, you can prepare a test case
 and report the issue.




 On 02/18/2010 03:21 PM, TonyFugere wrote:

 I don't follow what you mean by Try using expression for fields. Can
 you clarify or give me an example?

 On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:


 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:



 I've search this group and Google for a solution without any luck.




 I can read (SELECT) data, but cannot change this database at all. To
 get the data I run the following find:




 $this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
 ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
 ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
 ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
 ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
 ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
 ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
 ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
 ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
 ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
 'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
 );




 This is the resulting query:




 SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',

function not found using Oracle cake_1.2.0.7296-rc2

2008-09-09 Thread Anthony Smith


Greetings,

I downloaded cake yesterday and I was going through the tutorial and
found a wii bit of trouble. I get this message when I go to a page.

Fatal error: Call to undefined method DboOracle::fetchResult() in /usr/
local/apache2-development/htdocs/cake/cake/libs/model/datasources/
dbo_source.php on line 345


I saw this:
https://trac.cakephp.org/ticket/5038

But I am not sure how best to proceed. Should I be using cake 1.1? is
there a stable version present where this would work?

It seems as if many people were using Cake and oracle, but is that not
true?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
CakePHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---

Re: Using Oracle with Cake

2006-05-05 Thread [EMAIL PROTECTED]


The latest CakePHP 1.x has a dbo_odbc.php.  Have you tried setting up
your Oracle as ODBC and using that??


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups Cake 
PHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~--~~~~--~~--~--~---

Re: Using Oracle with Cake

2006-05-04 Thread Thomas Baron


I'm interested in an Oracle dbo driver for Cake. Can you post your code
so I can test it and report any bug/info ?
Otherwise, what about a Oracle support in Cake (either by OCI native or
PEAR driver) ?
nate, you told me once a PEAR driver may be available in May... Any
info or delay about it ?

thanks by advance
tom (going to install 1.0, great !)

nate wrote:
 Open an Enhancement ticket on Cake Trac (https://trac.cakephp.org).
 Also, you can hop on Cake's IRC at irc.freenode.net, or
 http://irc.cakephp.org.  Or email me.  I just implemented an SQL Server
 driver, so I might be able to lend you some insights.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups Cake 
PHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~--~~~~--~~--~--~---

Re: Using Oracle with Cake

2006-05-04 Thread Dave Rogers


There were some things with my dbo_oracle that were causing problems:

* PHP OCI functions do not return the table, so everything was being
  put into the [0] array, which caused problems if the column name
  from different tables were used.
* The base dbo_source.php file was aliasing tables with the keyword AS,
  which Oracle doesn't seem to like.  It works ok without the 'AS'
  keyword.
* There is no 'insert_id' function for Oracle like there is for MySQL.
  We are using a sequence table.  I think this is standard for Oracle,
  so I need to figure out a way to get the lastInsertID working
  properly.
* I'm sure that there will be other things that crop up.

I am trying to get this all working, but I am having to delve into the
dbo_source.php and datasource.php files, so it's not going to be
pretty.

I can't seem to get adodb-oci8 to work and I'm not sure about a PEAR
driver.

Anyone have any more suggestions?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups Cake 
PHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~--~~~~--~~--~--~---

Re: Using Oracle with Cake

2006-05-04 Thread Dave Rogers


I have a not so elegant first round attempt at getting oracle to work.
I modified the dbo_source.php and datasource.php files.  I also created
a dbo_oracle.php file.

The code for all three is at
http://cakephp.org/pastes/show/081dbc0d1d0c131feec6be10e02d9285

Please give me suggestions on how to improve it.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups Cake 
PHP group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cake-php
-~--~~~~--~~--~--~---

Re: Using Oracle

Re: Using Oracle

Re: Using Oracle

Re: Using Oracle

Using Oracle

Complex find() using Oracle

Re: Complex find() using Oracle

Re: Complex find() using Oracle

Re: Complex find() using Oracle

Re: Complex find() using Oracle

Re: Complex find() using Oracle

Re: Complex find() using Oracle

function not found using Oracle cake_1.2.0.7296-rc2

Re: Using Oracle with Cake

Re: Using Oracle with Cake

Re: Using Oracle with Cake

Re: Using Oracle with Cake

17 matches

Site Navigation

Mail list logo

Footer information