MS SQL Server

If you have an ODBC DSN (Data Source Name) created linking to a SQL Server, you are ready to connect to the SQL Server through the DSN with ODBC functions. There is no changes needed in the php.ini configuration file.

The tutorial script below shows you how to call odbc_connect() and odbc_close() to connect and disconnect to the SQL Server through the DSN name "ggl_SQL_SERVER":

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

if (!$con) {

print("There is a problem with SQL Server connection. ");

} else {

print("The SQL Server connection object is ready. ");

odbc_close($con);

}

?>

If you run this script and get this output: "The SQL Server connection object is ready", your PHP environment and ODBC connection to the SQL Server are working.

Note that 'sa' and 'GlobalGuideLine' used in this script are system administrator login name and password. You may use any other login name and password defined on the SQL Server.

If you are interested to know what DSN entries are available on your local machine, you can use odbc_data_source($con, SQL_FETCH_FIRST) and odbc_data_source($con, SQL_FETCH_NEXT) in a loop to list all DSN entries defined on your local machine. The tutorial script below shows a good example:

<?php

 $con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

 if (!$con) {

 print("There is a problem with the connection.
");

 } else {

 print("The ODBC connection object is ready.
");

 $list = odbc_data_source($con, SQL_FETCH_FIRST);

 while ($list) {

  foreach ($list as $key => $value) {

  print($key . " = " . $value . "
");

  }

  $list = odbc_data_source($con, SQL_FETCH_NEXT);

 }

 odbc_close($con); 

 }

?>

Once you have created an ODBC connection object, you can use the odbc_exec() function to send a SQL statement to the SQL Server linked to the connection object for execution.

Here is a simple PHP script that creates a new schema and a new table:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

# creating a new schema

$sql = 'CREATE SCHEMA fyi';

odbc_exec($con, $sql);

# creating a new table

$sql = 'CREATE TABLE fyi.center (name VARCHAR(80))';

odbc_exec($con, $sql);

odbc_close($con);

?>

If you run this script for the first time, it will execute those two statements correctly for you. But if you run it again, you will some warning messages:

Warning: odbc_exec(): SQL error: [Microsoft]

[ODBC SQL Server Driver][SQL Server]

There is already an object named 'fyi' in the database.,

SQL state S0001 in SQLExecDirect in C: estggl_center.php

on line 6

Warning: odbc_exec(): SQL error: [Microsoft]

[ODBC SQL Server Driver][SQL Server]

There is already an object named 'center' in the database.,

SQL state S0001 in SQLExecDirect in C: estggl_center.php

on line 10

The messages are very clear and easy to understand.

When you call odbc_exec() to execute a SQL statement, and the execution failed on the SQL Server, you can use odbc_error() and odbc_errormsg() to retrieve the error code and error messages.

The tutorial script below shows you a good example:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

# dropping a table

$sql = 'DROP TABLE fyi.center';

$res = odbc_exec($con, $sql);

if (!$res) {

print("Execution failed: ");

print(" State: ".odbc_error($con)." ");

print(" Error: ".odbc_errormsg($con)." ");

} else {

print("Execution was successful. ");

}

odbc_close($con);

?>

If you run this script for the first time, you will get this output:

Execution was successful.

If you run this script again, the SQL statement will fail on the SQL Server, and you will get:

Warning: odbc_exec(): SQL error: [Microsoft]

[ODBC SQL Server Driver][SQL Server]

Cannot drop the table 'fyi.center', because

it does not exist or you do not have permission.,

SQL state S0002 in SQLExecDirect in C: estggl_center.php

on line 6

Execution failed:

State: S0002

Error: [Microsoft][ODBC SQL

If you want to get a list of all columns in a table, you can use the odbc_columns() function, which can actually be used to list all columns in all tables and views in the database. The syntax of odbc_columns() is:

$result_set = odbc_columns($connection_object,

$qualifier, # database name for SQL Server

$schema, # schema name for SQL Server

$table_name, # table or view name for SQL Server

$column_name # column name

)

#- The returning result set contains 5 fields:

#- TABLE_QUALIFIER, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME

#- DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE, RADIX

#- NULLABLE, REMARKS

The schema, table_name and column_name arguments accept search patterns ( '%' to match zero or more characters and '_' to match a single character).

The tutorial example below shows you how to get a list of columns in a table called ggl_rates in the current database, GlobalGuideLineDatabase, which is hard coded in the DSN definition:

If you want to create a table in the database connected through a ODBC DSN, you can run the CREATE TABLE SQL statement using the odbc_exec() function, as shown in the following sample script:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

# creating a new table

$sql = "CREATE TABLE ggl_links ("

. " id INT NOT NULL"

. ", url VARCHAR(80) NOT NULL"

. ", notes VARCHAR(1024)"

. ", counts INT"

. ", time DATETIME"

. ")";

$res = odbc_exec($con, $sql);

if (!$res) {

print("Table creation failed with error: ");

print(odbc_error($con).": ".odbc_errormsg($con)." ");

} else {

print("Table ggl_links created. ");

}

odbc_close($con);

?>

If you want to get a list of all tables in the database, you can use the odbc_tables() function, which can actually be used to list all tables and views in the database. The syntax of odbc_tables() is:

$result_set = odbc_tables($connection_object,

$qualifier, # database name for SQL Server

$owner, # schema name for SQL Server

$name, # table or view name for SQL Server

$type # valid type names are TABLE and VIEW

)

#- The returning result set contains 5 fields:

#- TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,

#- REMARKS

The owner and name arguments accept search patterns ('%' to match zero or more characters and '_' to match a single character).

The tutorial example below shows you how to get a list of tables in the current database, GlobalGuideLineDatabase, which is hard coded in the DSN definition:

If the returning output of a query statement is captured in a result set object, you can use odbc_fetch_row() to loop through each row in the output.

The tutorial PHP script below shows you how to list tables in the database:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$sql = "SELECT * FROM sys.objects"

. " WHERE type_desc='USER_TABLE'";

$res = odbc_exec($con, $sql);

print("User Tables: ");

while (odbc_fetch_row($res)) {

print(" ".odbc_result($res,'name')." ");

}

odbc_free_result($res);

odbc_close($con);

?>

If you run this script, you will get something like:

User Tables:

ggl_rates

ggl_team

ggl_random

ggl_links_indexed

ggl_links

ggl_links_copy

tipBackup2

If you have a SQL statement that need to executed repeatedly many times with small changes, you can create a prepared statement object with parameters so it can be executed more efficiently.

There are two functions you need to use prepare and execute a prepared statement object:

$statement_object = odbc_prepare($connection,

$statement_string);

#- The $statement_string may have parameters represented

#- by "?".

$result_set = odbc_execute($statement_object $array);

#- The $array is used to supply values to parameters

#- defined in the statement object.

The tutorial PHP script below shows you how to insert 3 rows into a table with a prepared statement object with 2 parameters:

When you execute a SQL SELECT statement with the odbc_exec() function, you can capture the returning result with a result set object with the following syntax:

$result_set = odbc_exec($sql_statement);

#- The returning value could be a Boolean value FALSE,

#- if the execution failed.

Data rows and field values in the result set object can be retrieved using other ODBC functions as shown in the tutorial PHP script below:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$sql = 'SELECT GETDATE()';

$res = odbc_exec($con, $sql);

odbc_fetch_row($res);

$date = odbc_result($res,1);

print("Database current time: ". $date ." ");

odbc_close($con);

?>

If you run this script, you will get something like this:

Database current time: 2007-06-02 22:07:05.110

If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$sql = "INSERT INTO ggl_links (id, url) VALUES ("

. " 101, 'GlobalGuideLine.com')";

$res = odbc_exec($con, $sql);

if (!$res) {

print("SQL statement failed with error: ");

print(odbc_error($con).": ".odbc_errormsg($con)." ");

} else {

print("One data row inserted. ");

}

$sql = "INSERT INTO ggl_links (id, url) VALUES ("

. " 102, 'GlobalGuideLine.com')";

$res = odbc_exec($con, $sql);

print("One data row inserted. ");

odbc_close($con);

?>

If you run this script, two data rows should be inserted into the table. And you will get:

One data row inserted.

One data row inserted.

If want to insert rows into a table based on data rows from other tables, you can use a subquery inside the INSERT statement as shown in the following script example:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$sql = "INSERT INTO ggl_links"

. " SELECT id+1000, REVERSE(url), notes, counts, time"

. " FROM ggl_links";

$res = odbc_exec($con, $sql);

if (!$res) {

print("SQL statement failed with error: ");

print(odbc_error($con).": ".odbc_errormsg($con)." ");

} else {

print("Multiple rows inserted. ");

}

odbc_close($con);

If you run this script, the table should have 4 rows now. And you will get:

Multiple rows inserted

If you insert multiple rows with a single INSERT statement, you can use the odbc_num_rows() function to find out how many rows were inserted. odbc_num_rows($result_set) returns the number of affected rows based on the result set object returned by the last INSET, UPDATE or DELETE statement.

The following tutorial script shows you report back the number of rows inserted properly:

<?php

$con = odbc_connect('ggl_SQL_SERVER','sa','GlobalGuideLine');

$sql = "INSERT INTO ggl_links"

. " SELECT id+1000, REVERSE(url), notes, counts, time"

. " FROM ggl_links WHERE id > 1000";

$res = odbc_exec($con, $sql);

if (!$res) {

print("SQL statement failed with error: ");

print(odbc_error($con).": ".odbc_errormsg($con)." ");

} else {

$number_of_rows = odbc_num_rows($res);

print("$number_of_rows rows inserted. ");

}

odbc_close($con);

If you run this script, you should get:

2 rows inserted

Can you pass expressions to stored procedure parameters? The answer is yes.

When executing functions, input values can be written as expressions. But the resulting value data type must match the parameter. The tutorial exercise below shows you how input values should be specified:

CREATE FUNCTION Area(@radius REAL)

RETURNS REAL

AS BEGIN

RETURN 3.14*@radius*@radius;

END;

GO

-- Input value data matches the parameter

PRINT 'Area of a circle: '+STR(dbo.Area(1.5),9,3);

GO

Area of a circle: 7.065

-- Input value data does not match the parameter

PRINT 'Area of a circle: '+STR(dbo.Area('1.5'),9,3);

GO

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'Area'.

-- Expressions are allowed

PRINT 'Area of a circle: '+STR(dbo.Area(1.0+0.5),9,3);

GO

Area of a circle: 7.065

If a user defined function is created with parameters, you need pass values to those parameters when calling the function with one of two formats listed below:

expression... function_name(value_1, value_2, ... value_n)...

The tutorial exercise below shows you how to pass values to function parameters:

DROP FUNCTION Welcome;

GO

CREATE FUNCTION Welcome(@url VARCHAR(40))

RETURNS VARCHAR(40)

AS BEGIN

RETURN 'Welcome to '+@url;

END;

GO

PRINT 'Hi there, '+dbo.Welcome('GlobalGuideLine.com');

GO

Hi there, Welcome to GlobalGuideLine.com

PRINT 'Hi there, '+dbo.Welcome('GlobalGuideLine.com');

GO

Hi there, Welcome to GlobalGuideLine.com

SQL Server supports 2 categories of user defined functions based on their return modes:

1. Scalar-valued Functions - A function that returns a single value. Scalar-valued functions can be used in scalar expressions. Below are some scalar-valued functions:

PRINT GETDATE();

GO

May 19 2007 1:26PM

PRINT 'URL reversed: '+REVERSE('rendc.org');

GO

URL reversed: moc.enilediuglabolg

2. Table-valued Functions - A function that returns data in rows and columns like a table. Table-valued functions can be used in table expressions like the FROM clause of SELECT statements Below are some scalar-valued functions:

SELECT * FROM fn_helpcollations() WHERE name LIKE 'French_CI%'

GO

name

-------------------

French_CI_AI

French_CI_AI_WS

French_CI_AI_KS

French_CI_AI_KS_WS

French_CI_AS

French_CI_AS_WS

French_CI_AS_KS

French_CI_AS_KS_WS

(8 row(s) affected)

SQL Server supports two syntax of creating table-valued functions:

1. Inline Table-valued Functions - A table-valued function created with a single SELECT statement:

CREATE FUNCTION function_name(

@parameter_1 data_type,

@parameter_2 data_type,

...

@parameter_n data_type

)

RETURNS TABLE

AS

RETURN (select_statement);

2. Multi-statement Table-valued Functions - A table-valued function created with a local temporary table and a statement block:

CREATE FUNCTION function_name(

@parameter_1 data_type,

@parameter_2 data_type,

...

@parameter_n data_type

)

RETURNS @table_variable_name TABLE (

column_definition_list)

AS BEGIN

statement_1;

statement_2;

...

statement_n;

RETURN

END

To create an inline table-valued function, you need to use the "RETURNS TABLE" clause in the "CREATE FUNCTION" statement. There should be no function body, except for a RETURN statement with a SELECT subquery:

An inline table-valued function can be viewed as a select statement with parameters, see the example showing in this tutorial exercise:

USE GlobalGuideLineDatabase;

GO

CREATE FUNCTION Top_Links(@level INT)

RETURNS TABLE

AS

RETURN (SELECT * FROM ggl_links WHERE counts > @level);

GO

SELECT counts, id, url FROM Top_Links(999900) ORDER BY counts DESC;

GO

counts id  url

----------- ----------- -----------------------------------

999966 36470  dgqnv qd toqcoupuxortasdtzvc

999953 12292  qebmw ywe q kza wskxqns j

999943 6192  p o qi akk hk od 

999923 79161  kv g g

999920 19124  p zoio

999909 90930  xq x y r 

(6 row(s) affected)