SQL Programming

SQL Programming

Trending Questions and Lessons

Follow 4,662 Followers

Ask a Question

Feed

All

All

Lessons

Discussion

Lesson Posted on 10 May IT Courses/SQL Programming

PLSQL- PROGRAM TO FIND THE GIVEN CHARACTER HOW MANY TIMES REPEATED

Bijaya Kumar Mantri

Having more than 10 years of real time experience in SQL , PL/SQL programming.

declare str varchar2(80):='&str'; ab char:='&ch'; i number; l number; c number; begin l:=length(str); for i in 1..l loop if (substr(str,i,1=ab) then c:=c+1; end if; end loop; dbms_output.put_line('legnth... read more

declare

      str varchar2(80):='&str';

      ab char:='&ch';

      i number;

      l number;

      c number;

begin

      l:=length(str);

      for i in 1..l loop

            if (substr(str,i,1=ab) then

                  c:=c+1;

            end if;

      end loop;

      dbms_output.put_line('legnth of the string   '||l);

      dbms_output.put_line('no.of times repeated   '||c);

end;

read less
Comments
Dislike Bookmark

Lesson Posted on 06 Feb IT Courses/SQL Programming

Difference Between Stored Procedure And Function

KJ Vinod Kumar

* Strong experience in SQL server * Msbi Including SSIS, SSRS.

Stored Procedures Functions Stored procedures may or may not return a value Function should return a value Stored procedure should be executed by ‘EXECUTE’ command. Functions should execute by using ‘SELECT’ statement. It will... read more

Stored Procedures

Functions

Stored procedures may or may not return a value

Function should return a value

Stored procedure should be executed by ‘EXECUTE’ command.

Functions should execute by using ‘SELECT’ statement.

It will allow DML (Insert ,Update,Delete) operations.

It won’t allow DML (Insert ,Update,Delete) operations

Store Procedure Can allow Input and Output (Upto 24000)parameters.

Function Can allow Input parameters(Up to1200),

it won’t support for output parameters.

For exception handling we can use ‘Try catch blocks’. We can Perform Error handling Using StoreProcedures.

We cant Perform error handling inside a Function.

Inside a stored procedure we can call all types of ‘Data types’

Inside a function we can’t call ‘Text,Ntext,Image’ data types.

In stored procedure we can call any of the SQL objects like tables, temporary tables, sub queries Etc…

We can’t use Temporary tables inside a function

We can’t  create Triggers and Views inside a stored procedures.

We can’t use Views  inside a function

We can call functions and Child stored procedures (32) inside a stored procedures.

We can’t call stored procedures inside a functions.

Code reusability available in stored procedures.

Code reusability not available in functions.

Stored procedures will prefer Differ name resolution.

Differ name Resolution will not be applicable for functions.

After creating a Stored procedures, it will  create an execution plan.

Function won’t create any execution plans.

Stored procedures can’t be used in Join clause

Function can be used in join clause as a result set.

We can use Table variables inside a Stored procedures.

Here also we can use Table variables inside a Functions.

 

read less
Comments
Dislike Bookmark

Lesson Posted on 26/10/2017 IT Courses/MS SQL/MS SQL Development IT Courses/SQL Programming

Derived Tables

CodePicks

I am proficient in MS SQL Development. With over 6 years of experience in SQL Development, I have trained...

Following are the signs those qualify a query to be a derived table: These are defined in FROM clause of an outer query. It is surrounded by parenthesis followed by AS clause to define the derived table name. A derived table is an example of a Sub Query that is used in the FROM clause of a SELECT... read more

Following are the signs those qualify a query to be a derived table:

  1. These are defined in FROM clause of an outer query.
  2.  It is surrounded by parenthesis followed by AS clause to define the derived table name.

A derived table is an example of a Sub Query that is used in the FROM clause of a SELECT statement to retrieve a set of records. A derived table is a virtual table that is created within the scope of a query.

Case Scenario:

Create a table for trainers:

CREATE TABLE CodePicksTrainer(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
JoiningDate DATE
)

Insert trainer details:

INSERT INTO CodePicksTrainer(FirstName, LastName, DateOfBirth, JoiningDate)
SELECT 'Vivek', 'Grover', '1989-09-01', '2017-10-01'
UNION ALL
SELECT 'Ujjwal', 'Grover', '1994-11-27', '2017-09-10'
UNION ALL
SELECT 'Sahil', 'Arora', '1988-09-28', '2016-05-01'

Create a table for batches taken by trainers:

CREATE TABLE CodePickBatches(
ID INT IDENTITY(1,1),
Name VARCHAR(50),
StartDate DATE,
TrainerID INT FOREIGN KEY REFERENCES CodePicksTrainer(ID)
)

Insert Batches Detail:

INSERT INTO CodePickBatches (Name, StartDate, TrainerID)
SELECT 'SQL Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Java Programming', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Vivek' AND LastName = 'Grover')
UNION ALL
SELECT 'Corel Draw', '2017-01-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Ujjwal' AND LastName = 'Grover')
UNION ALL
SELECT 'ASP.Net', '2016-06-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'SQL Programming', '2016-07-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')
UNION ALL
SELECT 'R Programming', '2017-04-01', (SELECT ID FROM CodePicksTrainer WHERE FirstName = 'Sahil' AND LastName = 'Arora')

Requirement – One of your client demands a report with detail in following format containing Trainer Name, Joining date, Date of Birth, Total number of batches being run by trainer.

Code Build: Our result relies firstly on CodePickBatches table from where we can do aggregation to find number of batches being run my each Trainer. Once, we find the number of batches being run my each trainer, then we can make a join with CodePickTrainer table to find the detail of each trainer as shown in Requirement section. So, it requires the use of a derived table or on the fly table having aggregated data.

 SELECT
T.LastName + ', '+ T.FirstName AS TrainerName,
T.DateOfBirth,
T.JoiningDate,
TS.TotalRunningBatches
FROM (
SELECT
COUNT(1) AS TotalRunningBatches, B.TrainerID
FROM CodePickBatches B
GROUP BY B.TrainerID
) AS TS
INNER JOIN CodePicksTrainer T ON T.ID = TS.TrainerID
ORDER BY TotalRunningBatches DESC

In above code, a derived table named TS is created which makes its join with CodePicksTrainer table to find expected data. Total running batches are calculated in derived table (TS) itself. (It is surrounded by round brackets and followed by AS clause).

read less
Comments
Dislike Bookmark

Looking for SQL Programming Training

Find best SQL Programming Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 16/09/2017 IT Courses/MS SQL IT Courses/MS SQL/MS SQL Administration IT Courses/MS SQL/MS SQL Certification +6 IT Courses/MS SQL/MS SQL General IT Courses/MS SQL/MS SQL Integration IT Courses/MS SQL/MS SQL Reporting IT Courses/MS SQL/MS SQL Development IT Courses/SQL Programming IT Courses/Microsoft Training/Microsoft BI (Business Intelligence) Tools/SQL Server less

Cursors In SQL Server

Redbush Technologies Pvt.Ltd.

At RedBush technologies,we specialize in providing training on Hadoop, Big Data, SQL DBA, SQL Developer,...

First thing first Usage of Cursors is not encouraged in SQL Server as they are slow. You may go with While loop if you need to iterate through a recordset. Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the... read more
First thing first Usage of Cursors is not encouraged in SQL Server as they are slow. You may go with While loop if you need to iterate through a recordset. Cursor is a database object to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.So this is a row by row operation instead of a set based operation.
 
 
SQL Server Cursor Components: Cursors include following components:
 
1) Declare statements: Declare variables used in the code block.
 
2) Set\Select statements: Initialize the variables to a specific value.
 
3) Declare Cursor statement: Populate the cursor with values that will be evaluated.
 
4) Open statement: Open the cursor to begin data processing.

5) Fetch Next statements: Assign the specific values from the cursor to the variables

Note: This logic is used for the initial population before the While statement and then again during each loop in the process as a portion of the While statement.

While statement: Condition to begin and continue data processing.

Begin...End statement: Start and end of the code block.
 
Close statement: Releases the current data and associated locks, but permits the cursor to be re-opened.

Deallocate statement: Destroys the cursor
 
Below is an example of a static cursor:
 
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR STATIC FOR
SELECT EmpID,EmpName,Salary from ContractEmployee
OPEN cur_emp
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
--FETCH ABSOLUTE 3 FROM cur_emp INTO @Id,@name,@salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
--FETCH RELATIVE 3 FROM cur_emp INTO @Id,@name,@salary
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
END
CLOSE cur_emp
DEALLOCATE cur_emp

 
read less
Comments
Dislike Bookmark

Lesson Posted on 23/08/2017 IT Courses/SQL Programming

Looping In Oracle SQL Using Level

Arun

I have more than 6 years of industry experience in Oracle sql plsql real time development and currently...

We will see about the syntax LEVEL in Oracle sql. If we want to loop through and print a set of values in SQL, LEVEL could come handy. Example query: SELECT 'hi', LEVEL FROM DUALCONNECT BY LEVEL <= 5; output: hi 1hi 2hi 3hi 4hi 5 CONNECT BY LEVEL syntax does the trick here of incrementing the... read more

We will see about the syntax LEVEL in Oracle sql. If we want to loop through and print a set of values in SQL, LEVEL could come handy.

Example query:

SELECT 'hi', LEVEL FROM DUAL
CONNECT BY LEVEL <= 5;

output:

hi 1
hi 2
hi 3
hi 4
hi 5

CONNECT BY LEVEL syntax does the trick here of incrementing the levels for looping till the value 5 is reached. So for above example, the SELECT columns will be fired 5 times as you can see in the output. The keyword LEVEL can be used in SELECT statement too, to print the current level, as you can see in the output.

There are so many interesting analytical applications of LEVEL in DUAL

SELECT SUBSTR('bala',1,LEVEL) LETTER FROM DUAL
CONNECT BY LEVEL <= LENGTH('bala');

output:

b
ba
bal
bala

SELECT SUBSTR('bala',LEVEL,1) LETTER FROM DUAL
CONNECT BY LEVEL <= LENGTH('bala');

output:

b
a
l
a

SELECT RPAD(SUBSTR('Bala',LEVEL,1), LENGTH('Bala') + 2 - level, '*')
FROM DUAL
CONNECT BY LEVEL <= LENGTH('Bala');

output

B****
a***
l**
a*

The requirement for below example is - convert Case (flipflop - upper to lower and viceversa) for given input. It can be achieved using different ways in Oracle. I have tried with LEVEL and LISTAGG. LISTAGG aggregates data. Please check for it online. I will add a post on LISTAGG shortly.

SELECT LISTAGG(changed,'') WITHIN GROUP (ORDER BY lvl) finally_changed
FROM
(
    SELECT lvl, CASE WHEN letter = UPPER(letter) THEN LOWER(letter)
    ELSE UPPER(letter) END changed
    FROM
    (
         SELECT LEVEL lvl,SUBSTR('bAlA',LEVEL,1) LETTER FROM DUAL
         CONNECT BY LEVEL <= LENGTH('bAlA')
    )
);

The query structure comprises of subqueries, please understand and run the queries starting from the inner most query.

The inner most query - query 3 is same as we have seen earlier - it splits given string into rows of letters.

The second query - query 2 has the main logic to convert Upper case to lower and vice versa using CASE.

The First main query does the regrouping process using LISTAGG function

Input: bAlA

output: BaLa

This is a basic post about LEVEL. Please add on or correct any mistakes. Welcome your suggestions. Thanks for your time.

read less
Comments
Dislike Bookmark

Lesson Posted on 23/08/2017 IT Courses/SQL Programming IT Courses/Oracle Training/Oracle Database/Oracle 11g DBA

Virtual (Derived) Column: Oracle 11g R1 (Part 1)

Arun

I have more than 6 years of industry experience in Oracle sql plsql real time development and currently...

VIRTUAL OR DERIVED COLUMN A nice feature introduced in Oracle 11gR1. Welcome to the practical analysis of various scenarios with virtual columns - introduced in 11gR1 are like normal table columns whose values are derived from other columns. Creating table with virtual column DROP TABLE t_virtual_test1... read more

VIRTUAL OR DERIVED COLUMN

A nice feature introduced in Oracle 11gR1. Welcome to the practical analysis of various scenarios with virtual columns - introduced in 11gR1 are like normal table columns whose values are derived from other columns. 

  • Creating table with virtual column

DROP TABLE t_virtual_test1 PURGE;
CREATE TABLE t_virtual_test1
(
salary NUMBER,
commission NUMBER,
total NUMBER GENERATED ALWAYS AS (salary + commission) VIRTUAL
);

  • Inserting values into virtual columns

INSERT INTO t_virtual_test1 (salary, commission) VALUES (10000,999);
COMMIT;

The third column (which is virtual/derived) is auto derived (not auto populated).

SELECT * FROM t_virtual_test1;

SALARY COMMISSION TOTAL
10000 999 10999

We will see difference between auto derivation and auto population in later sections. Actually, the above logic is same as deriving values in SELECT like

SELECT salary, commission, salary + commission total FROM t_virtual_test1;

We will try out different scenarios starting with the below INSERT. Any guess about the value in the virtual column TOTAL?

INSERT INTO t_virtual_test1 (salary, commission) VALUES (20000,NULL);
COMMIT;

SELECT * FROM t_virtual_test1;

SALARY COMMISSION TOTAL
Rs. 10000 null null
  • Data dictionary view

As a developer/DBA, it is important to get to know the data dictionary details corresponding to each new concept your are learning. For virtual columns, we can check the column DATA_DEFAULT in data dictionary USER_TAB_COLUMNS

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT
T_VIRTUAL_TEST1 SALARY NUMBER null
T_VIRTUAL_TEST1 COMMISSION NUMBER null
T_VIRTUAL_TEST1 TOTAL NUMBER "SALARY"+"COMMISSION"
  • Other scenarios

* Try updating the actual columns, and when you SELECT, the virtual column value will be derived based on the new/updated/latest values in the actual columns.

UPDATE t_virtual_test1
SET commission = 111;
COMMIT;

SELECT * FROM t_virtual_test1;

* Try giving your own value for derived column. You cannot insert value for derived columns because - it is always derived.


INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (10000,999,10999);

Read the error report carefully - you get so much info/clue from this report
always note the error code and error message
--ORA-54013:Insert operation disallowed on virtual columns

Same for UPDATE.

UPDATE t_virtual_test1 SET total = 4444 WHERE salary = 10000;

error - Update operation disallowed on virtual columns

* Is there any way to include derived column name in INSERT?

yes - we can use the DEFAULT keyword (DEFAULT - is a basic concept which we normally use in our DMLs to instruct Oracle to go by default settings given during table creation. Please search and learn more about default).

INSERT INTO t_virtual_test1 (salary, commission, total) VALUES (11,22,DEFAULT);
COMMIT;

Note: Using DEFAULT in UPDATE will not work for virtual columns

  • Variations in virtual column creation Syntax

Above points discussed gives an overview of Virtual column. Now we will look into the optional syntax keywords and what will happen if we ignore those keywords while virtual column creation

* We can omit the datatype of virtual column - datatype is auto set based on the expression given,       like (salary + commission) gives a NUMBER in below example

DROP TABLE t_virtual_test2 PURGE;
CREATE TABLE t_virtual_test2
(
salary NUMBER,
commission NUMBER,
total GENERATED ALWAYS AS (salary + commission) VIRTUAL
);

DESC t_virtual_test2

SALARY          NUMBER
COMMISSION NUMBER
TOTAL            NUMBER

 * GENERATED ALWAYS and VIRTUAL keywords are optional

DROP TABLE t_virtual_test3 PURGE;
CREATE TABLE t_virtual_test3
(
salary NUMBER,
commission NUMBER,
total AS (salary + commission)
);


INSERT INTO t_virtual_Test3 VALUES (10000,300,default);
COMMIT;

Keep you updated in next post.

I keep my posts designed in such a way that the contents are more program oriented. The program speaks volumes. I welcome your suggestions and reviews to update my style.

read less
Comments
Dislike Bookmark

Looking for SQL Programming Training

Find best SQL Programming Training in your locality on UrbanPro.

FIND NOW

Answered on 14/08/2017 IT Courses/SQL Programming

Amitesh Sri

SQL Server Database Trainer

Hi Komal, Try to command basic SQL statement first. Understand the very basic concepts of DDL , DML and TCL commands.. start with writing SELECT statement with WHERE clause , using aggregate functions , GROUP BY clause.. Once you get enough confidence in filtering . grouping data .. then focus on making... read more
Hi Komal, Try to command basic SQL statement first. Understand the very basic concepts of DDL , DML and TCL commands.. start with writing SELECT statement with WHERE clause , using aggregate functions , GROUP BY clause.. Once you get enough confidence in filtering . grouping data .. then focus on making different JOINS.. JOINS are the core of day to day real time work... after completing all these you your self would be clear to know that "what next". read less
Answers 4 Comments
Dislike Bookmark

Answered on 24/08/2017 IT Courses/SQL Programming

How can we delete tables with dependencies in SQL server?

Arpi N.

Trainer

TRUNCATE, DROP
Answers 1 Comments
Dislike Bookmark

Answered on 02/09/2017 IT Courses/SQL Programming

How do I connect SQL with c++?

Sumit Shaw

Senior Business Analyst

int main() { #define SQL_RESULT_LEN 240 #define SQL_RETURN_CODE_LEN 1000 //define handles and variables SQLHANDLE sqlConnHandle; SQLHANDLE sqlStmtHandle; SQLHANDLE sqlEnvHandle; SQLWCHAR retconstring[SQL_RETURN_CODE_LEN]; //initializations sqlConnHandle =... read more
int main() { #define SQL_RESULT_LEN 240 #define SQL_RETURN_CODE_LEN 1000 //define handles and variables SQLHANDLE sqlConnHandle; SQLHANDLE sqlStmtHandle; SQLHANDLE sqlEnvHandle; SQLWCHAR retconstring[SQL_RETURN_CODE_LEN]; //initializations sqlConnHandle = NULL; sqlStmtHandle = NULL; //allocations if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle)) goto COMPLETED; if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) goto COMPLETED; if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle)) goto COMPLETED; //output cout << "Attempting connection to SQL Server..."; cout << " "; //connect to SQL Server //I am using a trusted connection and port 14808 //it does not matter if you are using default or named instance //just make sure you define the server name and the port //You have the option to use a username/password instead of a trusted connection //but is more secure to use a trusted connection switch (SQLDriverConnectW(sqlConnHandle, NULL, (SQLWCHAR*)L"DRIVER={SQL Server};SERVER=ServerAddress, 1433;DATABASE=DataBaseName;UID=DataBaseUserName;PWD=PassWord;", //(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;Trusted=true;", SQL_NTS, retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT)) { case SQL_SUCCESS: cout << "Successfully connected to SQL Server"; cout << " "; break; case SQL_SUCCESS_WITH_INFO: cout << "Successfully connected to SQL Server"; cout << " "; break; case SQL_INVALID_HANDLE: cout << "Could not connect to SQL Server"; cout << " "; goto COMPLETED; case SQL_ERROR: cout << "Could not connect to SQL Server"; cout << " "; goto COMPLETED; default: break; } //if there is a problem connecting then exit application if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle)) goto COMPLETED; //output cout << " "; cout << "Executing T-SQL query..."; cout << " "; //if there is a problem executing the query then exit application //else display query result if (SQL_SUCCESS != SQLExecDirectW(sqlStmtHandle, (SQLWCHAR*)L"SELECT @@VERSION", SQL_NTS)) { cout << "Error querying SQL Server"; cout << " "; goto COMPLETED; } else { //declare output variable and pointer SQLCHAR sqlVersion[SQL_RESULT_LEN]; SQLINTEGER ptrSqlVersion; while (SQLFetch(sqlStmtHandle) == SQL_SUCCESS) { SQLGetData(sqlStmtHandle, 1, SQL_CHAR, sqlVersion, SQL_RESULT_LEN, &ptrSqlVersion); //display query result cout << " Query Result: "; cout << sqlVersion << endl; } } //close connection and free resources COMPLETED: SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle); SQLDisconnect(sqlConnHandle); SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle); SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle); //pause the console window - exit when key is pressed cout << " Press any key to exit..."; getchar(); } read less
Answers 1 Comments
Dislike Bookmark

Looking for SQL Programming Training

Find best SQL Programming Training in your locality on UrbanPro.

FIND NOW

Answered on 02/09/2017 IT Courses/SQL Programming

How do I connect to a MS SQL database with C++?

Sumit Shaw

Senior Business Analyst

#include "stdafx.h" #include "iostream" #include "windows.h" #include "sqltypes.h" #include "sql.h" #include "sqlext.h" using namespace std; int main() { #define SQL_RESULT_LEN 240 #define SQL_RETURN_CODE_LEN 1000 //define handles and variables SQLHANDLE sqlConnHandle; ... read more
#include "stdafx.h" #include "iostream" #include "windows.h" #include "sqltypes.h" #include "sql.h" #include "sqlext.h" using namespace std; int main() { #define SQL_RESULT_LEN 240 #define SQL_RETURN_CODE_LEN 1000 //define handles and variables SQLHANDLE sqlConnHandle; SQLHANDLE sqlStmtHandle; SQLHANDLE sqlEnvHandle; SQLWCHAR retconstring[SQL_RETURN_CODE_LEN]; //initializations sqlConnHandle = NULL; sqlStmtHandle = NULL; //allocations if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlEnvHandle)) goto COMPLETED; if (SQL_SUCCESS != SQLSetEnvAttr(sqlEnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) goto COMPLETED; if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_DBC, sqlEnvHandle, &sqlConnHandle)) goto COMPLETED; //output cout << "Attempting connection to SQL Server..."; cout << " "; //connect to SQL Server //I am using a trusted connection and port 14808 //it does not matter if you are using default or named instance //just make sure you define the server name and the port //You have the option to use a username/password instead of a trusted connection //but is more secure to use a trusted connection switch (SQLDriverConnectW(sqlConnHandle, NULL, (SQLWCHAR*)L"DRIVER={SQL Server};SERVER=ServerAddress, 1433;DATABASE=DataBaseName;UID=DataBaseUserName;PWD=PassWord;", //(SQLWCHAR*)L"DRIVER={SQL Server};SERVER=localhost, 1433;DATABASE=master;Trusted=true;", SQL_NTS, retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT)) { case SQL_SUCCESS: cout << "Successfully connected to SQL Server"; cout << " "; break; case SQL_SUCCESS_WITH_INFO: cout << "Successfully connected to SQL Server"; cout << " "; break; case SQL_INVALID_HANDLE: cout << "Could not connect to SQL Server"; cout << " "; goto COMPLETED; case SQL_ERROR: cout << "Could not connect to SQL Server"; cout << " "; goto COMPLETED; default: break; } //if there is a problem connecting then exit application if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, sqlConnHandle, &sqlStmtHandle)) goto COMPLETED; //output cout << " "; cout << "Executing T-SQL query..."; cout << " "; //if there is a problem executing the query then exit application //else display query result if (SQL_SUCCESS != SQLExecDirectW(sqlStmtHandle, (SQLWCHAR*)L"SELECT @@VERSION", SQL_NTS)) { cout << "Error querying SQL Server"; cout << " "; goto COMPLETED; } else { //declare output variable and pointer SQLCHAR sqlVersion[SQL_RESULT_LEN]; SQLINTEGER ptrSqlVersion; while (SQLFetch(sqlStmtHandle) == SQL_SUCCESS) { SQLGetData(sqlStmtHandle, 1, SQL_CHAR, sqlVersion, SQL_RESULT_LEN, &ptrSqlVersion); //display query result cout << " Query Result: "; cout << sqlVersion << endl; } } //close connection and free resources COMPLETED: SQLFreeHandle(SQL_HANDLE_STMT, sqlStmtHandle); SQLDisconnect(sqlConnHandle); SQLFreeHandle(SQL_HANDLE_DBC, sqlConnHandle); SQLFreeHandle(SQL_HANDLE_ENV, sqlEnvHandle); //pause the console window - exit when key is pressed cout << " Press any key to exit..."; getchar(); } read less
Answers 1 Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best SQL Programming Training in India. Post Your Requirement today and get connected.

Overview

Questions 154

Lessons 15

Total Shares  

Top Contributors

Connect with Expert Tutors & Institutes for SQL Programming

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more