CODEWITHSUNDEEP
javascriptphpasp.netsql-serverajax
Lucas S.
Lucas S.

Reputation: 23

Javascript to Microsoft SQL Server (AJAX request)

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 !

Upvotes: 2

Views: 8348

Answers (3)

Manu
Manu

Reputation: 418

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;
    }

Upvotes: 0

techno
techno

Reputation: 192

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...

Upvotes: 0

Ohgodwhy
Ohgodwhy

Reputation: 50798

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()

Upvotes: 3

Related Questions