Freetds introduction, installation, configuration and usage, freetds Introduction

Freetds introduction, installation, configuration and usage, freetds Introduction

What is FreeTDS?
In short, FreeTDS is a library that allows you to access Microsoft's SQL database in Linux! FreeTDS is an open-source Library (if you like it, you can call it as a free library). It is a re-Implementation of the TDS (table data stream) protocol. It can be used in the database-lib or ct-lib library of Sybase. It also contains an ODBC library. Allow many open-source applications such as Perl and PHP (or your own c or C ++ program) to connect to Sybase or Microsoft SQL servers. FreeTDS is released as source code and can be compiled on almost any operating system. It means Unix and Unix-like systems (including well-known branches such as Interix and QNX), Win32, VMS, and OSX.

FreeTDS Installation
1. Download freetds, click here to download the http://www.bkjia.com/database/201983.html

2. decompress it to any directory and enter the decompressed folder.

3. switch to root and configure :. /configure-prefix =/usr/local/freetds-with-tdsver = 8.0-enable-msdblib:-prefix is the installation directory for setting FreeTDS, -with-tdsver is used to set the TDS version.-enable-msdblib indicates whether to allow the Microsoft database function library.

4. make & make install

5. Configure the environment variable: vim ~ /. Bashrc Add: export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH:/usr/local/freetds/lib/to this file/

FreeTDS test:
After FreeTDS is installed, you can view the FreeTDS status;

Run./tsql-C. Find tsql in the bin directory of the installation directory and view the information printed by the terminal.-with-tdsver = 7.1:

Installation Reference
Http://linux.chinaunix.net/techdoc/database/2008/10/31/1042291.shtml or: http://www.linuxdiyf.com/viewarticle.php? Id = 109086

FreeTDS Configuration
FreeTDS configuration file, FreeTDS also supports an old configuration file interfaces, but please use freetds. conf unless your environment must use interfaces. FreeTDS first finds the freetds. conf file. If not, it searches for the interfaces file. The freetds. conf file is in the/usr/local/freetds/etc directory by default, but you can configure the sysconfdir Option When configuring. This option is the directory in which the freetds. conf file exists. The freetds. conf configuration file is divided into two parts: one is [global] and the other is [dataserver]. [dataserver] corresponds to a database. The settings in golbal work for all databases, but the settings in dataserver only work for their own databases, and can overwrite the global settings.

Example: freetds. conf file:

[Global]
Tds version = 4.2
[Myserver]
Host = ntbox.mydomain.com
Port = 1433
[Myserver2]
Host = unixbox.mydomain.com
Port = 4000
Tds version = 5.0

In this file, global sets the tds version of all databases to 4.2, but the version used in myserver2 is 5.0. If myserver2 does not contain this item, it uses version 4.2, such as myserver.

The configuration items are described as follows:

Ltds version: indicates the version of the tds protocol used to connect to the database. If this option is not set in the environment variable, the configuration determines that the Protocol version can be 4.2, 5.0, 7.0, 8.0.

Lhost: the host name or IP address of the database server.

Lport: the listening port of the database server, which can take any valid port value. Generally, Sybase SQL10 used 5000 for 1433,10 and above, and Sybase SQLAnywhere 7 is 2638, for Microsoft SQL server, 1433 is used. This configuration can be rewritten by TDSPORT in the environment variable.

Linitial block size: This value can only be a multiple of 512. The default value is 512. the maximum value of the Protocol block is specified. Do not change the default value.

Ldump file: Any valid file name that specifies the path of the dump file and opens the log.

Ldump file append: yes or no to determine whether to append the file to the dump file.

Ltimeout: sets the maximum waiting time for processing.

Lconnect timeout: sets the maximum waiting time for a connection.

Lemulate little endian: yes or no, whether to force a large computer to communicate with the MS Server in a small way.

Lclient charset: Any valid iconv character set. The default value is a ISO-8859-1 that enables FreeTDS to use iconv to convert between the database server and the user program.

FreeTDS Functions
1. Dbcmd and dbfcmd

Function prototype: Dbcmd (DBPROCESS * proc, char * SQL );

Dbcmd (DBPROCESS * proc, char * format, char * args );

Function: this function is mainly used to construct an SQL statement, one with parameters and the other without parameters.

2. Dbsqlexec

Function prototype: Dbsqlexec (DBPROCESS * proc );

Skill: this function is responsible for executing the SQL statements you have constructed.

3. Dbresults

Function prototype: Dbrerults (DBPROCESS * proc );

Function: Obtain the execution result of the SQL statement. If the returned value is NO_MORE_RESULTS = 0, it indicates that the SQL query is null (that is, there is no result that meets the conditions). If it is (FAIL) =-1, it indicates that the query has an error. If it is (SUCCESS) = 1, indicating that there are results and not empty.

4. DBROWS (all uppercase)

Function prototype: DBROWS (DBPROCESS * proc );

Function: extract the information recorded in a row.

5. Dbbind

Function prototype: Dbbind (DBPROCESS * proc, int colmn,

Function: bind the SQL query result to a variable. The first parameter is the handle obtained from the database, and the second parameter corresponds to the required field in your select statement (Note: It must be bound in the select order, for example, select user, password from hist1, if the value is 1, it is the bound user), the third parameter is the type of the bound field, and the last parameter is the bound variable.

6. Dbnextrow

Function prototype: Dbnextrow (DBPROCESS * proc );

Function: This function extracts each row that meets the SQL statement. The returned value is 0, indicating that the processing is complete and the returned value is-1.

7. Dbcancel

Function prototype: Dbcancel (DBPROCESS * proc );

Function: clears the dataset obtained from the previous query. If it is a handle, you must call it to clear the result every time you re-execute the select statement. Otherwise, the database reports an error.

8. Dbclose

Function prototype: Dbclose (DBPROCESS * proc );

Function: Close the handle. The handle must be closed when it is no longer in use.

9. Dbinit

Function prototype: Dbinit ()

Skill: first recognized database connection. An error occurred while returning the value-1.

10. Dblogin

Function prototype: LOGINREC * Dblogin ();

DBSETLUSER (login, SOFT); // set the database user

DBSETLPWD (login, SOFTPASS); // set password

Function: connect to the database based on the user name and password.

11. Dbcount

Function prototype: Dbcount (DBPROCESS * proc );

Function: This function gets the number of rows processed by the SQL result set. You can use this function to determine whether your select statement is correctly processed.

12. Dbopen

Function prototype: DBPROCESS * Dbopen (LOGINREC * login, NULL );

Function: return the handle of a database operation.

In addition, we will introduce two functions for database error information:

Dberrhandle (int * err );

Dbmsghandle (int * err );

Instance code

# Include <stdio. h>
# Include <stdlib. h>
# Include <string. h>
# Include <unistd. h>
# Include <sqlfront. h>/* sqlfront. h always comes first */
# Include <sybdb. h>/* sybdb. h is the only other file you need */
# Define SQLDBIP "" // IP address of the SQL Database Server
# Define SQLDBPORT "" // SQL database server port
# Define SQLDBNAME "" // SQL Database Server Database Name
# Define SQLDBUSER "" // sqldatabase Server database username
# Define SQLDBPASSWD "" // sqldatabase Server user password
# Define sqldbserver sqldbip ":" SQLDBPORT
# Define DBSQLCMD "select * from yancao"
Int main (int argc, char * argv [])
{
Int I, ch;
LOGINREC * login; // describes the structure of the client, which is passed to the server during connection.
DBPROCESS * dbproc; // describes the connected struct, Which is returned by the dbopen () function.
RETCODE erc; // The most common return type in library functions.
/*************************************** **********************/
// You must call the dbinit () function before calling the function of this database.
If (dbinit () = FAIL ){
Fprintf (stderr, "% s: % d: dbinit () failed \ n", argv [0], _ LINE __);
Exit (1 );
}
// The dblogin () function applies for the LOGINREC struct, which is passed to the dbopen () function to create a connection.
// Check whether the call fails !.
If (login = dblogin () = NULL ){
Fprintf (stderr, "% s: % d: unable to allocate login structure \ n", argv [0] ,__ LINE __);
Exit (1 );
}
// The LOGINREC struct cannot be accessed directly. Use the following macro settings to set two necessary fields:
DBSETLUSER (login, SQLDBUSER );
DBSETLPWD (login, SQLDBPASSWD );
/*************************************** **********************/
// Dbopen () establishes a connection with the server. Pass the LOGINREC pointer and server name
If (dbproc = dbopen (login, SQLDBSERVER) = NULL ){
Fprintf (stderr, "% s: % d: unable to connect to % s as % s \ n ",
Argv [0], _ LINE __,
SQLDBSERVER, SQLDBUSER );
Exit (1 );
}
// You can call the dbuser () function to select the database name we use. You can omit it and use the default database.
If (SQLDBNAME & (erc = dbuse (dbproc, SQLDBNAME) = FAIL ){
Fprintf (stderr, "% s: % d: unable to use to database % s \ n ",
Argv [0], _ LINE __, SQLDBNAME );
Exit (1 );
}
/*************************************** **********************/
Dbcmd (dbproc, DBSQLCMD); // fill SQL statements in the Command Buffer
Printf ("\ n ");
If (erc = dbsqlexec (dbproc) = FAIL ){
Fprintf (stderr, "% s: % d: dbsqlexec () failed \ n", argv [0], _ LINE __);
Exit (1); // wait for the server to execute the SQL statement. The wait time depends on the query complexity.
}
/*************************************** **********************/
// Call the dbresults () function after dbsqlexec (), dbsqlok (), and dbrpcsend () are successfully returned.
Printf ("then fetch results: \ n ");
Int count = 0;
While (erc = dbresults (dbproc ))! = NO_MORE_RESULTS ){
Struct col {// save all column information
Char * name; // column name
Char * buffer; // column Data Pointer
Int type, size, status;
} * Columns, * pcol;
Int ncols;
Int row_code;
If (erc = FAIL ){
Fprintf (stderr, "% s: % d: dbresults failed \ n ",
Argv [0], _ LINE __);
Exit (1 );
}
Ncols = dbnumcols (dbproc); // Number of columns that return execution results
If (columns = calloc (ncols, sizeof (struct col) = NULL ){
Perror (NULL );
Exit (1 );
}
/* Read metadata and bind .*/
For (pcol = columns; pcol-columns <ncols; pcol ++ ){
Int c = pcol-columns + 1;
Pcol-> name = dbcolname (dbproc, c); // return the name of the specified Column
Pcol-> type = dbcoltype (dbproc, c );
Pcol-> size = dbcollen (dbproc, c );
Printf ("% * s (% d)", 20, pcol-> name, pcol-> size );
If (pcol-> buffer = calloc (1, 20) = NULL ){
Perror (NULL );
Exit (1 );
}
Erc = dbbind (dbproc, c, NTBSTRINGBIND, 20, (BYTE *) pcol-> buffer );
If (erc = FAIL ){
Fprintf (stderr, "% s: % d: dbbind (% d) failed \ n ",
Argv [0], _ LINE __, c );
Exit (1 );
}
Erc = dbnullbind (dbproc, c, & pcol-> status); // (5)
If (erc = FAIL ){
Fprintf (stderr, "% s: % d: dbnullbind (% d) failed \ n ",
Argv [0], _ LINE __, c );
Exit (1 );
}
}
Printf ("\ n ");
/* Print data */
While (row_code = dbnextrow (dbproc ))! = NO_MORE_ROWS) {// read row data
Switch (row_code ){
Case REG_ROW:
For (pcol = columns; pcol-columns <ncols; pcol ++ ){
Char * buffer = pcol-> status =-1?
"Null": pcol-> buffer;
Printf ("% * s", 20, buffer );
}
Printf ("\ n"); break;
Case BUF_FULL: break;
Case FAIL:
Fprintf (stderr, "% s: % d: dbresults failed \ n ",
Argv [0], _ LINE __);
Exit (1); break;
Default: // (7)
Printf ("data for computeid % d ignored \ n", row_code );
}
}
/* Free metadata and data buffers */
For (pcol = columns; pcol-columns <ncols; pcol ++ ){
Free (pcol-> buffer );
}
Free (columns );
If (DBCOUNT (dbproc)>-1)/* The number of rows affected by the SQL statement */
Fprintf (stderr, "% d rows affected \ n", DBCOUNT (dbproc ))
}
Dbclose (dbproc );
Dbexit ();
}

What is freetds?

Is a software that can use Linux and Unix to connect MS SQLServer and Sybase databases.

ODBC and freetds have been configured in linux, and you can use C ++ to connect to and operate databases. The teacher has another connection pool to teach prawns.

The connection pool is the configuration of ODBC!