DBI: ODBC - Running Batch SQL 1

[clueo8]

I'm editing a program which will run a set of .sql batch statements. It uses the following code to connect to the database:
---------------------------------------------------------
use DBI;
use strict;

my $data_source="dbi:ODBC:$ENV{DSQUERY}";

# Connect to the db - retrieve db handle
my $dbh = DBI->connect($data_source,$ENV{SYBPRIVUSR},$ENV{SYBPRIVPWD}) or die $DBI::errstr;

# Switch to the environments db
my $sth = $dbh->prepare("USE $ENV{DBNAME}");
$sth->execute() or die $sth->errstr;
---------------------------------------------------------

With each line in the sql files, I have it load each segment of code before a GO statement to an array (to execute each segment seperately). The problem I'm having is that in the sql file, the statement sp_rename... will not run. It gives me errors like Invalid Curser position. I believe this is because the sp_rename statement returns a sucess/fail integer. If we can't get this to work, how else can I rename an object or column without using sp_rename and get the same exact results. If anyone could help me out, it would be greatly appreciated.

Tim

 

[stevexff]

From the DBI docs:

bind_param_inout

$rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len) or die $sth->errstr;
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr) or ...
$rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ...
This method acts like bind_param, but also enables values to be updated by the statement. The statement is typically a call to a stored procedure. The $bind_value must be passed as a reference to the actual value to be used.

Note that unlike bind_param, the $bind_value variable is not copied when bind_param_inout is called. Instead, the value in the variable is read at the time execute is called.

The additional $max_len parameter specifies the minimum amount of memory to allocate to $bind_value for the new value. If the value returned from the database is too big to fit, then the execution should fail. If unsure what value to use, pick a generous length, i.e., a length larger than the longest value that would ever be returned. The only cost of using a larger value than needed is wasted memory.

Undefined values or undef are used to indicate null values. See also Placeholders and Bind Values for more information.

I've not tried this, but it looks like it might do what you want...

 

[clueo8]

I was curious why you think this will solve my problem. I just wanted to include the code where I actually execute my sql statements:

------------------------------------------------------------
# Prepare the script
$sth = $dbh->prepare("$query");

# Execute the script
$sth->execute() or die;
------------------------------------------------------------

Also, could you give an example of the use of your code in my case?

And, is there an easy way to access and navigate through the DBI Docs?

 

[stevexff]

When you execute a statement, you can bind parameters to it. The doc above shows you how to set a parameter as in/out, and it says it is typically used in a call to a stored procedure. From the OP, it sounded like that's what you are trying to do.

However, back to sp_rename. A quick experiment (perl 5.8.6, SQL Server 2000) reveals that

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("DBI:ODBC:mydb", "user", "pwd") or die "Can't connect $DBI::errstr";
my $sql = q[EXEC sp_rename 'steve', 'stevex', 'OBJECT'];

my $sth = $dbh->prepare($sql);

my $sqlcode = $sth->execute();

$dbh->disconnect();

print "sqlcode = $sqlcode";

executes giving message

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Caution: Changing any part of an object name could break scripts and stored procedures. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]The OBJECT was renamed to 'stevex'. (SQL-01000) at tt.pl line 10.
sqlcode = -1

Despite all the bumf, and returning -1 to the caller, it works. When I look at the database, it has renamed my test table successfully.

The DBI doc indicates that calling stored procedures is 'not defined', and that some interfaces support it, and others may not. It looks like this one does both?

The DBI doc comes with ActivePerl (assuming you are on WIN32). If not, you can get it from CPAN.

HTH

Steve

 

[clueo8]

I did realize that it does actually rename the object/column, but since it sends back an error, my perl script errors also and thus haults the program. Is there anything I can do to make ODBC ignore this return value or this error all together so that my program continues?

 

[clueo8]

I have set scripts which call sp_rename to rename tables and columns. I cannot run my program using sp_rename but is there a similar rename function to call? That might also solve my problem.

When I used "my $sqlcode = $sth->execute();" to execute (rather then just $sth->execute() it wont crash at the script but continue. Since I had other things in the script relying on the sp_rename going through, it errors out.

 

[PaulTEG]

my $sqlcode = $sth->execute() or warn "$!";

might yield more info.
is sp_rename a standard stored proc, or one of your own?

--Paul

cigless ...

 

[clueo8]

sp_rename is a standard stored proc. And the code I tried worked just like "my $sqlcode = $sth->execute()" without the warn.

I'm going to look into the errors again and make sure its the renamed objects that setting them off.

Thanks for all of your help! If you find something, let me know.

 

[clueo8]

The error I'm getting from the sp_rename(s) is (Active Perl 5.8.0 with MS-SQL Server 2000):

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid cursor
state (SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1) at C:\update_db.pl line 586.

the line 586 is the "my $sqlcode = $sth->execute()

 

[clueo8]

I was able to accomplish what I wanted using the do() command rather then the execute(). do() doesn't return any values... and did not send back a return string/char.

 

[stevexff]

Gladto see you finally cracked it. I guess that's why some other languages have ExecuteNonQuery() and similar, to get around the same issues.

Thanks for the tip!