9

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

var ws = new WebSocket("ws://127.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.

Alex
  • 5,674
  • 7
  • 42
  • 65
  • 7
    The WebSockets and MySQL communication protocol is different, hence the above won't work. [MySQL Protocol](http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Organization) != [WebSocket Protocol](http://www.whatwg.org/specs/web-socket-protocol/) – Ross Sep 06 '11 at 17:50
  • 3
    Which SQL server are you talking about? I don't know of any db server that supports WebSockets. Remember, WebSocket != Socket. – Hyangelo Sep 06 '11 at 17:50
  • 1
    @Hylangelo: this is the type of thing I am looking for, to my understanding, a websocket is the same thing as a normal socket ( eg I figure if I send the right hex to handshake etc, it should just work ) – Alex Sep 06 '11 at 17:54
  • Ross, the OP is talking about MS SQL not MySQL. :) – Nick Binnet Sep 06 '11 at 18:10
  • 1
    Having never played with it (and it being deprecated) are you looking for xml webservices in SQL Server? [SQL Server Authentication over SOAP](http://msdn.microsoft.com/en-us/library/ms180919.aspx) – billinkc Sep 06 '11 at 18:13
  • @Hyangelo: however it would be amazing if it did! – Richard H Sep 10 '11 at 19:29
  • Is this server side js, like with node? – Factor Mystic Sep 11 '11 at 23:36
  • 1
    As with many things Javascript-related: Just because you can, doesn't mean you should. – geofftnz Sep 11 '11 at 23:56

7 Answers7

7

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.

Jamey
  • 1,595
  • 9
  • 23
  • 23
    Not to mention the fact that exposing your SQL server to your browser clients directly would be, to put it mildly, a career-ending move. – Mark Sep 06 '11 at 19:45
4

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

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/

JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245
1

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.

oberstet
  • 21,353
  • 10
  • 64
  • 97
  • This is actually the closest anyone has come to what I was interested in all week. – Alex Sep 14 '11 at 22:12
  • There is one additionaly consideration (if you really want to give it a shot): between browser and proxy, it is WS. With WS, you have 2 kinds of payload: text (utf-8) or binary. Only very recently Chrome has added support for binary. The kanaka proxy is able to unwrap base64 encoded text messages into binary TCP. This works today with both Chrome and FF. A more efficient way is of course skipping this wrapping and work with binary WS messages. You need Chrome 15 to do that (today). And I don't know if the proxy has option to turn off base64 unwrapping (but would guess so). – oberstet Sep 15 '11 at 15:21
0

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.

Omar Al-Ithawi
  • 4,988
  • 5
  • 36
  • 47
0

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.

David Laberge
  • 15,435
  • 14
  • 53
  • 83
-1

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

Jaseem
  • 2,236
  • 6
  • 28
  • 35