[ic] Accessing $Db or $Sql in Jobs?

[Ethan Rowe]

Jeff Fearn wrote:

>Ok, after _much_ confusion on my part, I have finally figured out how
>to get a ref to the DB ... or maybe just the table, either way I can
>run select statements via $db->query.
>
>However when I try to update the table I get "Attempt to write
>read-only table orderline" errors.
>
>  
>
 From <VENDROOT>/lib/Vend/Table/Common.pm:
        if($stmt->command() ne 'SELECT') {
                if(defined $s and $s->[$CONFIG]{Read_only}) {
                        $s->log_error(
                                        "Attempt to write read-only 
table %s",
                                        $s->[$CONFIG]{name},
                        );
                        return undef;
                }
                $update = $stmt->command();
                @vals = $stmt->row_values();
#::logDebug("row_values returned=" . ::uneval(\@vals));
        }

Your table is configured as read-only ($s->[$CONFIG]{Read_only}).  This 
is the thing to sort out next.

I'm not sure how best to proceed... I think you need to use [tag] to set 
the particular table in write-mode.  Do it before any other access to 
that table occurs.  Within the usertag, you could probably do:
$Tag->tag('flag', 'write' { table => 'orderline' });

I'm not sure if that's the exact syntax.  If your job is simply calling 
the usertag that you're designing, you can also call the [tag] tag 
before you call the usertag.  Thus, your job file would be
[tag op=flag arg=write table=orderline]
[your-usertag]

I haven't used the read/write flags much so don't be surprised if this 
doesn't solve the problem immediately... or if it's not even related.

>Here is part of the code I am using:
>
>my $tablename = "orderline";
>$Tag->perl({tables => $tablename});
>
>  
>
I believe this call to $Tag->perl is not necessary.  Have you tried 
taking it out?  I don't anticipate a fix to your problem, but it will 
increase the efficiency of your code.  Calling $Tag->perl adds a bunch 
of overhead, even if you aren't giving it any code to eval.

>my $db = database_exists_ref( $tablename ) or die "Couldn't get handle
>to table  $tablename'!";
>
>$db = $db->ref();
>
># Works fine
>$sql = "select code from $tablename where order_number = '$order_num'";
>my @result = $db->query({type => 'row_count', sql => $sql});
>my $num_lines = $result[0];
>
>
>my $code = 'TEST0061-1';
>$sql = "update $tablename set status = 'shipped' where code = '$code'";
>
># Both of these fail!
>$db->set_field($temp, 'status', 'shipped');
>$db->query({sql => $sql}) || die("Couldn't update orderline: $_");
>
>$_ is NULL in the above die, how do I get the error message?
>
>  
>
I believe NULL will come through perhaps as undef...

>If the tag is setup as a global tag, as suggested befor, why does it
>have access restrictions?
>
>  
>
I think it's safe to say it's a global tag; otherwise, the system would 
choke on the code (specifically, the call to database_exist_ref).

>If it's not a global tag why does $Tag->perl... have no effect?
>  
>
>Where are you supposed to locate local usertags? i.e. which directory
>
>  
>
A nice way to do it is, for a particular catalog:
    - put catalog-specific usertags in <CATALOGROOT>/usertag with a 
'.tag' extension
    - in <CATALOGROOT>/catalog.cfg, add the line: include usertag/*.tag

>Where are database_exists_ref and the like documented? I have searched
>the website and RTFM site and have only found references in the change
>logs.
>
>  
>
I've never seen docs for these kinds of global utility functions; that 
doesn't mean they don't exist, but I'm skeptical.

Basically, when you're writing global code, not wrapped in the confines 
of the Safe module, I find the best way to learn how to do stuff is just 
look at the Interchange core modules and see how it does stuff.  If you 
want to figure out how to access database objects, find an Interchange 
core tag that works with database objects (i.e. the data tag or export 
tag), find the routine it executes, and see how it's done there.

That's probably not the most encouraging answer.  Anyway, that's what I 
do, for what it's worth.

>Thanks for you help and patience :)
>  
>
Good luck!

    - Ethan

-- 
Ethan Rowe
End Point Corporation
ethan at endpoint.com