Equivalent Perl DBI statement 1

[waiterm]

Hiya guys, I'm not sure if this is a Perl or SQL question but I thought someone should be able to point me in the right direction.

I have a table in my database (tbl_Meeting) with three fields; MeetingUID, ME_Course, ME_Date.

Basically all I'm trying to do is insert a record into an SQL database using the standard query, but I want to return the primary key MeetingUID without having to requery the database. I have an equivalent VB version (see below) but how do I return the MeetingUID into a variable in my Perl script.

strSQL = "Set Nocount on INSERT INTO tbl_Meeting (ME_Course,ME_Date) VALUES (?,?) select IdentityInsert=@@MeetingUID set nocount off"
Set objRS = objConn.Execute(strSQL)
Response.Write objRS("IdentityInsert")

My code so far:

$dbh = &sqlConnect;
my $sth = $dbh->prepare(q{Set Nocount on INSERT INTO tbl_Meeting (ME_Course,ME_Date) VALUES (?,?) SELECT MeetingUID=@@MeetingUID set nocount off}) or die $dbh->errstr;
$sth->execute($ME_Course,$ME_Date) or die $dbh->errstr;&sqlDisconnect($dbh);

can I use fetchrow_array in order to retireve the MeetingUID:

@row_ary  = $sth->fetchrow_array;
$MeetingUID = @row_ary[0];

Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[MikeLacey]

Hello Rob,

I don't think so no, though I don't have SQL Server here to test that.

IIUC you can only call any of the fetch functions after executing a SELECT statement.

I think you're going to have to go for two queries.

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[waiterm]

Managed to figure it out just as you sent the reply:

$sth1 = $dbh->prepare(q{SET NOCOUNT ON INSERT INTO tbl_Meeting (ME_Course,ME_Date) VALUES (?,?) SELECT MeetingUID=@@identity SET NOCOUNT OFF}) or die $dbh->errstr;
$sth1->execute($_[0],$d."/".$m."/".$year) or die $dbh->errstr;
@row_ary  = $sth1->fetchrow_array;
$RA_MeetingID = $row_ary[0];
[code]
@@identity is a system variable, which you have to assign to your tables primary key within the SQL statement, although this has created another problem now...happy days!!

Rob Waite
[URL unfurl="true"]http://www.hostpipe.co.uk?src=TT[/URL]

 

[MikeLacey]

Very cool... Thanks Rob, I wonder if that trick works with all DBDs?

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[waiterm]

Well it's now throwing up this error when I try and execute a nested statement:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at C:\tmp\blah\blah.pl line 135.

Think I got the code tags right that time!

I believe it has something to do with the connection string, currently

my $conn_string = "DRIVER={SQL Server};SERVER=$data_source;DATABASE=$database;UID=$user_id;PWD=$password";
	$dbh = DBI->connect( "DBI:ODBC:$conn_string" ) or die $DBI::errstr;

Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[MikeLacey]

Hmmmm I suspect nested statements not properly supported... "busy with result for another" etc...

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[waiterm]

I guess the simplest option is to create a couple of connections, i.e. one to handle statements inside the loop and another for those statements outside of the loop if that makes sense. I'm guessing it's not possible to fork an SQL connection is it?

Rob Waite

http://www.hostpipe.co.uk?src=TT

 

[MikeLacey]

I never tried.... You mean sharing a $dbh between two threads? Might be fun to try.

Mike

To err is human,
but to really foul things up -
you require a man Mike.

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884