Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.javascript > #16216 > unrolled thread

ADODB and SQL Server rowversion Columns

Back to article view | Back to comp.lang.javascript

Contents

  ADODB and SQL Server rowversion Columns Gene Wirchenko <genew@ocis.net> - 2012-09-27 21:00 -0700
    Re: ADODB and SQL Server rowversion Columns Ross McKay <au.org.zeta.at.rosko@invalid.invalid> - 2012-09-28 16:36 +1000
      Re: ADODB and SQL Server rowversion Columns Gene Wirchenko <genew@ocis.net> - 2012-09-28 10:38 -0700
        Re: ADODB and SQL Server rowversion Columns Gene Wirchenko <genew@ocis.net> - 2012-10-02 17:37 -0700
          Re: ADODB and SQL Server rowversion Columns Ross McKay <au.org.zeta.at.rosko@invalid.invalid> - 2012-10-03 20:53 +1000
            Re: ADODB and SQL Server rowversion Columns Gene Wirchenko <genew@ocis.net> - 2012-10-03 16:38 -0700
              Re: ADODB and SQL Server rowversion Columns Ross McKay <au.org.zeta.at.rosko@invalid.invalid> - 2012-10-04 12:02 +1000

#16216 — ADODB and SQL Server rowversion Columns

Dear JavaScripters:

     The gremlins are on the attack again.

     I am trying to add rowversion handling to my SQL Server database.

     I can request a row and get it sent to the Web server
(server-side JavaScript) using ADODB.  I can pick up all of the column
values (as I have done for some time) except for the rowversion.

     It does get sent from SQL Server.  Well, something is sent.  The
column name is correct in the recordset.  As to the value for that
column, all I can tell is that it is an object (according to typeof(),
but I am unable to get the value.  I am ultimately going to have to be
able to serialise the value and send it to the browser and receive it
back.

     I am unable to list the properties.  I have some temporary code
inside the loop where I tabularise the data.  Here is the relevant
code:

if (RS.Fields(i).Name!="RowTS")
              document.write("<td>"+RS.Fields(i)+"</td>");
else
 {
 document.write("<td>"+"type:"+typeof(RS.Fields(i))+"</td>");
 var oStr=new String("Hello, world!");
//***** var oTarget=oStr;
 var oTarget=RS.Fields(i);
 var PropList="";
 for (var PropName in oTarget)
  PropList+=", "+oTarget[PropName];
 alert("Properties: "+PropList);
 }

     RowTS is the rowversion column.

     PropList never gets appended to.  If I run the property listing
code on oStr, it works, and I see a list of the characters.

     What do I do with the RS.Field(i) object to serialise the
rowversion value?

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#16220

On Thu, 27 Sep 2012 21:00:29 -0700, Gene Wirchenko wrote:

>     I am trying to add rowversion handling to my SQL Server database.
>
>     I can request a row and get it sent to the Web server
>(server-side JavaScript) using ADODB.  I can pick up all of the column
>values (as I have done for some time) except for the rowversion.
>[...]
>     What do I do with the RS.Field(i) object to serialise the
>rowversion value?

Some data types supported by ADO just aren't supported by ASP. You could
mess around with bytes and sums and ADODB binary streams, or...

I suggest you cast it from rowversion to bigint in the select statement,
and then if you need to return it to the server and compare, cast it
back from bigint to rowversion.

e.g.

select a,b,c,convert(bigint, rv) as rv from example

update example set ... where a=@a and rv=convert(rowversion,@rv)
-- 
Ross McKay, Toronto, NSW Australia
"Let the laddie play wi the knife - he'll learn"
- The Wee Book of Calvin

[toc] | [prev] | [next] | [standalone]

#16232

On Fri, 28 Sep 2012 16:36:43 +1000, Ross McKay
<au.org.zeta.at.rosko@invalid.invalid> wrote:

>On Thu, 27 Sep 2012 21:00:29 -0700, Gene Wirchenko wrote:
>
>>     I am trying to add rowversion handling to my SQL Server database.
>>
>>     I can request a row and get it sent to the Web server
>>(server-side JavaScript) using ADODB.  I can pick up all of the column
>>values (as I have done for some time) except for the rowversion.
>>[...]
>>     What do I do with the RS.Field(i) object to serialise the
>>rowversion value?
>
>Some data types supported by ADO just aren't supported by ASP. You could
>mess around with bytes and sums and ADODB binary streams, or...

     I do wish it was more than just an object that I can not do
anything (that I know of) with.

>I suggest you cast it from rowversion to bigint in the select statement,
>and then if you need to return it to the server and compare, cast it
>back from bigint to rowversion.
>
>e.g.
>
>select a,b,c,convert(bigint, rv) as rv from example
>
>update example set ... where a=@a and rv=convert(rowversion,@rv)

     Thank you.

     I did not think of casting it in SQL Server.  After trying to
convert it in JavaScript, I gave up on the approach.  I just tried
casting within SQL Server, and a simple test script worked.

     However, IIUC, casting to bigint could cause a problem since
JavaScript does not have a 64-bit int so large values would be
corrupted.  Is my understanding correct?

     I will convert() to/from string instead, and that should deal
with that.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#16324

On Fri, 28 Sep 2012 10:38:49 -0700, Gene Wirchenko <genew@ocis.net>
wrote:

[snip]

>     Thank you.
>
>     I did not think of casting it in SQL Server.  After trying to
>convert it in JavaScript, I gave up on the approach.  I just tried
>casting within SQL Server, and a simple test script worked.
>
>     However, IIUC, casting to bigint could cause a problem since
>JavaScript does not have a 64-bit int so large values would be
>corrupted.  Is my understanding correct?
>
>     I will convert() to/from string instead, and that should deal
>with that.

     To follow up:

     "should" *is* such a lovely word, isn't it?

     The above worked except for two things:

  1) To convert from rowversion to hex string, you have to convert()
twice:
          convert(varchar(16),convert(varbinary(max),RowTS),2)
(Make the last parm 1 for a "0x" in front.)

  2) JavaScript or ADODB or both do not like varchar(max).  JavaScript
was displaying the value when I did a document.write of it, but I
could not access it with String functions.  Finally, I thought to
change what SQL Server returned from varchar(max) to varchar(16), and
that did it.

     Figuring this out was but a couple hours.  <SOB!>  Not the most
fun way to spend the afternoon.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#16335

On Tue, 02 Oct 2012 17:37:14 -0700, Gene Wirchenko wrote:

>     To follow up:
>
>     "should" *is* such a lovely word, isn't it?

one that I keep tripping over...

>     The above worked except for two things:
>
>  1) To convert from rowversion to hex string, you have to convert()
>twice:
>          convert(varchar(16),convert(varbinary(max),RowTS),2)
>(Make the last parm 1 for a "0x" in front.)

Aha! Yes, now that you mention that, I vaguely recall tripping over it
myself some years ago. Oh well, maybe you can blog about it somewhere
that it will be picked up by a search engine, for the next poor coder...

>  2) JavaScript or ADODB or both do not like varchar(max).  JavaScript
>was displaying the value when I did a document.write of it, but I
>could not access it with String functions.  Finally, I thought to
>change what SQL Server returned from varchar(max) to varchar(16), and
>that did it.

Probably ADODB likes it just fine, but it presents it to JS as a data
type that doesn't fit into the set of types that JS can handle easily.
Again, you could mess about with ADO streams to read the binary data and
return as a string, or... just pick an appropriately sized varchar() to
cast to as you've done.

Another thought is that ADODB can access SQL Server via a native OLE-DB
driver or via ODBC, and the OLE-DB driver provides better data type
support than the ODBC driver (or at least, that's how I remember it). So
if you're using ODBC, perhaps you should investigate using OLE-DB
connection strings instead and see how you go there. I specifically
recall that large text blobs ("clobs" -- ugh) were problematic in ODBC
but handled as strings via the native OLE-DB driver. 

Otherwise, you could have read that varchar(max) field using GetChunk(),
another handy way to lose an afternoon :)

>     Figuring this out was but a couple hours.  <SOB!>  Not the most
>fun way to spend the afternoon.

No, but that's what you get for using Classic ASP in 2012. Most modern
web scripting platforms handle such things very easily, or at least have
some functions that help you with the conversions, but not Classic ASP
(either JScript or VBScript). But it's available and functional and
still works, for now; it just likes to stick a leg out in front of your
leading foot occasionally :(

cheers,
Ross
-- 
Ross McKay, Toronto, NSW Australia
"Hold very tight please! Ting! Ting!" - Flanders and Swann

[toc] | [prev] | [next] | [standalone]

#16369

On Wed, 03 Oct 2012 20:53:40 +1000, Ross McKay
<au.org.zeta.at.rosko@invalid.invalid> wrote:

>On Tue, 02 Oct 2012 17:37:14 -0700, Gene Wirchenko wrote:

[snip]

>>  2) JavaScript or ADODB or both do not like varchar(max).  JavaScript
>>was displaying the value when I did a document.write of it, but I
>>could not access it with String functions.  Finally, I thought to
>>change what SQL Server returned from varchar(max) to varchar(16), and
>>that did it.
>
>Probably ADODB likes it just fine, but it presents it to JS as a data
>type that doesn't fit into the set of types that JS can handle easily.
>Again, you could mess about with ADO streams to read the binary data and
>return as a string, or... just pick an appropriately sized varchar() to
>cast to as you've done.

     I have done some more experimenting.  I can return a varchar up
to 8000 characters long.  Anything longer requires using varchar(max),
and whenever I do that, boom!

     Oddly enough,
          document.write("<td>"+RS.Fields(i)+"</td>");
works to display a varchar(max).  I have verified that the full string
is displayed (by using Notepad to count).

     But when I try stringising a varchar(max) value and assigning it
to a variable, I get that the length is 4 and the value "null".  Note
that is a string with the four characters "n" et al.  This is
regardless of the length, even short lengths (like 2) do not work.

     I have been unable to access the individual characters in
RS.Fields(i) nor to determine its properties.  The way that I convert
is
          var Work=new String(RS.Fields(i));
With or without the new, it is the same.

     Any other ideas, or do I have to live with the 8000 limit?

[snip]

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#16377

On Wed, 03 Oct 2012 16:38:47 -0700, Gene Wirchenko wrote:

>     I have done some more experimenting.  I can return a varchar up
>to 8000 characters long.  Anything longer requires using varchar(max),
>and whenever I do that, boom!

At which point it will become a character BLOB (clob, character large
object). No longer considered a string by ADO probably, although see my
previous comments about ODBC vs OLE-DB.

>     Oddly enough,
>          document.write("<td>"+RS.Fields(i)+"</td>");
>works to display a varchar(max).  I have verified that the full string
>is displayed (by using Notepad to count).
>
>     But when I try stringising a varchar(max) value and assigning it
>to a variable, I get that the length is 4 and the value "null".  Note
>that is a string with the four characters "n" et al.  This is
>regardless of the length, even short lengths (like 2) do not work.
>
>     I have been unable to access the individual characters in
>RS.Fields(i) nor to determine its properties.  The way that I convert
>is
>          var Work=new String(RS.Fields(i));
>With or without the new, it is the same.
>
>     Any other ideas, or do I have to live with the 8000 limit?

1) see previous comments about ODBC and OLE-DB drivers, as I reckon you
might be OK if you use an up-to-date native OLE-DB driver; what are you
using now to connect to the database?

2) for large binary or character objects, ADODB has a separate method
for pulling data in chunks: GetChunk()

http://msdn.microsoft.com/en-us/library/windows/desktop/ms681747(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/ms678200(v=vs.85).aspx

Here's an example in VBScript, which you should be able to translate
easily enough:
http://www.tek-tips.com/viewthread.cfm?qid=865789
-- 
Ross McKay, Toronto, NSW Australia
"If ye cannae see the bottom, dinnae complain if ye droon"
- The Wee Book of Calvin

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.javascript

csiph-web