I’m trying to use the DB Connector and DB Data Source to access my Oracle database so I can add two numbers! I’ve setup my DB Connector with the correct user details. I’ve added my connector asset to my DB Data Source. I’ve read the Data manual that says:
[size=2]If the database details are incorrect, a MySource Warning will be shown indicating that it cannot connect to the database.[/size]
When I try and access the Query builder screen for my DB Data Source asset Squiz stops responding and after 1 minute I get the 504 error:
[color="#0000FF"][size=3]Could not load children: Server returned HTTP response code : 504 for url: http://… etc[/size]
I would assume that the account details are correct as I am not getting any MySource Warning but the account dose not have the correct privileges to perform some task in the database!!! I’m using v3.18.9. I can connect direct to the Oracle database with the user settings I am using in Squiz and I am able to create table with that user etc…
Any thoughts very welcome.
Cheers
Having done some more testing on this, the Oracle logs show no connection from squiz to the Oracle database. From the Squiz server a sql connection can be made with SqlPlus to the Oracle database. Using the user account set up in squiz, the user can run the select statement setup in squiz via SqlPlus.
Does anyone know of any error logging in Squiz that I could try look at to see where the connection is breaking down?
Cheers.
I now have two ‘DB Data Source’ assets in my menu that if I click on them will cause my browser to hang! I’ve tried to move them to the trash but just doing this causes by browser to hang. 
[quote]I’m trying to use the DB Connector and DB Data Source to access my Oracle database so I can add two numbers! I’ve setup my DB Connector with the correct user details. I’ve added my connector asset to my DB Data Source. I’ve read the Data manual that says:
[size=2]If the database details are incorrect, a MySource Warning will be shown indicating that it cannot connect to the database.[/size]
When I try and access the Query builder screen for my DB Data Source asset Squiz stops responding and after 1 minute I get the 504 error:
[color="#0000FF"][size=3]Could not load children: Server returned HTTP response code : 504 for url: http://… etc[/size]
I would assume that the account details are correct as I am not getting any MySource Warning but the account dose not have the correct privileges to perform some task in the database!!! I’m using v3.18.9. I can connect direct to the Oracle database with the user settings I am using in Squiz and I am able to create table with that user etc…
Any thoughts very welcome.
Cheers
Having done some more testing on this, the Oracle logs show no connection from squiz to the Oracle database. From the Squiz server a sql connection can be made with SqlPlus to the Oracle database. Using the user account set up in squiz, the user can run the select statement setup in squiz via SqlPlus.
Does anyone know of any error logging in Squiz that I could try look at to see where the connection is breaking down?
Cheers.
I now have two ‘DB Data Source’ assets in my menu that if I click on them will cause my browser to hang! I’ve tried to move them to the trash but just doing this causes by browser to hang. [/quote]
The “504” error corresponds to a “Gateway timeout” which would suggest a connectivity issue. There might be some information in the Matrix error.log file relating to this.
Edit: In my initial post a few minutes ago I suggested that an intensive SQL query might be causing the timeout, however after reading the post again there is actually an issue connecting to the database which would seem to eliminate this possibility.
Hi Mark
I have looked in the error.log and there is no relevant information with regards my trying to make a connection to the database.
I had a word with the infrastructure team here and they gave me the following DNS string to paste into the DNS field.
'db' => array (
'DSN' => 'ORACLE',
'user' => '',
'password' => '',
'type' => 'oci',
'encoding' => 'AL32UTF8',
'persistent' => true,
),
Adding this string [*'s replace the username and password settings] in the DNS field seems to do something! The squiz matrix does not freeze now. Clicking on the DB Data Source now allows me to at least build a SQL query and save it without having to close all my firefox sessions and restart.
However, the current documentation for the Data Module seems to be way out of date? The current documentation talks about 'Choose your mode'. I do not have this section at all! I seem to be in some sort of manual edit mode only. I only have three sections:
- SQL To Execute
- Record Set Asset Names
- Available Keywords
I built a simple sql query and saved it in the SQL To Execute section:
SELECT 2 + 3 as Total From Dual
[All I want to do is add two numbers together… in Squiz… not Oracle!!!]
I added a name for the results of my record set in the 'Record Set Asset Names' section, and added nothing in the Available Keywords section as this has no input fields.
From the documrntation it talks about switching the query to execute and commiting the changes!!! I can not find any way of setting the query to Execute. When I save the query the SQL to execute field I get no errors or warnings, so assume all is OK. From the documentation, if all was OK, I would expect to see the 'SQL To Execute' feild to be inactive now, but it is still active. However I do not get any 'Shadow Assets' under my DB Data Source asset. So I would think that something is still not working. Or am I not suppose to get any Shadow assets? The error.log in Squiz is still showing no error information!
So is the connection to the database good?
Has the SQL been executed OK?
Is my DNS in teh correct format?
Who knows??!!
Does anyone have any upto date documentation for this Module?
Cheers.
Two things: First, the query builder was removed from Matrix in one of the more recent versions – the manual you have obviously reflects the older versions. Double check the matrix.squiz.net site to see if there is a newer copy. Secondly, it's advisable to build/test your queries in something other than Matrix so you can see the exact results you getting (as well as any errors), then paste that query into the DB Data Source asset. I've seen the admin interface hang if you enter a query that takes too long for the server to respond.
The latest version of the data manual is for version 3.14.
At this stage I cannot say when the latest version of the data manual will be released as I am working on another project.
I'm glad it was possible to resolve your database issue.
[quote]However, the current documentation for the Data Module seems to be way out of date? The current documentation talks about 'Choose your mode'. I do not have this section at all! I seem to be in some sort of manual edit mode only.
…
Does anyone have any upto date documentation for this Module?[/quote]
Our Matrix Documentor is currently on another project and as yet there is no announcement regarding the availability of a new manual for the Data module. I would recommend following the detailed release notes in the CHANGELOG file in your Matrix root directory. These will point you in the direction of any new functionality changes in your major release version.
[quote]From the documrntation it talks about switching the query to execute and commiting the changes!!!! I can not find any way of setting the query to Execute. When I save the query the SQL to execute field I get no errors or warnings, so assume all is OK.[/quote]
The "Execute" functionality was removed from 3.18.x due to changes in how we access the database. Committing the query should immediately execute the query and produce Shadow Assets underneath the DB Data Source asset.
[quote]So is the connection to the database good?
Has the SQL been executed OK?
Is my DNS in teh correct format?[/quote]
Please try to select from a Matrix table as an example. I haven't tried an in-place query in the form of the mathematical query demonstrated, however this should probably work as well. Assuming the connection details in the DB Data Source are those of a Matrix system, please try the following query:
SELECT * FROM sq_ast WHERE type_code = 'site';
The above query should return the main asset variables for each Site asset in your system. Shadow Assets should be created under the DB Data Source and these should each show the columns from the table for each record. This will narrow down whether it is a problem with the initial query supplied.
Hi Mark
Thanks for the help. I think I am making progress… slowly!
I tried your SQL in the SQL Query field but still no results. I have now setup SQLPlus on my PC and can connect to the Oracle database with the username and password combination I am trying to use in Squiz. From within sqlplus I can run your query and get results back. Thus the user has access and the right permissions on the database.
Do you have an example of a 'Complete DSN' string that Squiz will except?
The following string that I was using as a DNS string I now know does not connect to the Oracle database. It also is not held by Squiz when I click on commit. Thus from this I would assume the parameters are in the wrong format:
'db' => array (
'DSN' => 'ORACLE',
'user' => '',
'password' => '',
'type' => 'oci',
'encoding' => 'AL32UTF8',
'persistent' => true,
),
If I change the format to the following string:
dbname=oracle;host=ORACLE;
This is held in the DSN field when I click commit. However this DNS string still does not connect to the oracle database. If I was to add the type, encoding, persistent attributes do you know what parameter names Squiz would require for these valuse?
I have tried: dbname=oracle;host=ORACLE;type=oci;encoding=AL32UTF8;persistent=true; This string is excepted by Squiz but again does not connect to the Oracle database.
If I populate the Database type, Database name and Host name fields in the database details section and do not use the DNS field my DB Data Source hangs every time. So using these fields is not an option.
Cheers
Also tried the Oracle type connection string:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE)(PORT=1521)(CONNECT_DATA=(SID=oracle)))
But still no joy. This string is excepted by Squiz so I would assume that its in the correct format!
[quote]Also tried the Oracle type connection string:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE)(PORT=1521)(CONNECT_DATA=(SID=oracle)))
But still no joy. This string is excepted by Squiz so I would assume that its in the correct format!
:([/quote]
Maybe you should contact Squiz support about this, as they would be able to give you more help on this.
Yep, the oracle db source is a real pain to setup, but have you thought of using remote content to add the numbers? eg just have a php script on the server to add them?
Hi ndrw
Thanks for the pointer. This would be a solution but now I need to make a connection to the Oracle database to run a query so need to get the connection working. When you say
[quote]Yep, the oracle db source is a real pain to setup…[/quote]
Have you managed to setup a connection to an Oracle database?
If so, did you use the Complete DNS field rather than the Database details section?
If so, do you have an example of the connection string that worked for you?
I can't see why this should be so hard. Our version of Squiz is running from the Oracle database so there is a connection being made by Squiz!!
Cheers
Sorry I missed this post, I'm running 3.16.9 so it is probably different for you.
i use a manual DSN like:
oci8://user:password@database.domain.com - database.domain.com is going to be the TNS database name you use with other sql clients.
You may be able to do some debugging in data_source_db.inc (probably function &connectToDB()) using the good old error_log('Descriptive string:' . $variable); trick.
Edit: Have you tried using the DSN format from your matrix /conf/db.inc file?