How to change value from DBI SQL request? 1

[jdm6457]

I am successful in connecting to a remote SQL database and printing information from tables. I need some assistance on how to change the value(s) of a column after I retrieve the data. For example, SQL may have the value of ‘0’ or ‘1’ for false or true. I need to print/write false/true in lieu of 0 or 1.

The following script works and prints the actual values from a SQL table. How would I change the ProductID from ‘1’ to another value like ‘True’ and then print? I have used the Northwind database as an example for the functionality that I need to know how to implement for a different DB. Thanks!

use DBI;

$data_source = "driver={SQL Server};Server={192.168.6.100};database={Northwind};UID={sa};PWD={password}";

$dbh = DBI->connect("dbi:ODBC:$data_source") or die "$DBI::errstr";

$sql = qq/
SELECT OrderID, ProductID, ProductName
FROM "Order Details Extended"
WHERE ProductName = 'Chai'
/;

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

while (@row = $sth->fetchrow_array()) {
print "$row[0] $row[1] $row[2]\n";
}
exit(0);

 

[audiopro]

UPDATE 'Order Details Extended' SET ProductID = 'true' WHERE ProductName = 'Chai'

but check the field's data type.
If it is 'text' it will work but if it is an 'int' value it will not accept text.

Keith

http://www.studiosoft.co.uk

 

[jdm6457]

Unfortunately, this will not work since we do not want to (and can not) change the data in the SQL table. Unless, UPDATE is being used differently in your example. For our purposes, SQL has to be read-only. We need to change the data after it is retrieved from the SQL table and write the change value(s) to a file. Is there a way to do this via Perl but not within the SQL query? I was able to use the CASE SQL command successfully in SQL Query Analyzer to change the results value, but CASE does not work when I run via a SQL request via DBI.

Any other suggestions or ideas? Thanks!

-john

 

[audiopro]

Do you just want to change the value of the retrieved data just for printing or are you saving the results to an actual file?
If it is just for printing

while (@row = $sth->fetchrow_array()) {
[red]
    if($row[1] eq 1){
        $row[1] = 'true';
    }else{
        $row[1] = 'false';
    }[/red]
    print "$row[0]   $row[1]   $row[2]\n";
}

would do it.
The same results could be written to a file if needed

Keith

http://www.studiosoft.co.uk

 

[jdm6457]

Sweet!!! :-D

I tried a variation of the IF statement and did not work. Mine was:

if($row[1] = '1'){
$row[1] = 'true';
}else{
$row[1] = 'false';
}

I'll have to read up on the nuances since I am still a Newbie at this Perl stuff. Thanks so muck!!!

-john

 

[audiopro]

I am sure everyone has fallen foul of that one but 'eq' and 'ne' etc. make the complex string handling possible. Perl is good - stick with it.

Keith

http://www.studiosoft.co.uk

 

[stevexff]

CASE does not work when I run via a SQL request via DBI.[/case]Are you sure - it doesn't seem very logical as the SQL should be handed off directly to the DBMS and not parsed by DBI - possibly query analyzer is just allowing you to be more lax about the syntax? Can you post an example?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]