Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I was using MySQL and this code work fine but when I use SQLite, I could not display the data .... I check my SQLite data but nothing wrong. Is the code wrong? 
            $sqlstatement2 =qq~
                     SELECT rowid, NamaBarang      
                        FROM barangan
                       WHERE CompanyID = '$companyid'
                    ORDER by rowid ASC ~;
 
             $sth2 = $dbh->prepare($sqlstatement2);
             $sth2->execute() or &common_error("SQL Error","Couldn't e
+xecute statement2: $DBI::errstr",1);

            if($sth2->rows() <= 0)
            {
                $output .= qq~ 
                <tr>
                           <td colspan="3"><b><font size="2" face="Tah
+oma">&nbsp;No data $companyid in your list, please add.</font></b></t
+d>
                           </tr>
                </table>~;
                $sth -> finish();
            }

            else
            {
                $sth2= $dbh->prepare($sqlstatement2);
                 $sth2->execute() or &common_error("$sqlstatement2","C
+ouldn't execute statement1: $DBI::errstr",1);

                $number = 0; $harga = 0, $harga2 = kan.$harga; $hargas
+ = 0, $hargas2 = aan.$hargas ; $hargaz = 0, $hargas3 = gan.$hargaz ; 
+      
                while(($id,$ach) = $sth->fetchrow_array())
                {

[download]

20040729 Edit by ysth: Change title from SQLite

Replies are listed 'Best First'.
Re: Converting from MySQL to SQLite
by davido (Cardinal) on Jul 29, 2004 at 07:56 UTC

    If I'm not mistaken, SQLite currently only supports one active statement handle at a time. You have $sth2 and $sth active at the same time. I seem to remember that SQLite just won't "do that".


    Dave

[reply]
      everything else work fine , it seem 
                  if($sth2->rows() <= 0)

      [download]
      I check the data in SQLite there are more then one row, but why it enter this 'if' statement. the output are 'No data in your list, please add.' when there is data in SQLite
[reply]
[d/l]
        Well, in the code you presented, I see that '$sth2->rows()' is called, and later on, '$sth->finish()' is called. So you have two statement handles open at the same time.

        The SQLite Home Page has a page that describes common SQL features not implemented in SQLite. The list is short, but one item on it says, "Nested Transactions: The current implementation only allows a single active transaction." See http://www.sqlite.org/omitted.html.


        Dave

[reply]
Re: Converting from MySQL to SQLite
by Tomte (Priest) on Jul 29, 2004 at 08:58 UTC

    The code and error/problem-information you provided are insufficient to help you. to me at least $harga = 0, $harga2 = kan.$harga; doesn't make any sense, and perl doesn't like it that much either: 

    [1046]tom@margo scripts $ perl -we '$harga = 0, $harga2 = kan.$harga;'
Unquoted string "kan" may clash with future reserved word at -e line 1
+.

    [download]
    so I can't help you with your problem, as the SQL itself looks fine: 
    [1052]tom@margo scripts $ sqlite /home/tom/sqlitetest.sldb
SQLite version 2.8.14
Enter ".help" for instructions
sqlite> create table barangan (NamaBarang text NOT NULL, CompanyID int
+(9));
sqlite> insert into barangan (NamaBarang, CompanyID) VALUES ('hello', 
+1);
sqlite> insert into barangan (NamaBarang, CompanyID) VALUES ('hello2',
+ 2);
sqlite> SELECT rowid, NamaBarang
   ...>                          FROM barangan
   ...>                         WHERE CompanyID = '2'
   ...>                     ORDER by rowid ASC ;
2|hello2

    [download]

    2 more notes to make your job easier in the future:

    1. Always check for the return-value of anything that can produce errors and indicates errors via the return-value, including $dbh->prepare(), as ysth already said.

    2. Don't interpolate data into SQL when not technically necessary (e.g. dynamically inserting the tablename):
    instead of 

    $sqlstatement2 =qq~
                      SELECT rowid, NamaBarang      
                         FROM barangan
                        WHERE CompanyID = '$companyid'
                     ORDER by rowid ASC ~;

    [download]
    write 
    $sqlstatement2 =qq~
                      SELECT rowid, NamaBarang      
                         FROM barangan
                        WHERE CompanyID = ?
                     ORDER by rowid ASC ~;

    [download]
    and give $companyid as a parameter to $sth2->excute() (as in $sth2->excute($companyid)), or use $dbh->quote() before you interpolate: 
    my $quotedCID = $dbh->quote($companyid);
$sqlstatement2 =qq~
                      SELECT rowid, NamaBarang      
                         FROM barangan
                        WHERE CompanyID = '$quotedCID'
                     ORDER by rowid ASC ~;

    [download]
    To adopt the practice of using placeholders if you use SQL in any language you programm in, greatly increases the quality and security of your code.

    regards,
    tomte

    An intellectual is someone whose mind watches itself.
    -- Albert Camus

[reply]
[d/l]
[select]
Re: Converting from MySQL to SQLite
by ysth (Canon) on Jul 29, 2004 at 07:08 UTC
    Know nothing of SQLite, but perhaps you should check if the prepares are succeeding. I assume you are checking that the connect succeeds. Are you getting any errors? Or just no data?
[reply]
Re: Converting from MySQL to SQLite
by Matts (Deacon) on Aug 02, 2004 at 19:07 UTC
    SQLite's model does not return the number of rows once you execute the statement. The DBI states that not all drivers support this, and SQLite is one of the ones that doesn't support it.

    The only way to work this is to try and fetch your rows, and if nothing comes back then you know there were no rows (it's just an inversion of your logic there).

[reply]

Back to Seekers of Perl Wisdom