External database integration

Next topic

Author	Message
ciradhb forward	Monday 21 March 2005 5:00:44 am Hi, I think my need is preety simple but I didn't read anything about it in EZPublish documentation. I have an existing database that stores a lot of scientific data. I need to create EZPublish content classes that integrate on one hand standard attributes like text, URL, email, images, etc , but on the other hand some attributes that should collect their values by a request on the external database. Conceptually, I would like to have a datatype for these attributes that could allow me to specifify that their value is the result of a SQL request like : "SELECT Field1,Field2,Field3 FROM MyBase::MyTable WHERE Field4=$X" where $X could be a value that is entered when the content classe is instanciated. For instance, imagine an ecommerce site that would create pages describing their products and that the price attribute of each product is the result of "SELECT price FROM product WHERE product_ref=$Ref" . I guess it is a basic feature for a CMS like EzPublish, and maybe there are several ways to do it. But I did not found the solution in EZP docs. Can someone help me ? Thanks in advance. HB
Frederik Holljen	Monday 21 March 2005 6:44:47 am Hi, This issue depends on a few variables that you do not mention in your request: - how do you want to control exactly which resource to select from in the external database (by another attribute in the content object?) - do you want to be able to "edit" the external attributes from eZ publish? - do you need translation or version control? If you don't need translation or version control I would suggest to write new datatypes for each of the different type of fields from the external resource (e.g external int, external varchar etc.) use a native eZ publish attribute to control the id of the external content row. I think some of our partners have developed something like this in the past. Frederik
ciradhb forward	Monday 21 March 2005 7:50:28 am Hi Frederik, In a first step, I only expect to request my database in a static way. I know the table and field names in advance, so I can hardcode them. I only need to allow a contributor to specify a value for the $X variable in the WHERE of the SELECT clause. Actually, for the moment I only want to know how to send a request to an external database and get the result in a content object. Maybe in a second step, I will want to do it more dynamically and be able to specify more elements of the SELECT clause. What do you mean by "edit the external attribute" ? To change its value and update the external DB ? Not for the moment, my hope is just to request the external DB, get the result, and integrate this result in a EZP content object. No translation or version control either. Thanks for your help . HB

Author

Message

Monday 21 March 2005 5:00:44 am

Hi,
I think my need is preety simple but I didn't read anything about it in EZPublish documentation.

I have an existing database that stores a lot of scientific data. I need to create EZPublish content classes that integrate on one hand standard attributes like text, URL, email, images, etc , but on the other hand some attributes that should collect their values by a request on the external database.

Conceptually, I would like to have a datatype for these attributes that could allow me to specifify that their value is the result of a SQL request like : "SELECT Field1,Field2,Field3 FROM MyBase::MyTable WHERE Field4=$X"
where $X could be a value that is entered when the content classe is instanciated.

For instance, imagine an ecommerce site that would create pages describing their products and that the price attribute of each product is the result of "SELECT price FROM product WHERE product_ref=$Ref" .

I guess it is a basic feature for a CMS like EzPublish, and maybe there are several ways to do it. But I did not found the solution in EZP docs. Can someone help me ?

Thanks in advance.

Frederik Holljen

Monday 21 March 2005 6:44:47 am

Hi,

This issue depends on a few variables that you do not mention in your request:

- how do you want to control exactly which resource to select from in the external database (by another attribute in the content object?)
- do you want to be able to "edit" the external attributes from eZ publish?
- do you need translation or version control?

If you don't need translation or version control I would suggest to write new datatypes for each of the different type of fields from the external resource (e.g external int, external varchar etc.) use a native eZ publish attribute to control the id of the external content row.

I think some of our partners have developed something like this in the past.

Frederik

ciradhb forward

Monday 21 March 2005 7:50:28 am

Hi Frederik,

In a first step, I only expect to request my database in a static way. I know the table and field names in advance, so I can hardcode them. I only need to allow a contributor to specify a value for the $X variable in the WHERE of the SELECT clause.
Actually, for the moment I only want to know how to send a request to an external database and get the result in a content object. Maybe in a second step, I will want to do it more dynamically and be able to specify more elements of the SELECT clause.
What do you mean by "edit the external attribute" ? To change its value and update the external DB ? Not for the moment, my hope is just to request the external DB, get the result, and integrate this result in a EZP content object. No translation or version control either.

Thanks for your help .

eZ debug

Timing:	Jan 18 2025 22:31:49
Script start
Timing:	Jan 18 2025 22:31:49
Module start 'layout'
Timing:	Jan 18 2025 22:31:49
Module start 'content'
Timing:	Jan 18 2025 22:31:50
Module end 'content'
Timing:	Jan 18 2025 22:31:50
Script end

Main resources:

Total runtime	1.2033 sec
Peak memory usage	4,096.0000 KB
Database Queries	57

Timing points:

Checkpoint	Start (sec)	Duration (sec)	Memory at start (KB)	Memory used (KB)
Script start	0.0000	0.0052	589.0469	152.6250
Module start 'layout'	0.0052	0.0027	741.6719	39.4453
Module start 'content'	0.0079	1.1939	781.1172	509.6172
Module end 'content'	1.2018	0.0015	1,290.7344	12.1641
Script end	1.2033		1,302.8984

Time accumulators:

Accumulator	Duration (sec)	Duration (%)	Count	Average (sec)
Ini load
Load cache	0.0034	0.2827	16	0.0002
Check MTime	0.0013	0.1058	16	0.0001
Mysql Total
Database connection	0.0008	0.0689	1	0.0008
Mysqli_queries	1.1529	95.8100	57	0.0202
Looping result	0.0006	0.0466	55	0.0000
Template Total	1.1719	97.4	2	0.5859
Template load	0.0023	0.1944	2	0.0012
Template processing	1.1695	97.1908	2	0.5848
Template load and register function	0.0002	0.0148	1	0.0002
states
state_id_array	0.0009	0.0712	1	0.0009
state_identifier_array	0.0026	0.2123	2	0.0013
Override
Cache load	0.0020	0.1651	29	0.0001
Sytem overhead
Fetch class attribute can translate value	0.0006	0.0538	2	0.0003
Fetch class attribute name	0.0013	0.1042	4	0.0003
XML
Image XML parsing	0.0009	0.0763	2	0.0005
class_abstraction
Instantiating content class attribute	0.0000	0.0008	4	0.0000
General
dbfile	0.0007	0.0593	16	0.0000
String conversion	0.0000	0.0007	4	0.0000
Note: percentages do not add up to 100% because some accumulators overlap

Templates used to render the page:

Usage	Requested template	Template	Template loaded
1	node/view/full.tpl	full/forum_topic.tpl	extension/sevenx/design/simple/override/templates/full/forum_topic.tpl
3	content/datatype/view/ezxmltext.tpl	<No override>	extension/community_design/design/suncana/templates/content/datatype/view/ezxmltext.tpl
4	content/datatype/view/ezxmltags/line.tpl	<No override>	design/standard/templates/content/datatype/view/ezxmltags/line.tpl
6	content/datatype/view/ezxmltags/paragraph.tpl	<No override>	extension/ezwebin/design/ezwebin/templates/content/datatype/view/ezxmltags/paragraph.tpl
1	content/datatype/view/ezimage.tpl	<No override>	extension/sevenx/design/simple/templates/content/datatype/view/ezimage.tpl
1	print_pagelayout.tpl	<No override>	extension/community/design/community/templates/print_pagelayout.tpl
Number of times templates used: 16 Number of unique templates used: 6

Time used to render debug report: 0.0001 secs