Issue in fetching data from database

Hi all,

We are facing a issue in fetching data from microsoft SQL Server 2000 (installed on another machine) using iwperl.

We have created a test.pl to test the DSN and database connection and uses the same function as in our .ipl file. The code for our test.pl is:

use DBI;
use DBD: Smiley Surprised

DBC;
my $data_type = "locale";
#connect to the al2 database
my $dbh = DBI->connect( 'DBI Smiley Surprised

DBC:myDSN','user_name','password')
|| die "cannot connect to database: $DBI::errstr\n";
my $sql = "select locale_code, language_desc from locale";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();
# Read the values and populate the select list
while (@data = $sth->fetchrow_array()) {
$data[1] =~ s/&/&/g;
print $data[1];
}
$sth->finish;
$dbh->disconnect;

This works fine for us. But when we try to connect the same database using same DSN and fire the same query through our .ipl script (i.e. from teamsite), we received an error message which says:

[Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied. (SQL-08001)
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). (SQL-01000)(DBD: db_login/SQLConnect err=-1)

I have explored documents, searched on net also. But wasn't able to find the solution. It would be a great help if anyone can help me in this.

Our machine and teamsite configuration is as follows:
OS: Microsoft Windows 2000 Server (SP4)
Teamsite: 6.5 SP3
SQL Server 2000 is installed on Windows 2003.

Thanks in advance...

Find more posts tagged with

Comments

Adam Stoller

Hi all,

We are facing a issue in fetching data from microsoft SQL Server 2000 (installed on another machine) using iwperl.

We have created a test.pl to test the DSN and database connection and uses the same function as in our .ipl file. The code for our test.pl is:\
[...]
This works fine for us. But when we try to connect the same database using same DSN and fire the same query through our .ipl script (i.e. from teamsite), we received an error message which says:
[...]
Thanks in advance...

What do you mean by "from teamsite" - are you talking about running your script from:

to exist when run outside the context of TeamSite.

romil_rose

Hi ghoti,

Thanks for the reply. We are using "FormPublisher inline callout", and our interwoven server is not on the domain.

Yes, you were right. We are getting null in environment variables after using your code in our script.

I have checked this over internet and found that Interwoven TeamSite Data Capture Template inline callout invocations clear all environment variables before forking our process (this is for security reasons).

Please suggest us what to do in this case.

Thanks a lot for your help.

Adam Stoller

Hi ghoti,

Thanks for the reply. We are using "FormPublisher inline callout", and our interwoven server is not on the domain.

Yes, you were right. We are getting null in environment variables after using your code in our script.

I have checked this over internet and found that Interwoven TeamSite Data Capture Template inline callout invocations clear all environment variables before forking our process (this is for security reasons).

Please suggest us what to do in this case.

Thanks a lot for your help.

What you need to do is explicitly set the environment information in your script:

$ENV{key} = value;

for each key that you need in the environment in order for your code to work (I believe it's usually some small number of environment variables, a half-dozen or less). If you want to extrapolate the information, create an external configuration file for the information and put code in your script to read and process the configuration file to set the environment variables.

You may have to do the environment variable setting within a

BEGIN{
    ...
};

block to ensure that the information exists prior to some other module's (e.g.: DBI) attempt to utilize that information for its own purposes.

If you search the forums, you should be able to find various threads that cover this in varying degrees of detail.

romil_rose

Hi Ghoti,

Thanks for spending you valuable time in helping me out. I have checked the environment settings which seems fine.

Also one more thing, when I call my .IPL through command prompt (i.e. by using command:

\>D:\iw-home\iw-perl\bin\iwperl.exe

\iw-home\custom\bin\agentlink2\inline\db_option_list.ipl agrument)

it works fine and returns me XML:

.
.
.

But when I do "New Form Entry" in teamsite for datacapture.cfg, it gives me error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000)(DBD: db_login/SQLConnect err=-1)

Please help me out what to do in this scenario.

ISCBorisB

But when I do "New Form Entry" in teamsite for datacapture.cfg, it gives me error:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000)(DBD: db_login/SQLConnect err=-1)

Please help me out what to do in this scenario.

Have you set Environment information within a BEGIN{...}; block so that they are evaluated before 'use' statements?
If the answer is 'No' - do that and try again. If the answer is 'Yes' you may still be missing some Environment Variable(s).

Another issue to check is your SQL Server security. Note that when you run your script from the command mode and when
TST Engine runs it on your behalf accounts and therefore the security contexts differ. I *think* that for Windows an account is
SYSTEM although it may be "TSIMP_<yourHostname>" account as well (That should not matter if you supply UID and
password right from connect Method)

romil_rose

Hi ISCBorisB,

Thanks for your reply. Following is the code I pluged in my script to check and set the environment variables:

********************************************************

BEGIN {
#$ENV{SYSTEMROOT} = "C:\\WINDOWS";

$ENV{Path} = "%SystemRoot%\\system32;%SystemRoot%;%SystemRoot%\\System32\\Wbem;C:\\Program Files\\Common Files\\Interwoven\\lib;D:\\iw-home\\bin;D:\\iw-home\\iw-perl\\lib;D:\\iw-home\\iw-perl\\vendor\\lib";

$ENV{TEMP} = "%SystemRoot%\\TEMP";

$ENV{TMP} = "%SystemRoot%\\TEMP";
}
use File::Temp qw(tempfile);
my($fh, $fname) = tempfile();
foreach my $key (sort(keys(%ENV))){
print $fh "$key\t$ENV{$key}\n";
log($key);
log($ENV{$key});
}
close($fh);
warn("CHECK TEMP FILE: $fname\n");

********************************************************

And I am connecting to the database using the following lines:

my $dbh = DBI->connect('DBI Smiley Surprised

DBC:myDSN','user_name','password')
|| die "cannot connect to database: $DBI::errstr\n";

After executing, following is the log I am getting:

May-14-2007 20:58:48 - IW_DCT:
May-14-2007 20:58:48 - //interwoven/default/main/WesternUnion/External/AgentLinkII/WORKAREA/Development/templatedata/AL2_Pages/test/datacapture.cfg:
May-14-2007 20:58:48 - IW_ROLE:
May-14-2007 20:58:48 - master:
May-14-2007 20:58:48 - IW_USER:
May-14-2007 20:58:48 - INTERWOVEN\administrator:
May-14-2007 20:58:48 - IW_WORKAREA:
May-14-2007 20:58:48 - //interwoven/default/main/WesternUnion/External/AgentLinkII/WORKAREA/Development:
May-14-2007 20:58:48 - PATH:
May-14-2007 20:58:48 - %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Common Files\Interwoven\lib;D:\iw-home\bin;D:\iw-home\iw-perl\lib;D:\iw-home\iw-perl\vendor\lib:
May-14-2007 20:58:48 - SYSTEMROOT:
May-14-2007 20:58:48 - C:\WINDOWS:
May-14-2007 20:58:48 - TEMP:
May-14-2007 20:58:48 - %SystemRoot%\TEMP:
May-14-2007 20:58:48 - TMP:
May-14-2007 20:58:48 - %SystemRoot%\TEMP:
May-14-2007 20:58:48 - TMPDIR:
May-14-2007 20:58:48 - :
May-14-2007 20:58:48 - argument is: : locale
May-14-2007 20:59:04 - db_option_list.ipl: datatype: error: cannot connect to database: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. (SQL-08001)
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()). (SQL-01000)(DBD: db_login/SQLConnect err=-1)

I am not able to to find the problem here. Please help me out what to do in this case.

ISCBorisB

There are few issues with your code:

1. SYSTEMROOT setting is commented out. Note that (as far as I recall) this is not supplied by TST.

2. You should not use %String substitute when setting $ENV Variables, it's too late for that (your shell is already forked).
In other words, you have to do all substitution of that nature in your code and set "ready to go" Environment Variables.

3. Environment variables are case sensitive, SYSTEMROOT and SystemRoot are not the same things.

4. Heed good advice!

If you want to extrapolate the information, create an external configuration file for the information
and put code in your script to read and process the configuration file to set the environment variables

You can initially populate this Env Configuration File by running "set >YourConfigurationFile" from the TS server command prompt.
You'll get plenty more than you need but at least it'll contain all Environment variables that make your script operational in command mode.

romil_rose

Hi All,

The problem was only with Environment Settings. After doing some R&D with Environment Variables, it working fine.

Thanks a lot for helping me out in this.