"Symfoware OPEN" database application development

Symfoware Open System

-Import method, connection form, execution method

JDBC (Java database Connectivity) is the interface specification for Java and database, and JDBC defines a generic low-level application programming Interface (API) that supports standard SQL functionality, which consists of classes and interfaces written in the Java language. Designed to allow database developers to provide standard database APIs for Java programmers.

ODBC (Open database Connectivity) is a widely accepted application programming interface (API) for database access by Microsoft and is based on the call-level Interface (CLI) specification of X/open and ISO/IEC. and uses Structured Query Language (SQL) as its database access language.

The JDBC/ODBC overall structure has four components:
Application, Driver Manager, driver, data source.

The JDBC Driver Manager is built-in and the driver itself can be downloaded automatically through a Web browser without installation, configuration, and ODBC Driver Manager and ODBC driver must be installed, configured separately on each client computer.

There are three ways to connect a database to a Java program: OCI mode, thin mode, and Jdbcodbc bridge mode:
OCI mode:
Application---JDBC API---driver---data source
Thin Way:
Thin mode is pure Java implementation of TCP/IP communication, and OCI mode, the client through the native Java method call C library access to the server, and this C library is the OCI (Oracle called Interface), So this oci always needs to be installed with the Oracle client
JDBCODBC Bridge mode (for Windows platforms):
Application--->JDBC API--->JDBC-ODBC--->ODBC API--->ODBC layer---> Data source

The ADO data providers is a set of basic class libraries that provide access to the specified data source.
Provider API prefix Data source description
ODBC Data Provider ODBC provides an ODBC interface for the DataSource. It is generally a relatively old database.
OLE DB data Provider OLE DB provides a data source for the OLE DB interface, such as Access or Excel.
Oracle Data Provider Oracle Oracle Database
SQL Data Provider SQL Microsoft SQL Server database
Borland data Provider BDP has access to many databases, such as InterBase, SQL
Server, IBM DB2, and Oracle.
These database connection methods Odbc,dao,rdo,ole Db,ado,ado. NET is based on Oracle client (Oracle OCI), which communicates with the database through Sql*net. If the pursuit of performance, you can develop the most suitable for their own database connection mode.

LIBPQ is the interface of PostgreSQL's C application staff. LIBPQ is a set of library procedures that allow clients to send queries to the PostgreSQL backend service process and get queries back. LIBPQ is also the engine for several other PostgreSQL application interfaces, including libpq++ (C + +), LIBPGTCL (TCL), Perl, and ECPG.

Reference: http://blog.csdn.net/wyzxg/article/details/5949424

"JDBC"

Development environment
Setup
1. Environment setting
Set Classpat
Jdbc:postgresql-jdbc4.jar

2. Setting language
Java-duser.language=en TestClass1

3, using SSL encryption (by default, SSL uses a parameter of false)
Cases:
1)
String url = "Jdbc:postgresql://sv1/test";
Properties Props = new properties ();
Props.setproperty ("User", "SYMFO");
Props.setproperty ("Password", "secret");
Props.setproperty ("SSL", "true");
Connection conn = drivermanager.getconnection (URL, props);

2)
String url = "Jdbc:postgresql://sv1/test?user=symfo&password=secret&ssl=true";
Connection conn = drivermanager.getconnection (URL);

Database connection
1. Using DriverManager
1) Specify Org.postgresql.Driver
2) Connection string:
Jdbc:postgresql://host:port/database?
User=user&password=password1&logintimeout=logintimeout&sockettimeout=sockettimeout
Options:
1) host host;
2) port port number, default is 26500;
3) database name;
4) User user name, the default is to run the program users;
5) password password;
6) Timeout on LoginTimeout connection: 0~9223372036854775 (seconds), 0 specified, or no limit specified;
7) sockettimeout Communication timeout: 0~2147483647 (seconds), specify 0, or do not specify unrestricted.

Connection examples:
Import java.sql.*;
...
Class.forName ("Org.postgresql.Driver");
String url = "Jdbc:postgresql://sv1:26500/mydb?"
User=myuser&password=myuser01&logintimeout=20&sockettimeout=20 ";
Connection con = drivermanager.getconnection (URL);

Use Example:
public string Fetchfilebybytes (string path) { 
  String sql = "SELECT * from Blob_table"; 
  Connection conn = null; 
  PreparedStatement PS = null; 
  ResultSet rs = Null;&nbs P
  File file = null;
  OutputStream fos = null; 

  Try { 
    conn = Util.getconn ();
    PS = conn.preparestatement (SQL);
    rs = Ps.executequery ();
    byte[] buffer = NULL;
    file = new file (path);
    fos = new FileOutputStream (file);
    while (Rs.next ()) {
    buffer = rs.getbytes ("file");
    fos.write (buffer);
   }
    Fos.flush ();
    fos.close ();
 } catch (SQLException e) { 
    e.printstacktrace (); 
    return null; 
 } catch (IOException e) { 
    e.printstacktrace (); 
& nbsp } finally { 
    util.close (NULL, PS, conn); 
 } 
  return Null;&nbs P
}
Reference: http://blog.csdn.net/no_cross_no_crown/article/details/6098222

2. Using Pgpoolingdatasource
Options:
1) Setdatasourcename set the data source name, there are multiple data sources, the name must be unique;
2) Setservername host;
3) Setportnumber port number, default is 26500;
4) Setdatabasename database name;
5) SetUser user name, the default is the user running the program;
6) SetPassword password;
7) Timeout on setLoginTimeout connection: 0~9223372036854775 (seconds), 0 specified, or no limit specified;
8) setsockettimeout Communication timeout: 0~2147483647 (seconds), specify 0, or do not specify unrestricted.

Example:
Import java.sql.*;
Import Org.postgresql.ds.PGPoolingDataSource;
...
Pgpoolingdatasource Source = new Pgpoolingdatasource ();
Source.setdatasourcename ("Jdbc/ds1");
Source.setservername ("Sv1");
Source.setportnumber (26500);
Source.setdatabasename ("MyDB");
Source.setuser ("MyUser");
Source.setpassword ("Myuser01");
Source.setlogintimeout (20);
Source.setsockettimeout (20);
...
Connection con = source.getconnection ();

Reference:
http://i-am-birdman.iteye.com/blog/821743

3. Using PGCONNECTIONPOOLDATASOURC
Options:
With Pgpoolingdatasource.

Example:
Import java.sql.*;
Import Org.postgresql.ds.PGConnectionPoolDataSource;
...
Pgconnectionpooldatasource Source = new Pgconnectionpooldatasource ();
Source.setservername ("Sv1");
Source.setportnumber (26500);
Source.setdatabasename ("MyDB");
Source.setuser ("MyUser");
Source.setpassword ("Myuser01");
Source.setlogintimeout (20);
Source.setsockettimeout (20);
...
Connection con = source.getconnection ();

4. Using PGXADATASOURC
Options:
With Pgpoolingdatasource.

Example:
Import java.sql.*;
Import Org.postgresql.xa. ;
...
Pgxadatasource Source = new Pgxadatasource ();
Source.setservername ("Sv1");
Source.setportnumber (26500);
Source.setdatabasename ("MyDB");
Source.setuser ("MyUser");
Source.setpassword ("Myuser01");
Source.setlogintimeout (20);
Source.setsockettimeout (20);
...
Connection con = source.getconnection ();

Summary
DriverManager and DataSource

DriverManager traditional JDBC Connection, through Class.forName ("XXX"), the method of registration, you can drivermanager.getconnection () get connected.

DataSource is built on the Jndi service and requires application server configuration datasource. First, you need to register a datasource (usually under/meta-inf/context.xml) You can then refer to this datasource in the Web. xml file to get the connection datasource.getconnection ().
Such as:
Context CTX = new InitialContext ();
DataSource ds = (DataSource) ctx.lookup ("Jdbc/openbase");

DataSource If you register a data source object in Jndi, you will have two advantages over using DriverManager:
1. The program does not need to hard-code the loaded database driver information like using DriverManager, the programmer can choose to register the data source object in Jndi first, then use a logical name to refer to it in the program. Jndi automatically finds the DataSource object that is bound to the name based on the name you give. You can then use this DataSource object to establish a connection to the specific database.

2. The second advantage of using a class that implements the DataSource interface is in connection pooling and distributed transactions. The connection pool significantly increases the efficiency of the program by reusing the connection instead of creating a new physical connection. This makes it suitable for enterprise-class distributed transactions that are busy and burdened with heavy tasks.

Connection pool:
Connection pooling (connection pooling) technology in multi-tiered applications allows for a noticeable performance of the system, which means that when an application needs to invoke a database connection, The database-related interface is returned by reusing a database connection instead of recreating a database connection. In this way, applications can reduce database connection operations, especially in multi-tier environments where multiple clients can meet system requirements by sharing a small number of physical database connections. Java applications through connection pooling technology not only improve system performance but also improve the scalability of the system.

The database connection pool is running in the background and the encoding of the application has no effect. The premise of this condition is that the application must use the DataSource object (an instance that implements the Javax.sql.DataSource interface) in place of the original method of obtaining a database connection through the DriverManager class. A class that implements the Javax.sql.DataSource interface can or may not support a database connection pool, but the code that obtains the database connection is basically the same.

Reference:
http://tobylxy.iteye.com/blog/1673421 (the difference between DriverManager and DataSource and the JDBC test code)
http://www.iteye.com/problems/77185
Http://blog.sina.com.cn/s/blog_4e57731f0100bfsj.html

"ODBC" (example of a Linux system)
Installation driver:

Install ODBC driver under Linux system
1, installation Unixodbc;
2, edit the Unixodbc odbcinst.ini,odbcinst.ini file in the UNIXODBC installation directory/etc/odbcinst.ini;
Set up:

1) Driver name
(32-bit)
If the text encoding is EUC_JP or SHIFT-JIS,PSQLODBC select "Symfoware serverv12.0ansi";
If the text encoding is UTF-8,PSQLODBC select "Symfoware serverv12.0unicode".
(64-bit)
If the text encoding is EUC_JP or SHIFT-JIS,PSQLODBC select "Symfoware serverv12.0x64ansi";
If the text encoding is UTF-8,PSQLODBC select "Symfoware serverv12.0x64unicode".

2) Description
ODBC driver description
3) Driver
(32) Drive Path, text encoding for EUC_JP or Shift-jis, path=/opt/symfoclient32/odbc/lib/psqlodbca.so;
(32) Drive Path, text encoding for UTF-8, path=/opt/symfoclient32/odbc/lib/psqlodbcw.so;

4) Driver64
(64) Drive Path, text encoding for EUC_JP or Shift-jis, path=/opt/symfoclient64/odbc/lib/psqlodbca.so;
(64) Drive Path, text encoding for UTF-8, path=/opt/symfoclient64/odbc/lib/psqlodbcw.so;

5) FileUsage: specified as 1;

6) Threading: Specified as 2.

Example
32-bit LINUXODBC driver settings
[Symfowareserverv12.0unicode]
Description = symfoware Server V12.0 Unicode Driver
Driver =/opt/symfoclient32/odbc/lib/psqlodbcw.so
FileUsage = 1
Threading = 2

To register a data source:
1. Register Data source
Define the data source in the Odbc.ini file, install directory (UnixODBC)/etc/odbc.ini.
Options:
1) data source name;
2) Description description;
3) Driver Select the same settings as the driver name, do not change its value;
4) Database specified
5) Servername Host
6) Username Connected users
7) Password Password
8) port Ports
9) Sslmode Encryption mode disable: Do not use SSL connection;
Allow: Use SSL connection;
Prefer: First use SSL when connecting;
Require: SSL connection must be used.
READONLY Specifies whether the connection is read-only 1: Read-only, 2: not read-only.

Example: (32-bit Linux)
[myDataSource]
Description = Symfowareserver
Driver = Symfowareserverv12.0ansi
Database = Db01
Servername = Sv1
UserName = Symfo
Password = Secret
Port = 26500
ReadOnly = No

2. Setting Environment variables
Settings: Ld_library_path
(32-bit Linux bash)
Ld_library_path=/usr/local/lib (※):/opt/symfoclient32/lib: $LD _library_path;export
Ld_library_path

Application development

Example:
Linux 32-bit
Gcc-m32-i/usr/local/include-l/usr/lib (※)-l ODBC Testproc.c-o TestProc

Linux 64-bit
Gcc-m64-i/usr/local/include-l/usr/lib64 (※)-l ODBC Testproc.c-o TestProc

-M32:32-bit applications;
-M64:64-bit applications;
-I:UNIXODBC driver installation directory;
The directory where the-L:UNIXODBC library resides;

Note: (※) indicates an example; To use UNIXODBC, specify the necessary path.

Reference:
http://jingyan.baidu.com/article/8065f87f38b31423312498e4.html (MySQL)
Http://www.xz7.com/article/18905_1.html

Connection database (MySQL)
String constr = "Dsn=odbcformysql";
OdbcConnection conn = new OdbcConnection (CONSTR);
Conn. Open ();
String sqlstr = "SELECT * from Tools.zhanghao";
OdbcCommand cmd = new OdbcCommand (SQLSTR, conn);
OdbcDataReader reader = cmd. ExecuteReader ();
while (reader. Read ()) {
Console.Write (reader. GetString (0) + ",");
Console.Write (reader. GetString (1) + ",");
}
Conn. Close ();
Console.read ();

Reference:
Http://jingyan.baidu.com/article/60ccbceb018f4d64cab19787.html

". NET Data Provider"
Setup
Visual Studio (Visual Studio 2010) appends Fujitsu Npgsql. Net Data Provider;
1. Windows program: "Project" menu select "Append Options",
Web program: "Web Site" menu select "Append option";
2. ". Net" tab "Component" add Fujitsu npgsql. NET Data Provider, click "OK".

Installing アドオン
After installation, you can use TableAdapter to Npgsql development Tools for. NET provided.
Pgx_ndtregister.exe need to run with administrator privileges, path: Install directory \dotnet\bin\.
32-bit アドオン registration:
> pgx_ndtregister.exe-x86

Connecting to a database
Options:
Server host;
Port port number, default is 26500;
database connection name;
User Id login username;
Password login password;
Timeout Connection Timeout time 0~1024 (seconds), 0 means no limit, default is 15 seconds;
CommandTimeout Communication Timeout 0~2147483647 (seconds), 0 or negative number means no limit, default 20 seconds.
。。。

1. Using Npgsqlconnection
Using Npgsql;
Npgsqlconnection conn = new Npgsqlconnection ("SERVER=SV1; Port=26500;database=mydb; User
Id=myuser; Password=myuser01; timeout=20; commandtimeout=20; ");

2. Using Npgsqlconnectionstringbuilder
Using Npgsql;
Npgsqlconnectionstringbuilder sb = new Npgsqlconnectionstringbuilder ();
Sb. Host = "SV1";
Sb. Port = 26500;
Sb. Database = "MyDB";
Sb. UserName = "MyUser";
Sb. Password = "Myuser01";
Sb. Timeout = 20;
Sb.commandtimeout = 20;
Npgsqlconnection conn = new Npgsqlconnection (sb.) ConnectionString);

3. Using ProviderFactory
Using System.Data.Common;
DbProviderFactory factory = dbproviderfactories.getfactory ("Npgsql");
DbConnection conn = Factory. CreateConnection ();
Conn. ConnectionString = "SERVER=SV1; Port=26500;database=mydb; User Id=myuser; Password=myuser01;
timeout=20; commandtimeout=20; ";

"LIBPQ"
LIBPQ is the interface of PostgreSQL's C application staff. LIBPQ is a set of library procedures that allow clients to send queries to the PostgreSQL backend service process and get queries back. LIBPQ is also the engine for several other PostgreSQL application interfaces, including libpq++ (C + +), LIBPGTCL (TCL), Perl, and ECPG.

Installation:
LIBPQ, which is bound together with the PostgreSQL source tree. If you are installing a database system using a binary file instead of compiling it from the source, LIBPQ can be installed separately, but remember to use the option-dev package.

Compile:
In the C program file, include the libpq-fe.h header file and add the appropriate link tag-lpq at compile time.

To connect to a database:
Options:
Host hosts;
HOSTADDR host IP address;
Port number of the port host server;
dbname database name;
User connection username;
password password;
Connect_timeout the time range that is set for the connection process, in seconds. 0 or not set to indicate infinity;
Options to the back end of the Tracking/debugging option;
TTY file or console (TTY) for optional debug output from the backend;
The requireSSL is set to ' 1 ' to require an SSL join with the backend, set to ' 0 ' (the default) to negotiate with the server.

(using HOSTADDR instead of host allows the app to avoid a hostname lookup, which can be very important for applications that have time constraints.) However, the Kerberos authentication system requires a host name. ）；

1, PQCONNECTDB and the backend database server to establish a new join.
Pgconn *pqconnectdb (const char *conninfo)

This process opens a new join with the database using arguments from a string conninfo. Unlike the following Pqsetdblogin (), we can extend the parameter set without having to replace the function signature (first name), so we recommend using this function or its non-blocking similar functions Pqconnectstart and Pqconnectpoll in the application. The arguments passed in can be empty, indicating that all default parameters are used, or you can include one or more parameter settings with white space intervals.

2, Pqsetdblogin and the backend database server to establish a new join. This function is PQCONNECTDB predecessor, it has a fixed number of parameters, but has the same function.

3, PQSETDB and the backend database server to establish a new join. This is a macro that calls Pqsetdblogin (), except that the login and PWD parameters are empty (null) instead, providing this function primarily for compatibility with older versions of the program.

4, Pqconnectstart, pqconnectpoll to establish a non-blocking connection with the database server.
Pgconn *pqconnectstart (const char *conninfo)
Postgresqlpollingstatustype Pqconnectpoll (Pgconn *conn)

These two procedures are used to open a non-blocking join with the database server, and the application's execution lead is not blocked by the remote I/O at the time it is executed, although there are some conditions:
1) The HOSTADDR and host parameters must be provided correctly to ensure that no forward or reverse name lookups occur;
2) If you call the Pqtrace, make sure that the stream object being tracked is not blocked;
3) You must ensure that the socket is in the correct state before calling Pqconnectpoll.

Starting the join, calling Conn=pqconnectstart ("connection_info_string"), if Conn is null, indicates that LIBPQ cannot allocate a new pgconn structure. Otherwise, a valid Pgconn pointer is returned. Pqconnectstart returns a call to Status=pqstatus (conn). If status equals Connection_bad,pqconnectstart fails.

5. Pqconndefaults returns the default join options. Returns the address of the JOIN option structure.

6. Pqfinish closes the connection to the back end while releasing the memory used by the Pgconn object.
void Pqfinish (Pgconn *conn)

Note: Even if the join attempt with the backend fails (which can be judged by pqstatus), the application also calls Pqfinish to release the memory used by the Pgconn object and should not use the Pgconn pointer after calling Pqfinish.

7. Pqreset Reset the communication port with the back end.
void Pqreset (Pgconn *conn)

8, Pqresetstart Pqresetpoll in non-blocking mode to reset the communication port with the back end.
int Pqresetstart (Pgconn *conn);
Postgresqlpollingstatustype Pqresetpoll (Pgconn *conn);
This function closes the join to the back end and attempts to rebuild the new join with the same server, using all previously used parameters. If it returns 0, then the reset fails. If you return 1, use Pqresetpoll to reset the join using the same method that you used to establish the join with Pqconnectpoll.

9. PQDB returns the database name of the join.
Char *pqdb (const pgconn *conn)
Declared to be a const type, this value is fixed for the lifetime of the Pgconn object (hereinafter).

10. Pquser returns the user name of the join.
Char *pquser (const pgconn *conn)

11. Pqpass returns the password of the join.
Char *pqpass (const pgconn *conn)

12. Pqhost returns the server host name of the join.
Char *pqhost (const pgconn *conn)

13. Pqport returns the port number of the join.
Char *pqport (const pgconn *conn)

14. Pqtty returns the debug console (TTY) of the join.
Char *pqtty (const pgconn *conn)

15. Pqoptions returns the back-end option used in the join.
Char *pqoptions (const pgconn *conn)

16. Pqstatus returns the state of the join.
Connstatustype pqstatus (const pgconn *conn)

17. Pqerrormessage returns the most recent error message generated by the operation in the join.
Char *pqerrormessage (CONST pgconn* conn);

18. Pqbackendpid returns the process number ID of the backend server that controls this join.
int pqbackendpid (const pgconn *conn);

19. PQGETSSL returns the SSL structure used by the join, or returns NULL if SSL is not used.
SSL *pqgetssl (const pgconn *conn);

Example:
#include <stdio.h>
#include <libpq-fe.h>

int main (int argc,char* argv[]) {
const char *conninfo;
Pgconn *conn;
if (argc > 1)
Conninfo = argcv[1];
Else
{
printf ("Not enough arguments, exiting ...");
return 1;
}
conn = Pqconnectdb (Conninfo);
/*check to see what I did */
if (pqstatus (conn) = = CONNECTION_OK)
printf ("Connection succeeded.\n");
Else
{
printf ("Connection failed.\n");
}
}
Run after compilation:
$ TESTLIBPG "hostaddr=127.0.0.1 user=postgres dbname=testdb1"
If successful display: "Connection succeeded."

Other sample programs:
Http://www1.phpchina.com/resource/manual/PostgreSQL/libpq-example.html

Reference:
http://tanhp.com/index.php/archives/208/
Http://www1.phpchina.com/resource/manual/PostgreSQL/libpq.html#LIBPQ-INTRO
http://blog.csdn.net/freeboy1015/article/details/9307993

This article is from the "Night" blog, be sure to keep this source http://icyore.blog.51cto.com/8486958/1596297

"Symfoware OPEN" database application development