I see what you're trying to do, and you're pretty close, but you're missing a couple important points. You want to have an extended number of rows from the query displayed in a ROText widget (which, by default, will be scrollable using arrow keys or the mouse scroll wheel). But, as pointed out above, your query returns only one row, and doesn't match up with your attempted use of bind_columns.
Try it like this (I'm only changing the bare minimum to do what I think you really want, but I'm not able to test it):
#!/usr/bin/perl
use strict;
use warnings;
use Tk;
use Tk::ROText;
use DBI;
## use DBD::mysql; # DBI will do this for you when you connect
our $type="mysql";
our $database="beezynet_db";
our $host="http://www.xsite.com/";
our $port="3306";
our $tablename="topic";
our $user="username123";
our $pwd="**************";
our $dsn="dbi:$type:$database:$host:$port";
my $query;
my $queryhandle;
our $connect=DBI->connect($dsn,$user,$pwd)or die &mysql_Err;
my $mw=new MainWindow;
$mw->geometry('400x400');
my $rotext=$mw->ROText()->pack;
# I can only guess at the real column names - please fix them as neede
+d:
$query="SELECT id, item_name, item_price FROM items ORDER BY id";
my ( $id, $item_name, $item_price );
$queryhandle=$connect->prepare($query);
$queryhandle->execute;
$queryhandle->bind_columns(undef, \$id, \$item_name, \$item_price);
while($queryhandle->fetch()){
# NB: use "end" instead of "1.0" here:
$rotext->insert('end', "ID: $id\t Price:: $item_price\tName: \$ite
+m_name");
}
$queryhandle->finish;
# $connect->disconnect; ## display will work, even while connected.
MainLoop;
(I tested something pretty similar to that on a database of my own, so it ought to work for you.) | [reply]
[d/l] |
| [reply] |
The SQL statement is not really valid.
COUNT(*) is an aggregate function, meaning, it will aggregate data from all records (in the group) and return one record for it. Without a GROUP BY clause, there is only one group: the entire table. This means that the query will return exactly one record, and that will be the count of records in the table. Because of that, the ORDER BY clause makes no sense. Both because one record does not need an ordering, and because the column mentioned in the ORDER BY clause does not appear in the column list. (Columns not specifically mentioned in an aggregate query's GROUP BY clause are not valid to be referred to outside an aggregate function.)
To make that statement valid (and sensical) it would be written: SELECT Id, COUNT(*) FROM Items GROUP BY Id ORDER BY Id; (ASC is the default and nearly always left out.)
| [reply] |
It's typical for a column called "id" (or "Id") to be the primary key field of the given table, so grouping by Id would be even more silly than using an "order by" clause on a query that returns just one row.
Also, in the case of mysql, an "order by" clause can refer to any column in the table being queried, regardless whether that column is used in any way elsewhere in the query.
So while it's true that the OP should remove the unnecessary "order by" clause, having it in there is a harmless mistake in this case.
| [reply] |
It's typical for a column called "id" (or "Id") to be the primary key field of the given table, so grouping by Id would be even more silly than using an "order by" clause on a query that returns just one row.
I thought i addressed that with Because of that, the ORDER BY clause makes no sense. Perhaps i need to be clear next time.
"id" (or "Id")
Databases capitalize all non-quoted object names. (SQL-generators use quotes to be safe.) So, it's just a matter of personal preference. As an aside, typical SQL developers' convention is the refer to clauses without the quotes, with some capitalizing them for clarity.
an "order by" clause can refer to any column in the table being queried, regardless whether that column is used in any way elsewhere in the query.
I don't know about mysql, but a query with an aggregate function cannot refer to any column--even in the ORDER BY-- unless it is specified in the GROUP BY. Logically, it makes no sense. That is, unless you are grouping by it, the column doesn't exist in the data set!
having it in there is a harmless mistake in this case.
I don't know mysql, but on every other RDBMS it would be generating an error.
| [reply] |
Programs like this one usually need to have at least two threads, since the “main thread” is obligated to respond to messages from the GUI. Every mouse-click, maybe mouse-pointer moves, notifications to repaint this-or-that, and so-forth, generates a “message,” and the main thread must very-timely respond to every one. (Basically, this is the main-thread’s only job ...) Otherwise, the interface will freeze. Therefore, a second thread must take care of anything that is time-consuming and/or that requires waiting. The thread can update the interface: its actions will be properly interlocked with those of the main thread. Typically, this thread is sent requests to do work, which are posted to it in a thread-safe queue, and it dequeues the requests and carries them out. The main thread doesn’t wait for them to be completed, but goes about its business of keeping the screen up-to-date. The two threads are completely asynchronous to one another.
There are lots of existing examples of GUI-based programs (Perl/TK nd otherwise ...) which do “time-consuming things” in this way, so I won’t repeat myself further. There are also existing frameworks which include at-least the skeleton of the necessary multi-threaded architecture. So, look around.
| |