Translating JavaScript to Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Translating JavaScript to Stored Procedure

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-29 : 11:07:46

I thought the title would get you. Why is anyone translating JavaScript to a SP?

I'm tracking HTTP_USER_AGENT in a table. For the uninitiated, it's a non-standard string that hints (often wrongly) to the type of browser. It's the only info availaible.

I'd like to translate it to more understandable english, current scripts that do this are written in JavaScript - a lot of IF statements.

There's a good script for the purpose at
[url]http://www.xs4all.nl/~ppk/js/index.html?detect.html[/url]

Here's the JS Code:

var detect = navigator.userAgent.toLowerCase();
var OS,browser,version,total,thestring;

if (checkIt('konqueror'))
{
browser = "Konqueror";
OS = "Linux";
}
else if (checkIt('omniweb')) browser = "OmniWeb"
else if (checkIt('opera')) browser = "Opera"
else if (checkIt('webtv')) browser = "WebTV";
else if (checkIt('icab')) browser = "iCab"
else if (checkIt('msie')) browser = "Internet Explorer"
else if (!checkIt('compatible'))
{
browser = "Netscape Navigator"
version = detect.charAt(8);
}
else browser = "An unknown browser";

if (!version) version = detect.charAt(place + thestring.length);

if (!OS)
{
if (checkIt('linux')) OS = "Linux";
else if (checkIt('x11')) OS = "Unix";
else if (checkIt('mac')) OS = "Mac"
else if (checkIt('win')) OS = "Windows"
else OS = "an unknown operating system";
}

function checkIt(string)
{
place = detect.indexOf(string) + 1;
thestring = string;
return place;
}

So you ask: Where's the beef?

Two beefs.

Beef 1: the function checkit I want to eliminate it and code it all inline (performance).

Beef 2: detect.charAt(place + thestring.length); - * this is tricky *. The browser version *follows* the browser name like in the following 3 examples:

Gecko/20020823 Netscape/7.0
Mozilla/4.79 [en] (Windows NT 5.0; U)
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.0.3705)

Parsing the version number beyond MSIE to retrieve 6.0 - could it be done easily with pattern match in SQL?

Shaking it all out, here's the problem. Given a starting point in a string, what's the shortest amount of code that could isolate the "N.N" decimal number that follows??

I realize this is a long post for a small amount of info, but I wanted to answer questions up front about why I want to do this..

Thanks,

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-29 : 12:56:12

Javascript has Regular Expressions for pattern matching.

Take a look at these links.

http://www.webreference.com/js/column5/

http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_20227404.html

http://www.gemal.dk/browserspy/

Edited by - ValterBorges on 12/29/2002 13:07:19

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-29 : 16:06:23

Hi Valter,

Maybe you misunderstood? That or my post is confusing..

I wanted to find a way to write the same code efficently in SQL..

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-29 : 18:27:02

Well if you don't mind keeping track of unknown strings you could build yourself a table will all the different versions and then just use a select. If it's not found put it aside and add it to the table at some point.

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-29 : 18:55:18

Valter,
The number of browsers is reasonable (less than a hundred), and the solution would flag and store unrecognized strings for later identification.

There are some details that I can work out. For reasons I can't explain, the last few characters can be - "Installation specific" having client/ company information. So the match has to be against the length of bytes stored in the lookup table.

CREATE TABLE BrowserLookup (
useragent varchar (255) , -- The left unique chars from http_user_agent
browser varchar (20) , -- e.g. 'Netscape', 'Internet Explorer'
version varchar (10) -- The version: e.g. '4.79'
)

SELECT Browser, Version
FROM BROWSERLOOKUP
WHERE @myuser_agent LIKE ??

OK, is there an easy way to do this? @myuser_agent will generally have more characters than the Column useragent.

If not found, insert this whole string in the lookup table with browser type "unknown", and I can edit it at my leisure to add the browser and version.

Thanks,

Sam

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-29 : 19:27:08

Is this what you are trying to do.

CREATE TABLE #BrowserLookup (
useragent varchar (255) , -- The left unique chars from http_user_agent
browser varchar (20) , -- e.g. 'Netscape', 'Internet Explorer'
version varchar (10) -- The version: e.g. '4.79'
)

DECLARE @myuser_agent varchar(255)

INSERT INTO #BrowserLookup (useragent, browser, version) VALUES ('aaab', 'Netscape', '4.79')
INSERT INTO #BrowserLookup (useragent, browser, version) VALUES ('aaac', 'Netscape', '4.8')

SET @myuser_agent = 'aaabxxx'

SELECT Browser, Version
FROM #BrowserLookup
WHERE useragent = LEFT(@myuser_agent,LEN(useragent))

SET @myuser_agent = 'aaabyyy'

SELECT Browser, Version
FROM #BrowserLookup
WHERE useragent = LEFT(@myuser_agent,LEN(useragent))

DROP TABLE #BrowserLookup

Careful if the useragent1 is for example aaab and useragent2 is aaabb
and your @myuser_agent is aaab then you'll match both useragent1 and useragent2 unless you choose the one with smallest length by adding in a order by clause and top 1 or something similar.

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-29 : 20:07:50

I can solve the length descripancy if it occurs. Right now I'm thinking the lookup table strings will be shorter than the string I pass.

Thanks..................

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources