Javascript to Microsoft SQL Server (AJAX request)

Question

I have a problem, how can i select data from my database (Microsoft SQL Server) from my javascript by an AJAX request. I know I need a "server language", but it seems that PHP cannot do this !

How can I do ?

Thank you !

• Manu: Is this a PHP based question or asp.net? because you have given tags of both php and asp.net
• Robby Cornelissen: Sure you can do this in PHP: php.net/manual/en/book.mssql.php
• Lucas S.: Thanks your for answer, this is both ! In fact, I don't know if it can be done in php or in asp.net(extreme beginner in asp.net, I've never touched this !)
• Manu: You simply need to call an AJAX function and then return data from server side back to client side.

Answer 1

PHP is a server side language. Drivers are created for thee PHP package that allow them to interface with several different types of database architecture systems. In this case, the SQL Server would be connected to through the sqlsrv drivers for PHP.

A simple query to the database looks like the following:

-- query.php --
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
 die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT * FROM Person";
$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
 die( print_r( sqlsrv_errors(), true));
}
if( sqlsrv_fetch( $stmt ) === false) {
 die( print_r( sqlsrv_errors(), true));
}
$name = sqlsrv_get_field( $stmt, 0);
echo $name; //maybe the name is "George"

This establishes the connection, and then attempts to query the database. As we're just retrieving one row, we use sqlsrv_fetch() to attempt to populate the $stmt variable. If it works, then we'll get $name as a return from the row at column with index 0. This will return the value of $name to the success function of our ajax call (as illustrated below)

The $.ajax() is simple. Figure out what element is going to fire the ajax call, then just do it..

$('element').on('click', function(e){
 e.preventDefault();
 $.ajax({
 url: 'query.php',
 type: 'GET',
 success: function(data){
 console.log(data); //will show George in the console
 //otherwise it will show sql_srv errors.
 }
 });
});

Resources

1. sqlsrv_connect()
2. sqlsrv_query()
3. $.ajax()

Answer 2

For connecting to SQL Server... You can use this code...

public function connect() {
 $dsn = "Driver={SQL Server};Server=xxxxx;Port=1433;Database=yyyy";
 $data_source='zzzz';
 $user='dbadmin';
 $password='password';
 // Connect to the data source and get a handle for that connection.
 $conn=odbc_connect($dsn,$user,$password);
 if (!$conn) {
 if (phpversion() < '4.0')
 {
 exit("Connection Failed: . $php_errormsg" );
 }
 else
 {
 exit("Connection Failed:" . odbc_errormsg() );
 }
 }
 return $conn;
}

Please note, here I have created a data source. This code is using ODBC as you can see. ;) And this connection is using Sql Authentication. Hope this helps...

Answer 3

Asp.net

Client Side Code

 $.ajax({
 url: "ViewData.aspx/GetTransitNameById",
 type: "POST",
 contentType: "application/json; charset=utf-8",
 dataType: "json",
 data: '{"Transitid":"' + id + '"}',
 success: function (result) {
 // You got the required data in result.d
 var finalresult = result.d;
 }
 });

Server Side Code

 [WebMethod]
 public static string GetTransitNameById(int Transitid)
 {
 string name = "";
 try
 {
 oohMonitoringManager om = new oohMonitoringManager();
 name = om.GetTransitNameByTransitId(Transitid);
 // here you got what you needed. 
 // name contains the data that you have to return back to the javascript ajax function
 }
 catch (Exception a1)
 { }
 return name;
 }