Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Table and Functions

Quote Reply
Table and Functions
So I discovered late last night that instead of fiddling with the existing "code" (and thus breaking future upgrades) that I have within my system I can just create my own and call it from the templates. For instance In the Template I do this:

Code:

<%set Carrier = Dbsql::custom::GetCarrier%>


and in the file called "custom.pm" (which is in the Dbsql directory) I create a file with this inside it:

Code:
sub GetCarrier {
#--------------------------------------------------------------------
# Lookup data in another database.
#
my $self = shift;
my $id = 'Wilson Lance';
my $table = $self->{sql}->table('Patients');
my $carrier = $table->get ('$id', 'HASH', ['Carrier']);
return $carrier;
}


Which should work as long as I can get the function to work. Here is where I need the help. I have an SQL table called "Patients" (which is outside of the table I am currently working on in my template, by the way) and I want to return the value within the column "Carrier". Sounds simple but I am doing something wrong here, what is incorrect? I get the error: "Can't call method "table" on an undefined value ..."

Another part of the puzzle, but less important, is how do I transfer a Variable inside the template back to this script? In this example, I want to transfer the variable "id" from the template (which I hard coded above for testing purposes)?

I need to get this project done and I'm ALMOST there, any help would be appreciated.
Quote Reply
Re: [LanceWilson2] Table and Functions In reply to
Hi,

Have a look at the script below:

sub GetCarrier {
my $id = shift;
my $tags = GT::Template->tags;
my $info = $tags->{home}->{sql}->table('Patients')->get($id);
return $info->{Carrier};
}

Now you can call it from the template by <%Dbsql::custom::GetCarrier('Wilson')%>. Ensure that 'id' is primary key field.

By the way, you can create a global template instead of adding a new pm file.

TheStone.

B.

Last edited by:

TheStone: Jan 6, 2003, 10:31 AM
Quote Reply
Re: [TheStone] Table and Functions In reply to
Ok... Now it is making a bit more sense. I understand the $info part now... makes sense. What does the "my $tags = GT::Template->tags;" do? I've seen that several places in the script.

Now when I do the script, as told, I get nothing back because It probably doesn't assign the return data to anything: <%Dbsql::custom::GetCarrier('000000')%>. So I modified it to be <%set Carrier = Dbsql::custom::GetCarrier('000000')%> in the template which should lookup the Primary Key (000000) in Table 'Patients' and return the data in the column called Carrier. (by the way, the database data DOES exist... I checked three times) But I just get a "blank" variable for 'Carrier'. I even tried getting the entire $info HASH but it still returns nothing. How do I debug whether there are any errors and or check to see if the database is indeed being accessed?

Original script:

Code:


sub GetCarrier {
#--------------------------------------------------------------------
# Lookup data in another database.
# $id = PK value
my $id = shift;
my $tags = GT::Template->tags;
my $info = $tags->{home}->{sql}->table('Patients')->get ('$id');
return $info->{'Carrier'};
}
Quote Reply
Re: [LanceWilson2] Table and Functions In reply to
You should correct the code like:

my $info = $tags->{home}->{sql}->table('Patients')->get ($id);

instead of:

my $info = $tags->{home}->{sql}->table('Patients')->get ('$id');


TheStone

B.
Quote Reply
Re: [TheStone] Table and Functions In reply to
WOW! It's amazing what a pair of single quotes will do. That was the problem. Crazy In looking back you did not have that in your original code either. I must have added them while I was trying different settings. My fault.

On a side note, dealing with the same script, I would effictively like to insert a Variable into that function call on the template: <%set Carrier = Dbsql::custom::GetCarrier('<%ID%>')%>

Obviously this does not work. What is the best way to insert a variable, rather than a fixed number, to call that script?

Also, is there any difference between using a Global versus using an outside script file? I actually prefer the script file over the global variable just because I'm a programmer and it is easier to modify and control a file (mainly with upgrades I can just drop and replace a file to test on a non production server). But if there is an advantage to using the Globals I would be happy to.
Quote Reply
Re: [LanceWilson2] Table and Functions In reply to
Just change the code a little bit:

...
my $tags = GT::Template->tags;
my $id = $tags->{ID};
....

TheStone.

B.
Quote Reply
Re: [TheStone] Table and Functions In reply to
Ok. That worked as well. So I can reference a variable just by using the form "$tags->{ID}" I'm learning very slowly but hopefully with enough learning I can contribute something back to the community. Thanks for the help... You ROCK (pun intended)