Introduction
This article provides the fundamentals concepts of SQL APIs . I hope this article will help you.
What is SQL-API?
A SQL-API can be used to access either the local SQL database server or a remote SQL database server. Local means that the client application and the database engine or server runs on the same machine and remote means that they run on a different machine or machines.
When DBMS supports a function call interface, an application program communicates with the the DBMS exclusively through a set of calls that are collectively known as an Application Program Interface (API).
The basic operation of a typical DBMS API is as specified below:
- The Program begins it's database access with one or more API calls that connect the program to the DBMS and often to a specific database or schema.
- To send a SQL statement to the DBMS, the program builds the statement as a text string in a buffer and then makes an API call to pass the buffer contents to the DBMS.
- The program makes API calls to check the status of it's DBMS request and to handle errors.
- If the request is a query, the program uses API calls to retrieve the query results into the program's buffers. Typically, the calls return data a row at a time or a column at a time.
- The program ends its database access with an API call that disconnects it from the DBMS.
![]()
When to use SQL API
An SQL-API is often used when the application program and the database are on two differnet systems in a client/server architecture. In this configuration, the code that is used for the API function is located on the client system, where the application program executes.The DBMS software is located on the server system where the database resides.
It calls from the application program to the API code then translates the calls into messages that it sends to and receives from the DBMS over a network.
In the remote or server configuration there are communication libraries on both the client and server machines. The communication libraries provide network protocol-specific support the client can use for communication purposes.
What is dblib API(SQL Server) ?
The original SQL Server API is called the database library or dblib. The dblib contains about 100 functions in the Client application Program that is used to communicate with the server/Remote Machine.
The following are some of the basic dblib API functions.
1.DatabaseConnection or Disconnection functions:
DbLogin(): It is used for providing information about Login details.
Dbopen(): It is used for opening a connection for the SQL Server.
Dbuse(): It is used to establish the default database.
Dbexit(): It is used for closing the connection for SQL Server.
2.Statement Processing functions:
Dbcmd(): It is used to push sqlstatement text to the dblib.
Dbsqlexec(): It is used to request execution of a statement batch process.
Dbresults(): It is used to obtain the results of a SQL statement in a batch.
Dbcancel(): It is used to cancel the execution of a SQL statement in a batch.
3.Query Results Processing functions:
Dbbind(): It is used to bind the query results column to the program.
Dbnextrow(): It is used to get the next row of query results.
Dbnumcols(): It is used to get the number of columns obtained by the query result.
Dbcolname(): It is used to obtain the name of the query results column.
Dbcoltype(): It is used to obtain the datatype of the column.
Dbcanquery(): It is used to cancel the query execution.
Summary
In this article I attempted to provides a basic introduction to the SQL-API. I hope after reading this article you will be are familiar with the SQL-API. I would like to have feedback from my readers. Please post your feedback, questions, or comments about this article.