sqlite数据库如何远程连接?

sqlite远程连接示例

Sqlite 是一个只有几百 k 大小的、 优秀的嵌入式数据库，本身不带有远程连接的功能。由于其身材小，速度快并且为众多的语言支持，所以在单机领域得到了广泛的使用。但由于天然不支持服务 / 客户端的模式，使其在遇到小型规模数据库远程连接的情况时不得不借助于附加的编程。

单位有三百多人，作为网管每天要接到很多电话，有很多一时无法想起对应的名字，翻查通讯录不仅速度慢而且容易漏过。为了方便起见，在 sqlite 上建立了一个通讯录数据表，然后用 perl 编写了一个查询脚本。效果不错的同时，也有同事希望可以得到一份拷贝。但是这时遇到一个问题，如何保证通讯录在不同人手中保持版本的统一性。最好的办法是建立一个服务 / 客户端的模式，在本机上维护一个数据库，而其它人通过连接数据库得到相关的查询结果。

具体的解决方法是这样的：在本机上维护一个到 sqlite 数据库的连接并建立一个来自于客户端的 socket 的监听，接受远程查询条件并回复查询结果；客户端志负责发送和接受这些最简单的任务。

客户端的代码：

use strict

my $in_buffer = undef

my $PF_INET = 2

my $port = 2345

my $remote_addr = pack('SnC4x8',$PF_INET,$port,192,168,138,228)

my $SOCK_DGRAM = 2

socket(UDP_CLIENT, $PF_INET, $SOCK_DGRAM, getprotobyname('udp'))

while(1){

       print(" 输入名字或号码： ")

       my $out_buffer=<STDIN>

       chomp($out_buffer)

       if($out_buffer eq "exit"){last}

       send(UDP_CLIENT, $out_buffer, 0, $remote_addr)

       print("waiting for reply...\n")

       recv(UDP_CLIENT, $in_buffer, 100, 0)

       chomp($in_buffer)

       print("$in_buffer\n")

}

close(UDP_CLIENT)

       服务器端的代码：

BEGIN{

       if( $^O eq 'MSWin32' ){

              require Win32::Console

              Win32::Console::Free()

       }

}

use strict

use DBI

#database parameters

my $db_path = 'd:/src/cc/phones.db'

my $dbh = DBI->connect("dbi:SQLite:$db_path", {PrintError => 0}) or die $DBI::errstr

my $sth = undef

#socket server parameters

my $in_buffer = undef

my $out_buffer = undef

my $PF_INET = 2

my $port = 2345

my $local_addr = pack('SnC4x8',$PF_INET,$port,192,168,138,228)

my $SOCK_DGRAM = 2

socket(UDP_SERVER, $PF_INET,$SOCK_DGRAM, getprotobyname('udp')) or die("$!")

bind(UDP_SERVER, $local_addr) or die("$!")

listen(UDP_SERVER, 10)

while(1){

       #receive query then send result

       last unless my $remote_addr = recv(UDP_SERVER,$in_buffer,100,0)

       chomp($in_buffer)

       if($in_buffer =~ /^[0-9]{6}$/){

              $sth = $dbh->prepare("select * from phones where number = $in_buffer")

              PROCEDURE:

              $sth->execute()

              my @items = $sth->fetchrow_array()

              if(scalar(@items)){

                     $out_buffer = $items[0].' 的虚拟网号码是 '.$items[1]

              }else{

                     $out_buffer = ' 查无此人 '

              }

       }else{

              $sth = $dbh->prepare("select * from phones where name = '$in_buffer'")

              goto PROCEDURE

       }

       send(UDP_SERVER,$out_buffer,0,$remote_addr)

}

#disconnect from sqlite

$dbh->disconnect()

使用自己的文件锁解决这个问题。

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API functions.

qlite应该是只是一个本地文件，API放在各个语言的开发包里了，它本身不具备C/S的网络功能。

见官方文档：

“ If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite.”

如果一定想支持远程访问有这么几条出路：

1、换其他支持网络访问的数据库如MySQL。

如果坚持要用Sqlite

2、楼上所述，用网络文件系统，但是不建议。因为随机读写在NFS等系统上的性能都很成问题，而且稳定性堪忧。

3、用RPC等封装一下，如Thrift、XML-RPC等，Java的话还有RMI等直接可以搞起。

jdbc使用通过JAVA的数据库驱动程序直接连接到数据库，而jdbc-ODBC连接到ODBC数据源，实际连接到数据库的是ODBC。 建议使用JDBC直接连接，并使用连接池。

JDBC是“Java数据库连接”。 它由一组用Java编程语言编写的类和接口组成。 JDBC为工具和数据库开发人员提供了标准API，使他们能够使用纯Java API创建数据库APP应用程序。

开放数据库连接(odbc )是开放数据库互连。 ODBC基于SQL，作为访问数据库的标准。 该接口允许：个最大限度地提高互 *** 作性的APP应用程序通过一组公共代码访问不同的数据库管理系统。 软件开发人员开发的客户端/服务器APP应用程序不会局限于特定的数据库。 ODBC可以为各种数据库提供适当的驱动程序。 对于ASP课程

Qt专栏之模态与非模态对话框的实现

dbms和jdbc,mysql数据库odbc连接器

jdbc哪几种方式,jdbc主要

---