display a sqlite database query in html

Question

So this is what I have, I have a SQLite database with 3 tables: status,up_date & users. This database is on the server. I need to run following query on this:

SELECT USR.name, USR.cymer_id, ST.license
FROM
users USR,
status ST,
upd_ate UD
WHERE
UD.upt_id = (select max(p2.upt_id) from upd_ate p2)
AND ST.cymer_id = USR.cymer_id
AND ST.upt_id = UD.upt_id
ORDER BY USR.name

And then display the result on a webpage using HTML.

I was wondering is there any way to do this using java/javascript? or is there any way I can write a java/perl/javascript code to get a xml/text of the output and then display that?

Thanks for your help.

Oh by the way, if you expect SO to write a chunk of immediately usable code for you, you're in the wrong place. Too many unstated variables for that. — Seva Alekseyev
– Seva Alekseyev, Commented Jun 14, 2012 at 19:11

Ωmega · Accepted Answer · 2012-06-14 20:34:14Z

2

In Perl, use CGI to generate HTML page and DBI for database query:

#!/usr/bin/perl

use strict;
use warnings;
use CGI;
use DBI;

my $q = CGI->new;
print $q->header, $q->start_html('hello'), '<table>';

my $dbh = DBI->connect("dbi:SQLite:dbname=mydatabase.db", "", "", {});

my $query = <main::DATA>;
my $query_handle = $dbh->prepare($query);
$query_handle->execute();
$query_handle->bind_columns(\my($id, $name, $license));

while($query_handle->fetch()) {
  print "<tr><td>$id</td><td>$name</td><td>$license</td></tr>\n"
}

$query_handle->finish;
$dbh->disconnect;

print '</table>', $q->end_html;

__DATA__
SELECT USR.cymer_id, USR.name, ST.license
FROM users USR, status ST, upd_ate UD
WHERE
  UD.upt_id = (select max(p2.upt_id) from upd_ate p2)
  AND ST.cymer_id = USR.cymer_id
  AND ST.upt_id = UD.upt_id
ORDER BY USR.name

edited Jun 14, 2012 at 20:34

answered Jun 14, 2012 at 18:12

Ωmega

44k35 gold badges143 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Seva Alekseyev Over a year ago

If I knew more about the OP's server platform, I'd suggest something in either Java or JavaScript, which the OP clearly prefers.

Ωmega Over a year ago

@SevaAlekseyev - OP has posted his question with more tags and one of them is Perl. Anyway - how would you do it in JavaScript to protect password to access the db?

Seva Alekseyev Over a year ago

Haven't noticed, my bad. The question text does not mention Perl.

user1285 Over a year ago

Got an error ::Error:Can't find string terminator "END_OF_SQL" anywhere before EOF at C:/Users/workspace/License/l1.pl line 19.

Ωmega Over a year ago

@shaleen - Be sure you copied source code correctly, as after your SQL statement, next line is "END_OF_SQL" (see my answer). Also be sure that behind that string on that line there is no other character, not even white space!

|

Seva Alekseyev · Accepted Answer · 2012-06-15 13:30:53Z

0

You'll need to write some server-side code for that. There are options for server-side JavaScript: Node.js, classic ASP. Do you know if your Web server supports any of those? Is it a Windows or a *nix server? Is it your home server, your company's server or a hosted one?

In classic ASP with JavaScript, it might look like:

<%@Language="javascript"%>
<html><body><table>
<%
var conn = Server.CreateObject("ADODB.Connection");
conn.Open("Provider=OleSQLite.SQLiteSource;Data Source=PATH_TO_DATABASE");

var rs = conn.Execute("SELECT USR.cymer_id, USR.name, ST.license
    FROM users USR, status ST, upd_ate UD
    WHERE   UD.upt_id = (select max(p2.upt_id) from upd_ate p2)
        AND ST.cymer_id = USR.cymer_id
        AND ST.upt_id = UD.upt_id ORDER BY USR.name");

rs.MoveFirst();
while(!rs.EOF)
{
%>
<tr>
<td><%=Server.HtmlEncode(rs["cymer_id"])%></td>
<td><%=Server.HtmlEncode(rs["name"])%></td>
<td><%=Server.HtmlEncode(rs["license"])%></td>
</tr>
<%
    rs.MoveNext();
}
rs.Close();
conn.Close();

%>
</table></body></html>

You'll need to install the SQLite OLE DB provider. Save as an ASP file, plug the correct database path. On some versions of IIS, you'll have to explicitly enable classic ASP. The Connection and Recordset objects are provided by the Microsoft ADO library.

edited Jun 15, 2012 at 13:30

answered Jun 14, 2012 at 18:10

Seva Alekseyev

61.8k25 gold badges173 silver badges297 bronze badges

1 Comment

user1285 Over a year ago

it's the company's server; I've just started here, I'm not sure if it supports any of those, its a Windows Server; but I also have the database locally available, just so I can make it work. What other variables should I provide?

anisbet · Accepted Answer · 2014-11-07 17:01:33Z

0

In Perl (on a Unix box) a quick-and-dirty way to do this is:

open( HTML, ">$htmlFile" or die "**error: unable to write to file '$htmlFile', $!\n" );
print HTML `echo "SELECT * FROM holds;" | sqlite3 -html holds_data.db`;
close( HTML );

I haven't experimented using this in Windows.

answered Nov 7, 2014 at 17:01

anisbet

2,6722 gold badges20 silver badges12 bronze badges

Collectives™ on Stack Overflow

display a sqlite database query in html

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related