File Locking - SQL ODBC vs FLOCK

[1DMF]

Hello peeps,

Ok I've managed to connect to a remote SQL server with WIN32::ODBC and a FILEDSN - sorted!

Only because I'm new I could do with advice on file/table/record locking under my new found DB environment.

I am re-developing a site that currently uses flat text files (PIPE Dilemited) and would use the current code to open and lock the file while processing the foreach record rows...

$catal_file = "$txt/catal.txt";
open(CATAL, "+<$catal_file");
flock(CATAL, 2);
binmode(CATAL);
@lines = <CATAL>;
foreach $line (@lines){
$line =~ s/\s+$//g;
($n7,$n6,$n5,$n4,$n3,$n2,$n1) = split(/\|/, $line);
if($n1 == 9){$n1 = 0;&add1;}
else{$n1++;}
}
$line = "$n7|$n6|$n5|$n4|$n3|$n2|$n1";
truncate(CATAL, length($line));
seek(CATAL, 0, 0);
print CATAL $line;
close(CATAL);
==============================================
Works great, file gets locked as each call to the routine is made.

What is the method to acheive the same thing via ODBC does
opening the dbase connection do the same job in as follows

# Open DB Connection
$db = new Win32::ODBC("FILEDSN=$DSN;UID=$UID;PWD=$PWD;") || die "Error connecting: " . Win32::ODBC::Error();

# Display SQL Data
if( ! $db->Sql( "SELECT * FROM [TABLE NAME]" ) ) {
while( $db->FetchRow() ) {
%Data = $db->DataHash();
@key_entries = keys(%Data);
$Row++;
print "\n$Row)";
foreach $key ( keys( %Data ) )
{
print "\t'$key' = '$Data{$key}'\n";
}
}
}
else
{
print "\nUnable to execute query: " . $db->Error() . "\n";
}

# Close DB Connection
$db->Close();
===========================================================
is the dbase released with the ->close(); or do i need to set some type of locking else where.

Hope you can help.

Regards 1DMF.

 

[johnv20]

Not sure exactly what you mean here

The table will be locked using the sql statement above
(generally this isn't a good idea as this prevents writes to the data)

The database connection is released using the Close command at the end of the script

 

[1DMF]

Hello John,

I'm just trying to understand if there is anything I need to do program wise to lock the data.

I'm converting from flat-files, and you issue the FLOCK command, I take it what your saying is SQL does that bit for me ?

 

[johnv20]

Yes sql will do that bit for you by default while accessing the tables

To ensure that the table is locked until the end of the transaction use the holdlock function - generally this should never be used unless a looping delete job is running on a table which receives dynamic data

select * from table1 holdlock

 

[johnv20]

sorry my mistake - use TABLOCKX instead on holdlock

 

[1DMF]

does the X stand for table name or uis that the syntax in full

 

[johnv20]

TABLOCKX is the syntax i.e.

select colA from Table1 TABLOCKX

 

[1DMF]

Nice one John - appreciated.