Connecting to Oracle?

development@wohlsen.com

I'm trying to connect Editor to an Oracle database, but I'm getting this javascript error. It works fine with Mysql, but I can't get the connection to Oracle working. I'm entering the credentials in the php/config.php file the same way that I successfully did for Mysql. I have other php pages that aren't using Editor, they are just using the php function oci_connect(), and they successfully connect to the same Oracle database so I know the credentials I am using are correct. What am I missing or doing wrong? I have removed the username, password, and host from my example here for security purposes, but they are there in my actual code.

    $sql_details = array(
        "type" => "Oracle",  // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
        "user" => "usernamehere",       // Database user name
        "pass" => "passwordhere",       // Database password
        "host" => "hosthere",       // Database host
        "port" => "",       // Database connection port (can be left empty for default)
        "db"   => "prod",       // Database name
        "dsn"  => "prod"        // PHP DSN extra information. Set as `charset=utf8` if you are using MySQL
    );

development@wohlsen.com

I discovered that changing line 67 of the php/Database/Driver/Oracle/Query.php page seems to have fixed the issue.

allan

Thanks for posting this! Good to hear you have a workaround.

Is your host localhost?

Allan

development@wohlsen.com

The host is not localhost. It is another server in our network. After changing that line of code, I thought the issue was resolved, but I'm not sure. Now I'm getting a new error:

"DataTables warning: table id=example - Error executing SQL for data get. Enable SQL debug using '->debug(true)'"

Where do I add the code ">debug(true)"? Is there an example page somewhere with this debug line?

allan

Hi,

The reason I asked about the host was that with that change I'm not entirely sure how the system knows which host to connect to. Unless your Oracle client has been configured with that already?

However, that's really beside the point now - it sounds like you've got a connection if its giving SQL errors!

The debug mode can be enabled by adding ->debug( true ) into the Editor statement chain anywhere before the process() method is called. i.e. you might have:

...
  ->debug( true )
  ->process( $_POST )
  ->json();

That will show the SQL that Editor is attempting to execute in the JSON response from the server (which in turn you will be able to see in the Network tab in your browser's development tools).

Are you able to show me that SQL statement?

Thanks,
Allan

development@wohlsen.com

Thanks, the debug code I'm seeing in the network tab is this:

"debugSql":[{"query":"SELECT id as \"id\", wcctn_course as \"wcctn_course\" FROM wcc_eport_train_course ","bindings":[]}]}

It makes sense why this isn't working now, this table does not actually have a field named "id". I'm not sure why that's part of the query, as I'm only asking for the "wcctn_course" field. Is there a a way I can remove the "id" from the query that's being generated? Here is the code I'm using:

<?php

$WCC_DataTablesEditor_connection = "oracle";
include( "../includes/DataTablesEditor/php/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

Editor::inst( $db, 'wcc_eport_train_course' )
->fields(
Field::inst( 'wcctn_course' )
)

->debug(true)
->process( $_POST )
->json();

tangerine

If 'id' is not your primary key, you have tell the Editor what your pk is named.
Add a third parameter to your Editor instance call.
https://editor.datatables.net/manual/php/getting-started#Primary-key-name