How big is an nvarchar(max) as far as ADO is concerned?

Question

i am trying to use a parameterized query against ADO:

INSERT INTO Foo (Name, Value) VALUES(@name, @value)

In SQL Server the Name column is a varchar type. The Value column is an nvarchar(max).

What size do i pass when creating a parameter when i don't know, or want to specify, the size?

procedure SaveTheThing(Connection: TADOConnection);
var
   sql: WideString;
   cmd: _Command;
begin
   sql := 'INSERT INTO Foo (Name, Value) VALUES(@name, @value)';

   cmd := CoCommand.Create;
   cmd.Set_ActiveConnection(Connection.ConnectionObject);
   cmd.Set_CommandType(adCmdText);
   cmd.Set_CommandText(sql);

   //and now add the parameters
   cmd.Parameters.Append(
         cmd.CreateParameter('@name', adVarChar, adParamInput, -1, filename)
   );
   cmd.Parameters.Append(
         cmd.CreateParameter('@value', adVarWChar, adParamInput, -1, GetXmlToWideString)
   );

   cmd.Execute({out}recordsAffected, EmptyParam, adCmdSomeThatDoesntCauseAnExcetpion or adExecuteNoRecords);
end;

The simple alternative was going to be:

sql := 'INSERT INTO Foo (Name, Value)'#13#10+
       'VALUES (+QuotedStr(filename)+', '+QuotedStrW(GetXmlToWideString)+')';

and be done already. But i thought i'd burn a few days trying to make parameterized queries a viable solution, and avoid having to write a QuotedStrW.

You can store up to 2 GB in a `nvarchar(max)` column - so that would be the number you need to use — marc_s, May 23 '12 at 16:09
What issues do you have when you uses the -1 size? any error message? ask because i use -1 wihout problems in nvarchar columns. — RRUZ, May 23 '12 at 16:37
@RRUZ i'll tell you how it works [once i can get it working](http://stackoverflow.com/questions/10725222/must-declare-the-variable-myvariable-error-with-ado-parameterized-query). — Ian Boyd, May 23 '12 at 17:48
@RRUZ `-1` *seems* to not cause an error. Put that in the form of an answer and you'll win an accept. — Ian Boyd, May 23 '12 at 18:28

score 6 · Accepted Answer · answered May 23 '12 at 18:34

You can use the -1 value in the size of a ADO parameter without problems.

Try this sample code , which insert a 2MB string in the Value column

var
   sql: WideString;
   cmd: _Command;
   recordsAffected : OleVariant;
begin
   sql := 'INSERT INTO Foo (Name, Value) VALUES(?, ?)';
   cmd := CoCommand.Create;
   cmd.Set_ActiveConnection(Connection.ConnectionObject);
   cmd.Set_CommandType(adCmdText);
   cmd.Set_CommandText(sql);

   //and now add the parameters
   cmd.Parameters.Append(cmd.CreateParameter('@name', adVarChar, adParamInput, -1, 'AfileName'));
   cmd.Parameters.Append(cmd.CreateParameter('@value', adVarWChar, adParamInput, -1, StringOfChar('#', 2*1024*1024)));
   cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);
end;

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

You can use my answer on your other post:

"Must declare the variable @myvariable" error with ADO parameterized query

to use parameters and parameter by name. Try to avoid using _Command, but use TADOCommand because it is more friendly (and simpler to code too). You can assign parameter to a value by using:

Parameters.ParamByName('xxxx').value := someValue.

Of course someValue data type must correspond to your SQL server column data type definition.

edited May 23 '17 at 12:04

Community

1
1

answered May 24 '12 at 10:28

Hendra

720
4
8

Unfortunately Delphi's [`TADOCommand` object doesn't support unicode strings](http://stackoverflow.com/questions/10740696/how-to-parameterize-widestrings-using-tadocommand-parameterized-query). Which was the only reason i have to use parameters. – Ian Boyd May 24 '12 at 15:37

How big is an nvarchar(max) as far as ADO is concerned?

2 Answers2

Linked