ab5ni
Posts: 177
|
| Posted: 04/30/2007, 2:44 PM |
|
Folks:
We seem to be having a problem when using FPDF and MySQL temporary tables
within CCS. For some unknown reason to us, the temporary tables are being
populated, can be viewed when using CCS Report Builder, yet when we use the
same exact query to display the data in pdf format, no data appears in the pdf file.
Example query:
$rpt_query = "CREATE TEMPORARY TABLE IF NOT EXISTS CDReports \n" .
"select field1, field2, \n" ...
The above code is in the BeforeBuildSelect event and works fine when displaying
CCS reports; however, when we wish to use the same query using FPDF,
nothing shows up. We've been scratching or heads on this one, and we're so
brain-drained on this that we feel like we've barely survived an everything-gone-wrong
Vulcan mind meld or something. 
TIA!
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
 |
|
mamboBROWN
Posts: 1713
|
| Posted: 04/30/2007, 3:50 PM |
|
ab5ni
I don't know if this will help but try this link: http://www.devwebpro.com/devwebpro-39-20010817Temporary...With-MySQL.html
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/01/2007, 5:58 AM |
|
Thanks, mambo. I'll give it a shot and see what gives.
Best Regards,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/02/2007, 10:46 AM |
|
I gave that link a try, mambo, and, unfortunately, it wasn't
what I was looking for. OTOH, it was some good info.
Anyway, I guess the original question still stands.
Off the top of my head (and without delving into the code),
I'm guessing that the problem I'm having has something
to do with multi-threaded asynchronous execution. In other
words, when I make the php calls to FPDF, thinking that
they are single-threaded and synchronous, they're actually
asynchronous, returning immediately, and my connection
to the temporary table is being closed (via $db->close()) before
FPDF gets a chance to work its magic. That is, once the
$db->close() is executed, the temporary table no longer
exists. Don't know if I'm right or not, but I'm staring to wonder
if Apache's 2.X release is the culprit. (Shrug.) If I'm not
mistaken, the latest, greatest version of Apache is multi-threaded
in nature. Guess I'll have to take a look and see.
Anywho, the problem still stands, and any help in this
area would be much appreciated!
Regards,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 05/02/2007, 4:45 PM |
|
ab5ni
Are you able to run the SQL in MySQL without any errors? BTW what version of MySQL are you using??
|
|
|
|
Damian Hupfeld
|
| Posted: 05/03/2007, 4:23 AM |
|
Have you tried using PERSISTENT connections?
"ab5ni" <ab5ni@forum.codecharge> wrote in message
news:54638ce71ec661@news.codecharge.com...
>I gave that link a try, mambo, and, unfortunately, it wasn't
> what I was looking for. OTOH, it was some good info.
> Anyway, I guess the original question still stands.
>
> Off the top of my head (and without delving into the code),
> I'm guessing that the problem I'm having has something
> to do with multi-threaded asynchronous execution. In other
> words, when I make the php calls to FPDF, thinking that
> they are single-threaded and synchronous, they're actually
> asynchronous, returning immediately, and my connection
> to the temporary table is being closed (via $db->close()) before
> FPDF gets a chance to work its magic. That is, once the
> $db->close() is executed, the temporary table no longer
> exists. Don't know if I'm right or not, but I'm staring to wonder
> if Apache's 2.X release is the culprit. (Shrug.) If I'm not
> mistaken, the latest, greatest version of Apache is multi-threaded
> in nature. Guess I'll have to take a look and see.
>
> Anywho, the problem still stands, and any help in this
> area would be much appreciated!
>
> Regards,
>
> Randall
>
>
> _________________
> --
> Randall Jouett
> Amateur Radio: AB5NI
> I eat spaghetti code out of a bit-bucket while sitting at a hash table!
> Friend
> asked if I used salt, and I told him I
> use it when I eat encrypted spaghetti code.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Wkempees
|
| Posted: 05/03/2007, 4:40 AM |
|
So if I understand right, you build the temp table in the Before Show of
the CCS Report, hat works ok. Then you use FPDF(?) to create a PDF from
the CCS Report, using the exact same query.
So you are executing the query again resulting in a new temp table, or
are you trying the temp table created in the CCS report?
Has anyone considered why the temp table is called a temporary(!) table.
Walter
|
|
|
|
Wkempees
|
| Posted: 05/03/2007, 4:45 AM |
|
"A temporary table will automatically be deleted if a connection dies
and the name is per connection. This means that two different
connections can both use the same temporary table name without
conflicting with each other or with an existing table of the same name.
(The existing table is hidden until the temporary table is deleted.)"
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/03/2007, 6:28 AM |
|
Hi again, mambo, and thanks for the reply.
Quote mamboBROWN:
ab5ni
Are you able to run the SQL in MySQL without any errors? BTW what version of MySQL are you using??
Yes. The query works fine in MySQL. I'm thinking It might have to do with code placement,
although I've tried moving the code to different events and whatnot. (Shrug.)
BTW, here's what a "mysql --version" outputs:
mysql Ver 14.12 Distrib 5.0.27, using readline 5.0
Regards,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/03/2007, 6:55 AM |
|
Hi Damian, and thanks for the reply.
Quote Damian Hupfeld:
Have you tried using PERSISTENT connections?
Yep. We all have it set in our project settings. (3 folks here hacking CCS
besides me.) BTW, here's a snippet of the code we are hacking on that
doesn't seem to work...
(SQL Query to bulid temp table goes here)
$pdf = new PDF("L","pt","A3");
$pdf->SetFont("Arial","",11.5);
$pdf->AliasNbPages();
$pdf->connect("192.168.0.2","db_user","password","dbase1"); //actually a mysql_connect() call.
$attr=array("titleFontSize"=>18,"titleText"=>"Reports");
$pdf->mysql_report("SELECT * FROM temp_table",false,$attr); //func that builds the PDF report.
$db2->close();
$db1->close();
$db->close();
By the time $pdf->mysql_report() is called...no temp table...
Regards,
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
Wkempees
|
| Posted: 05/03/2007, 7:13 AM |
|
Quote :
By the time....
You are mixing database connections to be looked upon as sessions.
You are building the temp table in one session (you just put SQL SQL
query to build temp goes here)
and then you connect to the database
and then SELECT from
Temporary tables only exist in and while in the same connect !
Resquence your actions or, instead of building a temporary table
create a 'normal' table with a specific name, fill it, run the report
and delete it, or truncate it and reuse it constantly.
Name it after the report, if multi-user is anticipated add some identifier.
ps. you respond to all posts but have you read mine?
Walter
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/03/2007, 7:32 AM |
|
Hi Walter. Thanks for the reply, dewd.
Quote Walter:
So if I understand right, you build the temp table in the Before Show of
the CCS Report, that works ok. Then you use FPDF(?) to create a PDF from
the CCS Report, using the exact same query.
Yep. Using pretty much the same query as I use to build the report.
Same fields show up in the report, etc. Only problem is that the
report displays correctly, and the PDF doesn't even show up.
BTW, it works perfectly if we use a real life, existing table.
Quote :
So you are executing the query again resulting in a new temp table, or
are you trying the temp table created in the CCS report?
Basically, a query is executed that builds a temporary table which is
used to display results in a CCS report. I have a "print" button on
the page that allows you to print this particular report in PDF format
(for our customers' convenience). On the "click the print button" event,
I use exactly the same query to build exactly the same temp table.
Quote :
are executing the query again resulting in a new temp table.
Yes.
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
ab5ni
|
| Posted: 05/03/2007, 9:03 AM |
|
_________________
--
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! Friend
asked if I used salt, and I told him I
use it when I eat encrypted spaghetti code.
---------------------------------------
Sent from YesSoftware forum
http://forums.codecharge.com/
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 05/03/2007, 10:17 AM |
|
Quote :
You are mixing database connections to be looked upon as sessions.
Ah. Ok.
Quote :
You are building the temp table in one session (you just put SQL
query to build temp goes here) and then you connect to the database
and then SELECT from
Temporary tables only exist in and while in the same connect !
So, what you're saying here is that only the connection that
builds the temporary table can access the table. If so, that
sucks, dewd! (To quote Eric Cartman) . Seriously, with
this being the case, and without really researching this
particular item (for the moment), I'm thinking that temporary
tables are, in all actuality, MySQL memory tables. Also,
this would explain the reason why a temp table with the
same name as an existing, real table can exist at the
same time. Thinking this through a bit further, one would
think that they'd be using some form of locking mechanism,
such as semaphores and the like, which should solve this
situation. OTOH, temp tables are really just add-on kludges
and band aids, IMHO. What we all really need are read-write
cursors, such as the ones available in Visual FoxPro, SQL Server,
and the like. Then we wouldn't run into the BS .
Quote :
Resquence your actions or, instead of building a temporary table
create a 'normal' table with a specific name, fill it, run the report
and delete it, or truncate it and reuse it constantly.
Name it after the report, if multi-user is anticipated add some identifier.
We thought about all of this, Walter, and we decided to go with the
temp tables. This particular app will be used by 20-30 users (per server),
and over half of these users will be interested in viewing data and
printing PDF reports. With this being the situation, we decided that
temporary tables or memory tables might be the better way to go.
(Memory is cheap, and the best way to speed up MySQL is to feed all
the RAM it can handle.) Creating real-world, existing tables has us a
bit worried, especially when we started contemplating all of these
tables being created and deleted while intensive IO-bound queries
were taking place.(Read: we're worried about excessive and unnecessary
thrashing.
Quote :
ps. you respond to all posts but have you read mine?
Yes, Walter, I reply to all the people that are kind enough to help me,
and that includes you, too! Thanks! . Yeah, it might be time
consuming a bit overboard, but I'd rather be that way than have
people color me rude. It's just not the way I was raised. (Shrug.)
Anywho, thanks for the reply, Walter. We'll give your suggestions
a try, benchmark the overall system, and we'll even let you know
which process is faster, slower, more memory intensive and the like
if you (or others here) are interested.
Randall
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/29/2007, 6:44 AM |
|
Any progress?
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
ab5ni
Posts: 177
|
| Posted: 08/30/2007, 2:12 PM |
|
Hey Walter,
Long time no type, dewd. To answer your question: Yes, we've made a ton
of progress. We've learned (the hard way ) that we have to leave the
temporary table alone and not mess with the thing. What we do is make
2-3 connections to the database, with one of them being to the temp
generated temp table. As long as we don't touch that connection, the
table still exists and data is still valid.
Regards,
Randall
P.S.
I'll send u a snip of the code if you'd like.
_________________
Randall Jouett
Amateur Radio: AB5NI
I eat spaghetti code out of a bit-bucket while sitting at a hash table! And yes, I paid for the meal in cache!
|
|
|
|
|
 |
|
|
|
