January 12, 2005 at 9:55 am
I have a Perl script that needs to insert rows in several tables by executing SPs. The reads work fine. The writes do not. These are my symptoms:
If I take the 'exec' statement that the perl script generates and run it from Query Analyzer or osql, it works. The records get inserted, and the cursor has what the script would be looking for (chiefly the ID for the new row in one of the modified tables).
If I let the perlscript run, mostly correct data comes back, but not the ID I'm looking for.
The exec statement is passing one parameter, a string of 1000 characters or less. Does anybody have an idea of what the root of this problem could be? I am using ActivePerl 5.6.1 build 638, with Dave Roth's ODBC package (version 0.032). The problem is tied to use of transactions and SPs in some way: a previous version of the script (same Perl, same ODBC) that issued SQL directly worked fine.
January 13, 2005 at 3:45 am
Are you getting any error messages?
From the last bit, I get theimpression that you've just updated the script to use SPs instead of statemnets?
January 13, 2005 at 7:38 am
No. The script generates its own saying it didn't get the userid, but that's it.
New, amplifying data: if I step through the script in the debugger, it succeeds. I suspect a timing issue, which I think will be resolved by re-writing this section in C#, although I would rather not have to learn C# right now.
January 13, 2005 at 7:44 am
just a thought, but have you tried to check what data is actually being returned? Like a NULL, or something else that the script doesn't like?
January 13, 2005 at 7:46 am
It fetches and there's nothing in the row.
January 13, 2005 at 8:08 am
Can you post the snippet of code that's not working as it should (sanitized, of course, to protect your organization and your databases)? That would help a lot in trying to determine why it's not working.
K. Brian Kelley
@kbriankelley
January 13, 2005 at 8:12 am
but running it through query analyser returns a value?
January 13, 2005 at 8:17 am
Sure. Here goes:
THe perl:
my $sql = "EXEC dbo.addApplication "; # Don't take the space out. It matters.
foreach $key (sort keys %$ref ) {
# print "$key\t'$$ref{$key}'\n";
if ($key =~ /^(CON|GOV|USR|REG)/) {
unless ($key =~ /(COMM_EXCHANGE|(VOICE|FAX)_NUMBER|ACCESS)/) {
# print "$key=>$$ref{$key}\n";
$sql .= "'$$ref{$key}', ";
}
}
}
$sql .= "'$stoffe', '$majcoms', '$AccessLevelID'";
$sql =~ s/'NULL'/NULL/g;
$db->Run($sql);
my @ErrorList;
$db->Error( \@ErrorList );
foreach my $ErrorMessage ( @ErrorList ) {
print "SQLState: $ErrorMessage->{SQLState}\n";
print "ErrorNum: $ErrorMessage->{Number}\n";
print "Text: $ErrorMessage->{Text}\n\n";
}
$db->FetchRow() or warn "didn't get \$UserID";
($UserID, $ContactID, $GovtID, $ContractorID) = ($db->Data(UserID), $db->Data(ContactID), $db->Data(GovtID), $db->Data(ContractorID));
The stored procedures:
ALTER PROC dbo.addApplication
-- Boy, d(o|id) we have a lot of parameters @bucket nvarchar(1000)
@CON_ADDRESS nvarchar(255),
@CON_CITY nvarchar(50),
@CON_COUNTRY nvarchar(50),
@CON_EMAIL nvarchar(50),
@CON_EXPIRE_DATE nvarchar(20),
@CON_FAX_DSN_EXCHANGE nvarchar(20),
@CON_FAX_PREFIX nvarchar(20),
@CON_FIRST_NAME nvarchar(50),
@CON_LAST_NAME nvarchar(50),
@CON_MAJCOM nvarchar(80),
@CON_MIDDLE_INITIAL nvarchar(50),
@CON_NUMBER nvarchar(80),
@CON_ORG nvarchar(80),
@CON_PREFIX nvarchar(20),
@CON_STATE nvarchar(20),
@CON_SUFFIX nvarchar(20),
@CON_VOICE_DSN_EXCHANGE nvarchar(20),
@CON_VOICE_EXTENSION nvarchar(4),
@CON_VOICE_PREFIX nvarchar(20),
@CON_ZIP nvarchar(20),
@GOV_ADDRESS nvarchar(255),
@GOV_CITY nvarchar(50),
@GOV_COUNTRY nvarchar(50),
@GOV_EMAIL nvarchar(50),
@GOV_FAX_DSN_EXCHANGE nvarchar(20),
@GOV_FAX_PREFIX nvarchar(20),
@GOV_FIRST_NAME nvarchar(50),
@GOV_LAST_NAME nvarchar(50),
@GOV_MAJCOM nvarchar(80),
@GOV_MIDDLE_INITIAL nvarchar(50),
@GOV_ORG nvarchar(80),
@GOV_PREFIX nvarchar(20),
@GOV_STATE nvarchar(20),
@GOV_SUFFIX nvarchar(20),
@GOV_VOICE_DSN_EXCHANGE nvarchar(20),
@GOV_VOICE_EXTENSION nvarchar(4),
@GOV_VOICE_PREFIX nvarchar(20),
@GOV_ZIP nvarchar(20),
@REGISTRATION_DATE nvarchar(50),
@USR_ADDRESS nvarchar(255),
@USR_AIRCRAFT nvarchar(50),
@USR_CITY nvarchar(50),
@USR_COUNTRY nvarchar(50),
@USR_EMAIL nvarchar(50),
@USR_FAX_DSN_EXCHANGE nvarchar(20),
@USR_FAX_PREFIX nvarchar(20),
@USR_FIRST_NAME nvarchar(50),
@USR_LAST_NAME nvarchar(50),
@USR_MAJCOM nvarchar(80),
@USR_MIDDLE_INITIAL nvarchar(50),
@USR_NUMBER nvarchar(9),
@USR_ORG nvarchar(80),
@USR_PREFIX nvarchar(20),
@USR_STATE nvarchar(20),
@USR_SUFFIX nvarchar(20),
@USR_VOICE_EXTENSION nvarchar(4),
@USR_VOICE_DSN_EXCHANGE nvarchar(20),
@USR_VOICE_PREFIX nvarchar(20),
@USR_ZIP nvarchar(20),
@PRODUCT INT,
@MAJCOM INT,
@ACCESS INT
AS
-- and a lot of variables as well
declare @error INT
declare @uContactID INT
declare @gContactID INT
declare @cContactID INT
declare @u_PREFIX nvarchar(20)
declare @u_FIRST_NAME nvarchar(50)
declare @u_MIDDLE_INITIAL nvarchar(50)
declare @u_NUMBER nvarchar(9)
declare @u_LAST_NAME nvarchar(50)
declare @u_SUFFIX nvarchar(20)
declare @u_ORG nvarchar(80)
declare @u_MAJCOM nvarchar(80)
declare @u_VOICE_PREFIX nvarchar(30)
declare @u_VOICE_EXTENSION nvarchar(4)
declare @u_VOICE_DSN_EXCHANGE nvarchar(20)
declare @u_FAX_PREFIX nvarchar(30)
declare @u_FAX_DSN_EXCHANGE nvarchar(20)
declare @u_ADDRESS nvarchar(255)
declare @u_CITY nvarchar(50)
declare @u_STATE nvarchar(20)
declare @u_ZIP nvarchar(20)
declare @u_COUNTRY nvarchar(50)
declare @u_EMAIL nvarchar(80)
declare @g_PREFIX nvarchar(20)
declare @g_FIRST_NAME nvarchar(50)
declare @g_MIDDLE_INITIAL nvarchar(50)
declare @g_LAST_NAME nvarchar(50)
declare @g_SUFFIX nvarchar(20)
declare @g_ORG nvarchar(80)
declare @g_MAJCOM nvarchar(80)
declare @g_VOICE_PREFIX nvarchar(30)
declare @g_VOICE_EXTENSION nvarchar(4)
declare @g_VOICE_DSN_EXCHANGE nvarchar(20)
declare @g_FAX_PREFIX nvarchar(30)
declare @g_FAX_DSN_EXCHANGE nvarchar(20)
declare @g_ADDRESS nvarchar(255)
declare @g_CITY nvarchar(50)
declare @g_STATE nvarchar(20)
declare @g_ZIP nvarchar(20)
declare @g_COUNTRY nvarchar(50)
declare @g_EMAIL nvarchar(80)
declare @c_PREFIX nvarchar(20)
declare @c_FIRST_NAME nvarchar(50)
declare @c_MIDDLE_INITIAL nvarchar(50)
declare @c_NUMBER nvarchar(80)
declare @c_EXPIRE_DATE nvarchar(20)
declare @c_LAST_NAME nvarchar(50)
declare @c_SUFFIX nvarchar(20)
declare @c_ORG nvarchar(80)
declare @c_MAJCOM nvarchar(80)
declare @c_VOICE_PREFIX nvarchar(30)
declare @c_VOICE_EXTENSION nvarchar(4)
declare @c_VOICE_DSN_EXCHANGE nvarchar(20)
declare @c_FAX_PREFIX nvarchar(30)
declare @c_FAX_DSN_EXCHANGE nvarchar(20)
declare @c_ADDRESS nvarchar(255)
declare @c_CITY nvarchar(50)
declare @c_STATE nvarchar(20)
declare @c_ZIP nvarchar(20)
declare @c_COUNTRY nvarchar(50)
declare @c_EMAIL nvarchar(80)
declare @User_Name nvarchar(25)
declare @Application_date nvarchar(50)
declare @u_AIRCRAFT nvarchar(50)
declare @userid INT
declare @Products INT
declare @Majcoms INT
declare @AccessLevel INT
--beats me why I can't use the parameters directly, but it seems to be how it works
set @u_PREFIX = @USR_PREFIX
set @u_FIRST_NAME = @USR_FIRST_NAME
set @u_MIDDLE_INITIAL = @USR_MIDDLE_INITIAL
set @u_NUMBER = @USR_NUMBER
set @u_LAST_NAME = @USR_LAST_NAME
set @u_SUFFIX = @USR_SUFFIX
set @u_ORG = @USR_ORG
set @u_MAJCOM = @USR_MAJCOM
set @u_VOICE_PREFIX = @USR_VOICE_PREFIX
set @u_VOICE_EXTENSION = @USR_VOICE_EXTENSION
set @u_VOICE_DSN_EXCHANGE = @USR_VOICE_DSN_EXCHANGE
set @u_FAX_PREFIX = @USR_FAX_PREFIX
set @u_FAX_DSN_EXCHANGE = @USR_FAX_DSN_EXCHANGE
set @u_ADDRESS = @USR_ADDRESS
set @u_CITY = @USR_CITY
set @u_STATE = @USR_STATE
set @u_ZIP = @USR_ZIP
set @u_COUNTRY = @USR_COUNTRY
set @u_EMAIL = @USR_EMAIL
set @g_PREFIX = @GOV_PREFIX
set @g_FIRST_NAME = @GOV_FIRST_NAME
set @g_MIDDLE_INITIAL = @GOV_MIDDLE_INITIAL
set @g_LAST_NAME = @GOV_LAST_NAME
set @g_SUFFIX = @GOV_SUFFIX
set @g_ORG = @GOV_ORG
set @g_MAJCOM = @GOV_MAJCOM
set @g_VOICE_PREFIX = @GOV_VOICE_PREFIX
set @g_VOICE_EXTENSION = @GOV_VOICE_EXTENSION
set @g_VOICE_DSN_EXCHANGE = @GOV_VOICE_DSN_EXCHANGE
set @g_FAX_PREFIX = @GOV_FAX_PREFIX
set @g_FAX_DSN_EXCHANGE = @GOV_FAX_DSN_EXCHANGE
set @g_ADDRESS = @GOV_ADDRESS
set @g_CITY = @GOV_CITY
set @g_STATE = @GOV_STATE
set @g_ZIP = @GOV_ZIP
set @g_COUNTRY = @GOV_COUNTRY
set @g_EMAIL = @GOV_EMAIL
set @c_PREFIX = @CON_PREFIX
set @c_FIRST_NAME = @CON_FIRST_NAME
set @c_MIDDLE_INITIAL = @CON_MIDDLE_INITIAL
set @c_NUMBER = @CON_NUMBER
set @c_EXPIRE_DATE = @CON_EXPIRE_DATE
set @c_LAST_NAME = @CON_LAST_NAME
set @c_SUFFIX = @CON_SUFFIX
set @c_ORG = @CON_ORG
set @c_MAJCOM = @CON_MAJCOM
set @c_VOICE_PREFIX = @CON_VOICE_PREFIX
set @c_VOICE_EXTENSION = @CON_VOICE_EXTENSION
set @c_VOICE_DSN_EXCHANGE = @CON_VOICE_DSN_EXCHANGE
set @c_FAX_PREFIX = @CON_FAX_PREFIX
set @c_FAX_DSN_EXCHANGE = @CON_FAX_DSN_EXCHANGE
set @c_ADDRESS = @CON_ADDRESS
set @c_CITY = @CON_CITY
set @c_STATE = @CON_STATE
set @c_ZIP = @CON_ZIP
set @c_COUNTRY = @CON_COUNTRY
set @c_EMAIL = @CON_EMAIL
set @Application_date = @REGISTRATION_DATE
set @u_AIRCRAFT = @USR_AIRCRAFT
set @Products = @PRODUCT
set @Majcoms = @MAJCOM
set @AccessLevel = @ACCESS
BEGIN TRANSACTION NEWAPPLICATION
-- Start with the adding information on the contacts
exec addContact @u_PREFIX, @u_FIRST_NAME, @u_MIDDLE_INITIAL, @u_LAST_NAME, @u_SUFFIX, @u_ORG, @u_MAJCOM, @u_VOICE_PREFIX, @u_VOICE_EXTENSION, @u_VOICE_DSN_EXCHANGE, @u_FAX_PREFIX, @u_FAX_DSN_EXCHANGE, @u_ADDRESS, @u_CITY, @u_STATE, @u_ZIP, @u_COUNTRY, @u_EMAIL, @uContactID OUTPUT
exec addContact @g_PREFIX, @g_FIRST_NAME, @g_MIDDLE_INITIAL, @g_LAST_NAME, @g_SUFFIX, @g_ORG, @g_MAJCOM, @g_VOICE_PREFIX, @g_VOICE_EXTENSION, @g_VOICE_DSN_EXCHANGE, @g_FAX_PREFIX, @g_FAX_DSN_EXCHANGE, @g_ADDRESS, @g_CITY, @g_STATE, @g_ZIP, @g_COUNTRY, @g_EMAIL, @gContactID OUTPUT
exec addContact @c_PREFIX, @c_FIRST_NAME, @c_MIDDLE_INITIAL, @c_LAST_NAME, @c_SUFFIX, @c_ORG, @c_MAJCOM, @c_VOICE_PREFIX, @c_VOICE_EXTENSION, @c_VOICE_DSN_EXCHANGE, @c_FAX_PREFIX, @c_FAX_DSN_EXCHANGE, @c_ADDRESS, @c_CITY, @c_STATE, @c_ZIP, @c_COUNTRY, @c_EMAIL, @cContactID OUTPUT
-- Now that we have ContactID info on the contracts, we can tie the tblUsers record to the right people
INSERT INTO dbo.tblUsers (ContactID, GovtPocContactID, ContractPocContactID, SSN, Aircraft, Majcom, Organization, ContractNumber, ContractDate, ApplicationDate)
VALUES (@uContactID, @gContactID, @cContactID, @u_NUMBER, @u_AIRCRAFT, @u_MAJCOM, @u_ORG, @c_NUMBER, @c_EXPIRE_DATE, @Application_date)
-- Get the UserID for permissions, and to null out ContractDate if it should be null, not 1900-01-01
SET @userid = IDENT_CURRENT('dbo.tblUsers')
IF (@c_EXPIRE_DATE = '')
BEGIN
UPDATE tblUsers SET ContractDate = NULL WHERE UserID = @userid
END
-- Variables for the product and majcom flags to be "or'd" against
DECLARE @PID INT
DECLARE @MID INT
DECLARE @MAJCON INT
set @MAJCON = @MAJCOMS
-- Get the list of product IDs in descending order for convenient subtraction and comparison
DECLARE products_cursor CURSOR FOR
SELECT ProductID FROM tblProductDivisions ORDER BY ProductID DESC
OPEN products_cursor
FETCH NEXT FROM products_cursor
INTO @PID
declare @tblaccess TABLE (UserID INT, MID INT, AccessLevel INT, PID INT)
WHILE @@FETCH_STATUS = 0
BEGIN
IF ((@Products - POWER(2, @PID)) >= 0)
BEGIN
-- Do the same for the majcoms
SET @Majcoms = @MAJCON
DECLARE majcoms_cursor CURSOR FOR
SELECT MajcomID FROM tblMajcom ORDER BY MajcomID DESC
OPEN majcoms_cursor
FETCH NEXT FROM majcoms_cursor
INTO @MID
WHILE @@FETCH_STATUS = 0
BEGIN
IF ((@Majcoms - POWER(2, @MID)) >= 0)
BEGIN
exec addaccesses @userid, @MID, @AccessLevel, @PID
-- Sets 3.3 for users requesting SOCOM and PFPS
if (@MID = 3 and @PID = 6)
BEGIN
exec addaccesses @userid, @MID, @accesslevel, 11
end
-- Remember to decrement so that the next lower power of two gets the right test
SET @Majcoms = @Majcoms - POWER(2, @MID)
END
FETCH NEXT FROM majcoms_cursor
INTO @MID
END
SET @Products = @Products - POWER(2, @PID)
close majcoms_cursor
END
FETCH NEXT FROM products_cursor
INTO @PID
END
DECLARE access_cursor CURSOR FOR
SELECT DISTINCT * FROM @tblaccess
OPEN access_cursor
FETCH NEXT FROM access_cursor
INTO @userid, @MID, @AccessLevel, @PID
while @@fetch_status = 0
begin
exec addAccesses @userid, @MID, @AccessLevel, @PID
FETCH NEXT FROM access_cursor
INTO @userid, @MID, @AccessLevel, @PID
end
close access_cursor
close products_cursor
declare @tblLastInsert TABLE (UserID INT, ContactID INT, GovtPocContactID INT, ContractPocContactID INT)
INSERT INTO @tblLastInsert (UserID, ContactID, GovtPocContactID, ContractPocContactID)
VALUES (@UserID, @uContactID, @gContactID, @cContactID)
SELECT * FROM @tblLastInsert where userid = userid
set @error = @@error
IF (@error 0)
BEGIN
/*raiserror (@message,16,1)*/
ROLLBACK TRANSACTION NEWAPPLICATION
RETURN
END
COMMIT TRANSACTION NEWAPPLICATION
ALTER PROC dbo.addContact
@Prefix as nvarchar(20),
@FirstName as nvarchar(50),
@MiddleInitial as nvarchar(50),
@LastName as nvarchar(50),
@Suffix as nvarchar(20),
@Organization as nvarchar(80),
@Majcom as nvarchar(80),
@voice as nvarchar(30),
@VoiceExtension as nvarchar(4),
@VoiceDsnExchange as nvarchar(20),
@Fax as nvarchar(30),
@FaxDsnExchange as nvarchar(20),
@Street as nvarchar(255),
@City as nvarchar(50),
@State as nvarchar(20),
@PostalCode as nvarchar(20),
@Country as nvarchar(50),
@Email as nvarchar(80),
@ContactID INT OUTPUT
AS
declare @error INT
IF (@FirstName = @Suffix)
RETURN
ELSE
BEGIN
BEGIN TRANSACTION NEWCONTACT
INSERT INTO dbo.tblContacts (City, Country, Email, Fax, FaxDsnExchange, FirstName, LastName, MiddleInitial, PostalCode, Prefix, State, Street, Suffix, Voice, VoiceExtension, VoiceDsnExchange)
VALUES (@City, @Country, @Email, @Fax, @FaxDsnExchange, @FirstName, @LastName, @MiddleInitial, @PostalCode, @Prefix, @State, @Street, @Suffix, @voice, @VoiceExtension, @VoiceDsnExchange)
SET @ContactID = IDENT_CURRENT('dbo.tblContacts')
--PRINT @ID
set @error = @@error
IF @error 0
BEGIN
ROLLBACK TRANSACTION NEWCONTACT
RETURN
END
COMMIT TRANSACTION NEWCONTACT
END
ALTER PROC addAccesses
@userid INT,
@MajcomID INT,
@AccessLevelID INT,
@ProductID INT
AS
declare @testaccess as int
SELECT @testaccess = Userid FROM tbluseraccesslevel where Userid = @userid
if (@testaccess is null)
begin
INSERT INTO tblUserAccessLevel (UserID, AccessLevelID) VALUES (@UserID, @AccessLevelID)
END
INSERT INTO tblUserContentArea (UserID, MajcomID, ProductID) VALUES (@UserID, @MajcomID, @ProductID)
SELECT * FROM tblUserAccessLevel l INNER JOIN tblUserContentArea c ON ((l.UserID = @userid) AND (c.UserID = @userid))
SELECT @testaccess = Userid FROM tbluseraccesslevel where Userid = @userid
January 13, 2005 at 8:18 am
Yes, and also inserts properly.
January 13, 2005 at 10:15 am
ah! you're looking for the userid value?
Its trying to give you back two sets of data at the end
#####
SELECT * FROM tblUserAccessLevel l INNER JOIN tblUserContentArea c ON ((l.UserID = @userid) AND (c.UserID = @userid))
SELECT @testaccess = Userid FROM tbluseraccesslevel where Userid = @userid
####
you need
set nocount on
at the top of the proc
so that the proc only returns the last value to the script
January 13, 2005 at 10:41 am
Thanks, I'll try it. It leaves a question hanging, though: the tables are only updated when the SP is run in Query Analyzer, or when the perl is stepped through the debugger; if the perl is run normally, the tables *are not changed*. Why is that?
January 13, 2005 at 10:57 am
hum. my PERL is not too strong, but are you confirming what is actually being sent to the proc? as you've got trans/commit lines in there, it might actually be causing an error that doesn't get reported back, as you've no way to get the status back.
What you could do is turn the last select into a gather of the error codes as well as the desired output, and thus get some feedback?
January 13, 2005 at 11:01 am
The perl generates the parameters for the SP (which I can see because $db->Run($sql) dumps it to STDOUT) and when those parameters are fed to the script in QA, it works fine. It also works fine if the the script is single-stepped by the debugger.
January 13, 2005 at 11:02 am
and the error codes?
January 13, 2005 at 2:49 pm
Curious. No error code, *and it got a userid*, but the tables have *not* been written to.
Perhaps the last select needs to be put after the transaction commits, so that it can fail properly.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply