Introduction, installation, configuration and use of freetds

Time:2021-9-23

What is freetds
In short, freetds is a program library that can access Microsoft’s SQL database under Linux system! Freetds is an open source (if you like, it can be called free) library. It is the re implementation of TDS (tabular data flow) protocol. It can be used in the DB 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 server. Freetds is released as source code and can be compiled on almost any operating system. It means UNIX and UNIX like systems (including famous branches such as interix and QNX), as well as Win32, VMS, and OSX.

Installation of freetds
1. Download freetds, click here to downloadhttps://www.jb51.net/database/201983.html

2. Unzip it to any directory and enter it into the unzipped folder.

3. Switch to root and configure:. / configure – prefix = / usr / local / freetds – with tdsver = 8.0 – enable msdblib explanation: – prefix is the installation directory of freetds, – with tdsver is the TDS version, and – enable msdblib is whether to allow Microsoft database function libraries

4.make & make install

5. Configure environment variable: VIM ~ /. Bashrc add: export LD to this file_ LIBRARY_ PATH=$LD_ LIBRARY_ PATH:/usr/local/freetds/lib/

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

Run. / TSQL   – C. You can find TSQL in the bin directory of the installation directory and check the information printed by the terminal. This – with tdsver = 7.1:

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

  Configuration of freetds
Freetds also supports an old configuration file interface, but please use freetds.conf unless your environment must use interfaces. Freetds first looks for the freetds.conf file. If not, it looks for the interfaces file. The freetds.conf file is in the / usr / local / freetds / etc directory by default, but the sysconfidir option can be configured during 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] part, and the other is the [dataserver] part, where [dataserver] corresponds to a database. The settings in golbal work for all databases, but the settings in the data server section only work for their own databases and can override the 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

In this file, global sets the TDS version for all databases to 4.2, but the version used in myserver2 is 5.0. If there is no such item in myserver2, it is version 4.2, such as Myserver.

The configuration items are explained as follows:

ltds version        : Indicates the version of the TDS protocol. It is used when connecting to the database. If this item is not set in the environment variable, it is determined by the configuration. 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 can take any valid port value. Generally speaking, Sybase sql10 used to be 1433, 5000 for more than 10, 2638 for SYBASE sqlanywhere 7 and 1433 for Microsoft SQL server. This configuration can be overridden by tdsport in the environment variable.

Linear block size: this value can only be a multiple of 512. The default value is 512. It specifies the maximum value of the protocol block. Generally, do not change this default configuration.

ldump file           : Any valid file name that indicates the path to the dump file and opens logging.

Ldump file append: Yes or no, which determines whether to append and save to dump 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, whether to force the large terminal to use the small terminal to communicate with the MS server.

lclient charset    : Any valid iconv character set. The default value is iso-8859-1, which enables freetds to use iconv to convert between the database server and the user program.

Freetds function
1. Dbcmd and dbfcmd

Function prototype: dbcmd (dbprocess * proc, char * SQL);

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

Merit       Yes: this function is mainly used to construct SQL statements, one with parameters and the other without parameters.

2. Dbsqlexec

Function prototype: dbsqlexec (dbprocess * proc);

Merit       Yes: this function is responsible for executing the SQL statement you constructed.

3. Dbresults

Function prototype: dbrerults (dbprocess * proc);

Merit       Can: get the execution result of SQL statement. Return value if no_ MORE_ Results = 0 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 is in error. If it is (success) = 1, it indicates that there is a result and it is not null.

4. Dbrows (all in words)

Function prototype: dbrows (dbprocess * proc);

Merit       Can: take out a line of recorded information.

5. Dbbind

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

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

6.    Dbnextrow

Function prototype: dbnextrow (dbprocess * proc);

Merit       Yes: this function will take out each line that meets the SQL statement, and the return value is 0, which means that the processing is completed, and the return value is – 1. There is an error.

7.  Dbcancel

Function prototype: dbancel (dbprocess * proc);

Merit       Yes: clear the dataset obtained from the last query. If it is a handle, call it to clear the result before re executing the select statement every time, otherwise the database will report an error.

8. Dbclose

Function prototype: dbclose (dbprocess * proc);

Merit       Can: close handle. The handle must be closed when it is no longer in use.

9.  Dbinit

Function prototype: dbinit()

Merit       Ability: first knowledge of database connection. Error with return value of – 1.

10. Dblogin

Function prototype: loginrec        * Dblogin();

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

             DBSETLPWD(login,SOFTPASS);//set password

Merit      Can: connect to the database according to the user name and password.

11.Dbcount

Function prototype: dbcount (dbprocess * proc);

Merit       Yes: this function will get the number of rows processed in the SQL result set. It can be used to judge whether your select statement has been processed correctly.

12.Dbopen

Function prototype: dbprocess * dbopen (loginrec)      * login,NULL);

Merit       Can: return a handle to operate the database.

In addition, two functions for database error information are introduced:

dberrhandle(int *err);

dbmsghandle(int* err);

Example 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 “” / / SQL database server IP
#Define sqldbport “” / / SQL database server port
#Define sqldbname “” / / SQL database server database name
#Define sqlbus “” / / 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 when connecting
    DBPROCESS *dbproc; // The structure describing the connection, which is returned by the dbopen() function
    RETCODE erc; // The most common return type in library functions
/*************************************************************/
//The dbinit () function is often called before calling this library function
    if (dbinit() == FAIL) {
        fprintf(stderr, “%s:%d: dbinit() failed\n”,argv[0], __LINE__);
        exit(1);
     }
//The dblogin() function applies for the loginrec structure, which is passed to the dbopen() function to create a connection.
//Although the call basically won’t fail, check it
    if ((login = dblogin()) == NULL) {
        fprintf(stderr, “%s:%d: unable to allocate login structure\n”,argv[0],__LINE__);
        exit(1);
    }
//Loginrec structure cannot be accessed directly. To set it through the following macro, set two essential fields below
    DBSETLUSER(login, SQLDBUSER);
    DBSETLPWD(login, SQLDBPASSWD);
/*************************************************************/
//Dbopen() establishes a connection with the server. Pass the loginrec pointer and the 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 user’s 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);// Populates 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 SQL statements. The waiting time depends on the complexity of the query.
    }
/*************************************************************/
//After calling dbsqlexec(), dbsqlok(), and dbrpcsend(), the dbresults() function is called
    printf(“then fetch results:\n”);
    int count = 0;
    while ((erc = dbresults(dbproc)) != NO_MORE_RESULTS) {
        Struct col {/ / save all the information of the column
        char *name; // Column name
        char *buffer; // Store 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); // Returns the column 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) / * get the number of rows affected by the SQL statement*/
fprintf(stderr, “%d rows affected\n”, DBCOUNT(dbproc))
}
dbclose(dbproc);
dbexit();
}