handshaking a SQL server with Javascript

I want to try, as a learning excersise, get my javascript to chat to sql.

var ws = new WebSocket("ws://127.开发者_StackOverflow中文版0.0.1:1433");

doesn't seem to be a blocked port, so it should in theory work.

I am looking for a breakdown of how to handshake with the sql server, and chat with it.

A pointer in the right direction would be much appreciated

(or even a reason explaining why it wont work.)

I want to try this on Microsoft SQL 2008 R2.

---

MS SQL doesn't have a text based protocol to allow you to interface with it through telnet. You can use a web socket to determine of the target server is listening on 1433, but you're best bet for completing the login sequence is to use a sql client api.

---

SQL Server connections use the TDS protocol, which is documented in the Tabular Data Stream Protocol Specification. If you follow the protocol specification, consult the protocol examples and have a peek at the FreeTDS open source implementation you should be able to do a low level handshake and more, using basic sockets. However, there really really isn't any point in doing so besides an academic exercise. But the nail in the coffin is the WebSockets, which are not basic sockets.

The way to go is to expose the SQL Server database to the web using a web service interface preferable REST, possibly OData) and then consume this web service from your Javascript HTML5 application. Here is a good read: Creating an OData API for StackOverflow including XML and JSON in 30 minutes.

---

In HTML5, JavaScript can communicate directly to SQL with SQLite. Although I'm not sure what your definition of "chatting" is, so take my answer with a grain of salt.
http://html5doctor.com/introducing-web-sql-databases/

---

Despite the "Socket" in the name WebSockets, and despite the fact that WS runs on top of TCP (with a HTTP-based initial handshake), WS is not TCP. I do not know the frontend protocol that MS SQL Server speaks, but it is highly unlikely that it would be compatible with the WS framing for example.

What you could do is probably the following:

Browser <= WS => WS Proxy <= plain TCP => SQL Server

For the proxy, you might want to look at

https://github.com/kanaka/websockify

This baby allows you to communicate via WS to the proxy, and the proxy will unwrap the WS payload and make it into a plain TCP stream.

That way it should be possible to speak to SQL Server .. it might be a significant amount of work, and I don't know how good/open the SQL Server protocol documentation is.

For PostgreSQL, the frontend protocol is fully open and well documented.

Should it be unclear what I mean with above, I can go into more details .. or ping kanaka to ask what he thinks .. kanaka = author of the proxy and very active on WS anyway.

---

Typical SQL servers does not have direct HTTP Interface, i.e. does not allow browser to connect directly.
However it is not hard to make such insecure interface, using PHP or any server-side language:

<?php
$query = mysql_query($_REQUEST['q']);
$dbResult = exeute($query); 
// execute is an imaginary function, you can use any function/library you want
echo json_encode($dbResults);

Try MangoDB, it have an HTTP interface: Simple REST Api

You can make it more dynamic by adding server/user/database/password params, but it will be just more insecure, if the page was public.

---

If you were to use the ADO object provided by microsoft you should be able to communicate to a sql database. http://msdn.microsoft.com/en-us/library/ms681519(v=vs.85).aspx

I know you could "chat" with an sql database. The hick, I'm not sure you can do that in normal web browser. At work, we did it using hta and a in house MySQL server.

---

Wow ! dont feel bad buddy, You got i/o completely wrong. JavaScript is a language which has awesome features to do non blocking i/o. This code kills the entire spirit of it.

Any database code should look something like this in the end in js.

getRemoteData('remoteURL', callback(data){

    // Use your data here
});

There are good parts in the language.. learn to use it.

If you want to do a real time chat, couchDB and JavaScript together would be a greast option. Node.js is also brillinat. SQL is not the stuff for real time applications