[Info-Ingres] DBI and star
Martin Bowes
Hi All,
I have a STAR database in which a table is registered via a vnode which connects to the source database as a different username (Y) to the one (X) whom accesses the STAR database.
This works perfectly fine in terminal monitor. On the installation holding the source database the username X is not even defined.
But in Perl DBI it gets real funky and it appears that to get the connection working properly I will have to define the user X and grant it db_admin privilege on the source database.
When I connect to the STAR database I do:
$connect_db = ‘my_dbname_ddb/star’;
$user = ‘X’;
$Attributes = {AutoCommit => 0, PrintError => 0, RaiseError => 0};
unless ($dbh = DBI->connect(
"dbi:Ingres:$connect_db”
,$user
,"" #Password
,$Attributes))
{
… get all error messagy …
};
Has anyone seen anything like this before? Any tips?
Martin Bowes
nikosv
might be worth turning DBI tracing on and check what's happening behind the scenes
take a look at:
http://www.effectiveperlprogramming.com/blog/147
nikosv
Martin Bowes
Hi Niko,
Thanks for that intel, its allowed me to identify the area the problem comes from. I can also now replicate the problem with ESQLC.
The insert statement which triggers the problem in DBI/ESQLC is prepared and then executed.
strcpy(stmt, "insert into extern_runap(run_id, app_id, status)"
"values (?, ?, ?)");
EXEC SQL PREPARE ins_stmt FROM :stmt;
EXEC SQL EXECUTE ins_stmt USING :app_id, :run_id, :status;
If (sqlca. sqlcode != 0)
{
EXEC SQL INQUIRE_SQL(:errorno=dbmserror, :errortext=errortext);
printf ("Failed to insert into extern_runap!\n");
printf ("Error Code %d:\nReason: %s\n", errorno, errortext);
};
And at the execute we get the message: E_US000D The -u flag specified a nonexistent user.
The table which is registered as extern_runapp is held in a database on a separate installation which does not know the user executing the code.
However the user executing the code has registered the table specifying a vnode which authenticates as another user known to that separate installation (the table owner).
Checking with iiaudit I find that there was a connection attempt to iidbdb on the separate installation as the user running the code…Hence the –u message
When I execute the insert from within normal terminal monitor there is no such connection attempt, instead we see the username the vnode specified coming straight into the target database.
So what is prepare doing in a DDB?
Marty
-----Original Message-----
From: nikosv [mailto:niko...@gmail.com]
Sent: 08 June 2012 18:35
To: Martin Bowes
Subject: Re: DBI and star
On Jun 8, 2:05 pm, Martin Bowes <martin.bo...@ctsu.ox.ac.uk> wrote:
> Hi All,
>
> I have a STAR database in which a table is registered via a vnode which connects to the source database as a different username (Y) to the one (X) whom accesses the STAR database.
>
> This works perfectly fine in terminal monitor. On the installation holding the source database the username X is not even defined.
>
> But in Perl DBI it gets real funky and it appears that to get the connection working properly I will have to define the user X and grant it db_admin privilege on the source database.
>
> When I connect to the STAR database I do:
>
> $connect_db = 'my_dbname_ddb/star';
> $user = 'X';
> $Attributes = {AutoCommit => 0, PrintError => 0, RaiseError => 0};
> unless ($dbh = DBI->connect(
> "dbi:Ingres:$connect_db"
> ,$user
> ,"" #Password
> ,$Attributes))
> {
> ... get all error messagy ...
>
> };
>
> Has anyone seen anything like this before? Any tips?
>
> Martin Bowes
Hi Martin
nikosv
1.What happens if you implant the Vnode into the connection string? as in :
$dbh = DBI->connect ('DBI:Ingres:Vnode::database') || die "$DBI::errstr";
2.try using:
$dbh->do(insert ...)
which mixes both prepare and execute into one step
3.Wrap the insert into one stored proc and give the user permission to execute it