FreeTDS Introduction, installation, configuration and use _mssql

What is FreeTDS
Simply put, FreeTDS is a library that enables access to Microsoft's SQL database under Linux systems! FreeTDS is an open source (if you prefer to be called free) of the library, is the TDS (table column data stream) protocol is implemented again. It can be used in Sybase's Db-lib or ct-lib libraries. It also contains a library of ODBC. 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 published in the form of a source code and can be compiled on almost any operating system. means Unix and Unix-like systems (including famous branches like Interix and QNX), as well as Win32,vms, and OS X.

Installation of FreeTDS
1. Download FreeTDS, click here to download http://www.jb51.net/database/201983.html

2. Extract it to any directory, into the unpacked folder.

3. Switch to root, configure:./configure–prefix=/usr/local/freetds–with-tdsver=8.0–enable-msdblib Explanation: –prefix for setting FreeTDS installation directory, – With-tdsver is to set the TDS version, –enable-msdblib to allow the Microsoft Database function library

4.make & make Install

5. Configure environment variables: Vim ~/.BASHRC Add to this file: Export ld_library_path= $LD _library_path:/usr/local/freetds/lib/

FreeTDS test:
FreeTDS installed, then you can view the next FreeTDS state;

Run./tsql-c, in the installation directory in the bin directory can find tsql, view the terminal print information, this-with-tdsver=7.1:

About installation References
Http://linux.chinaunix.net/techdoc/database/2008/10/31/1042291.shtml or: http://www.linuxdiyf.com/viewarticle.php?id=109086

Configuration of FreeTDS
FreeTDS configuration file, FreeTDS also supports an old profile interfaces, but please use freetds.conf unless your environment must use interfaces. FreeTDS First Find freetds.conf file if not found before to find interfaces file. The freetds.conf file defaults to the/usr/local/freetds/etc directory, but you can configure the SYSCONFDIR option when configure, which is the directory where the freetds.conf file exists. The freetds.conf configuration file is divided into two parts: one is the [Global] section and the other is the [DataServer] section, where [DataServer] corresponds to a database. The settings in Golbal work for all databases, but the settings in the DataServer section work on their own databases and can override global settings.

For 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

The global setting in this file uses TDS version 4.2, but the version used in Myserver2 is 5.0, and if Myserver2 does not have one, it is in 4.2 versions, such as MyServer.

The configuration items are explained as follows:

Ltds Version: Indicates the versions of the TDS protocol, which are used when connecting to the database, and if this is not set in the environment variable, the protocol version is preferred 4.2,5.0,7.0,8.0.

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

Lport: The database server's listening port, can take any valid port value, in general Sybase SQL10 formerly 1433, 10 more than 5000, and Sybase sqlanywhere 7 is 2638,microsoft SQ The L server uses 1433. This configuration can be overwritten by the tdsport in the environment variable.

Linitial Block Size: This value can only take multiples of 512, the default is 512, specifies the maximum value of the protocol blocks, and generally do not change this default configuration.

Ldump file: Any valid filename that indicates the path to the dump file and opens the log record.

Ldump file Append:yes or no to decide whether to append the save to the dump file file.

Ltimeout: Sets the maximum wait time for processing.

Lconnect Timeout: Sets the maximum wait time for a connection.

Lemulate little Endian:yes or no, forcing the big end machine to communicate with MS Server using small ends.

Lclient CharSet: Any valid iconv character set. The default value is Iso-8859-1, which enables FreeTDS to convert between the database server and the user program using Iconv.

FreeTDS function
1. Dbcmd and Dbfcmd

Function prototype: dbcmd (DBPROCESS *proc,char * sql);

dbcmd (Dbprocess *proc, char * Format,char *args);

Function: This function mainly constructs the SQL statement, one is with the parameter, one does not take the parameter.

2. dbsqlexec

Function prototype: dbsqlexec (Dbprocess *proc);

Function: This function is responsible for executing the SQL statement you have constructed.

3. dbresults

Function prototype: dbrerults (Dbprocess *proc);

Function: Get the execution result of SQL statement. The return value if No_more_results=0, indicates that the SQL query is null (that is, there is no result that satisfies the condition), if (FAIL) =-1, the query is faulted, if (SUCCESS) = 1, the result is not empty.

4. Dbrows (All caps)

Function prototype: dbrows (Dbprocess *proc);

Function: Take out a row of information.

5. Dbbind

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

Function: Bind the results of the SQL query to a variable. The first parameter is the handle taken from the database, and the second is the field that corresponds to the query in your SELECT statement (note: Must be bound in the select order, such as select User,password from Hist1, if the value is 1, 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 will take out every row that satisfies the SQL statement, the return value is 0, and the return value is-1 error.

7. Dbcancel

Function prototype: dbcancel (Dbprocess *proc);

Function: Empty the last query from the data set, if it is a handle, each time you rerun the SELECT statement to call it empty the results, otherwise the database will be an error.

8. Dbclose

Function prototype: dbclose (Dbprocess *proc);

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

9. Dbinit

Function prototype: Dbinit ()

Function: Initial recognition of the database connection. Error return value of-1.

10. Dblogin

Function prototype: Loginrec *dblogin ();

Dbsetluser (Login,soft); Set the database user

Dbsetlpwd (login,softpass);//set password

Function: Connect the database based on user name and password.

11. Dbcount

Function prototype: Dbcount (Dbprocess *proc);

Function: This function will get the number of rows processed by the SQL result set, which can be used to determine if your SELECT statement is handled correctly.

12. Dbopen

Function prototype: DBPROCESS * dbopen (loginrec *login,null);

Function: Returns a handle to an operation database.

Another two functions about database error information are introduced:

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
#include <sybdb.h>/* Sybdb.h is the only other file for you need * *
#define SQLDBIP ""//sql database server IP
#define SQLDBPORT ""//sql Database Server port
#define SQLDBNAME ""//sql Database server database name
#define SQLDBUSER ""//sql database server database user name
#define SQLDBPASSWD ""//sql Database 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 at the time of the connection.
DBPROCESS *dbproc; Describes the connected structure, which is returned by the dbopen () function
RETCODE ERC; The most common return type in a library function.
/*************************************************************/
You often call the Dbinit () function before you start calling this library function
if (dbinit () = = FAIL) {
fprintf (stderr, "%s:%d:dbinit () failed\n", argv[0), __line__);
Exit (1);
}
The dblogin () function requests the LOGINREC structure, which is passed to the dbopen () function to create a connection.
Although it's basically not a call to fail, check it!.
if (login = Dblogin ()) = = NULL) {
fprintf (stderr, "%s:%d:unable to allocate login structure\n", argv[0],__line__);
Exit (1);
}
The LOGINREC structure cannot be accessed directly, and for the following macro settings, two essential domains are set below
Dbsetluser (login, Sqldbuser);
Dbsetlpwd (login, sqldbpasswd);
/*************************************************************/
Dbopen () establishes a connection to the server. Passing LOGINREC pointers and server names
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 name of the database we use, you can omit it, omit it, and use the user default database.
if (sqldbname && (ERC = Dbuse (dbproc, sqldbname)) = = FAIL) {
fprintf (stderr, "%s:%d:unable to" database%s\n ",
Argv[0], __line__, sqldbname);
Exit (1);
}
/*************************************************************/
dbcmd (dbproc, dbsqlcmd);//populate the command buffer with SQL statements
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, and the wait time depends on the complexity of the query.
}
/*************************************************************/
Call the dbresults () function after the call to Dbsqlexec (), dbsqlok (), Dbrpcsend () returns success
printf ("Then fetch results:\n");
int count = 0;
while ((ERC = dbresults (dbproc))!= no_more_results) {
struct COL {//Save all the information for the column
Char *name; Column Name
Char *buffer; Storing column data pointers
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);//return number of columns executing results
if ((columns = calloc (ncols, sizeof (struct)) = = 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); Returns the column name of the specified column
Pcol->type = Dbcoltype (dbproc, C);
Pcol->size = Dbcollen (dbproc, C);
printf ("%*s (%d)", Pcol->name, pcol->size);
if ((Pcol->buffer = calloc (1)) = = NULL) {
Perror (NULL);
Exit (1);
}
ERC = Dbbind (dbproc, C, Ntbstringbind, (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", 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)/* Gets the number of rows affected by the SQL statement * *
fprintf (stderr, "%d rows affected\n", Dbcount (dbproc))
}
Dbclose (dbproc);
Dbexit ();
}