Error opening DB

Applications Database Connections
#1

Hi to All,

I have created a little project that use Oracle DB. In SC I have tested successfull the connection.

The first application is like the start_up_screen of sc_tickets example. This control call in “onScriptInit” this function in a my Project library:

<?php

function LoadSettings(){

	$str_sql = "select
			sys_smtpserver,
			sys_smtpport,
			sys_smtpuser,
			sys_smtppassword,
			sys_companyname,
			sys_emailaccount,
			sys_assigmentmode,
			sys_definedparameters,
			sys_defaultpriority,
			sys_smtpsecurityflag,
			sys_defaultlanguage,
			sys_version
		  FROM
			sys$eclipse.sys_settings
		  WHERE
			sys_id = 1";
			
	sc_lookup(dataset,$str_sql);
	
	$arr_load = array();
	$arr_load['sys_smtpserver']      = (!empty({dataset}[0][0]))?{dataset}[0][0]:'';
	$arr_load['sys_smtpport']       = (!empty({dataset}[0][1]))?{dataset}[0][1]:'';
	$arr_load['sys_smtpuser']       = (!empty({dataset}[0][2]))?{dataset}[0][2]:'';
	$arr_load['sys_smtppassword']     = (!empty({dataset}[0][3]))?{dataset}[0][3]:'';
	$arr_load['sys_companyname']      = (!empty({dataset}[0][4]))?{dataset}[0][4]:'';
	$arr_load['sys_emailaccount']     = (!empty({dataset}[0][5]))?{dataset}[0][5]:'';	
	$arr_load['sys_assigmentmode']     = (!empty({dataset}[0][6]))?{dataset}[0][6]:'';
	$arr_load['sys_definedparameters']   = (!empty({dataset}[0][7]))?{dataset}[0][7]:'';
	$arr_load['sys_defaultpriority']    = (!empty({dataset}[0][8]))?{dataset}[0][8]:'';
	$arr_load['sys_smtpsecurityflag']   = (!empty({dataset}[0][9]))?{dataset}[0][9]:'';
	$arr_load['sys_defaultlanguage']    = (!empty({dataset}[0][10]))?{dataset}[0][10]:'it_it;it_it';
	$arr_load['sys_version'] 	    = (!empty({dataset}[0][11]))?{dataset}[0][11]:'';	

	$_SESSION['myAppSettings'] = $arr_load;	

}

?>

Using Toad I have tested the query and work fine. But Wher I run the control this error is returned:

AUTHENTICATION FAILURE
ociexecute() [function.ociexecute]: ORA-00942: table or view does not exist

I have tried to explore the source generated from SC, but I don’t be able to find how is established the oci connection.

Can anyone help me ?

Thank You and Best Regards.
Gaetano

#2

Re: Error opening DB

Turn on the debug mode.

problably the error is the name of the table sys$eclipse.sys_settings … “$” is how you start a variable in oracle.
You should protect with … i really dont know if everything will work fine with $ … i think that you will have more problems.

#3

Re: Error opening DB

Are you logging in to TOAD with the same user as you are using for the database connection in Scriptcase? I’d guess not and you are logging into TOAD with sys, system or maybe sys$eclipse and the database connection is a much less powerful user with not enough grants on this database or missing a synonym (or maybe a few if it is a view).

#4

Re: Error opening DB

I think that the problem is …

$str_sql = “select * FROM sys$eclipse.sys_settings …”;

will became

$str_sql = “select * FROM sys.sys_settings …”;
because php will try to interpret $eclipse as a variable. Turn the debug mode on.
To prevente this, your sql must be:
$str_sql = “select * FROM sys$eclipse.sys_settings …”;

#5

Re: Error opening DB

digo,
I had similar problem, and already opened support ticket.

I had found the escape sequence issued in the query is not handled by scriptcase, Please refer my earlier post
http://www.scriptcase.net/forum_en_us/index.php?topic=1744.msg5558#msg5558

and see if you can help me on this

Regards
Dhana

#6

Re: Error opening DB

i answered there …

#7

Re: Error opening DB

Ok,

This problem is solved!

The problem is the character $ in the schema’s name. Other programming language o developer tools (for example Oracle Forms) don’t have problems with the special character $, unlike PHP or Javascript.

I have renamed my Oracle Schema syseclipse instead sys$eclipse and all work fine.

For Dhana:

I have submitted a large documented trhead to Netmake support, because the single quote used in the variables cause a failure of Scriptcase.

Many languages, as italian, use fully the single quote; for example:

'Confermi l’eliminazione? The english translation is ‘Confirm deletion?’

The second single quote is interpreted from user Javascript functions or Scriptcase Macros as end of string and than a ‘)’ or ‘;’ is expected. Of course the applications does not work and very often returns an error.

You can try this:

  1. Configure from Options, first in Settings and after in My Scriptcase, the italian language;
  1. restart Scriptcase (at login ITALIAN);
  2. Open the Helpdesk project sc_tickets (this project use libraries);
  3. From menu Tools, choose Libraries;
  4. Choose project

No libraries are displayed to modify and it is impossible to create a new library. If You use as Browser Mozilla Firefox with the Firebug plugin actived, this error is reported:

This happens with many other functions of scriptcase, also with locations other than the Italian. The Netmake support has not yet answered (about a month - I have opened the trhead at 5, August 2010)

Thanks to All and Best Regards
Gaetano

#8

Re: Error opening DB

Gaetano,
Thanks…! let me try and keep posted here

regards
dhana