4

My requirement is to declare a datatype which accepts xml value with maximum size.
Question: Do we have text, xml or varchar(max) datatype in Sybase?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
antosnowin
  • 221
  • 2
  • 6
  • 13
  • It helps if you specify which Sybase database product and version your are talking about (ASE, ASA, IQ, etc). – Mike Gardner Feb 25 '13 at 19:31
  • we are using Adaptive Server Enterprise/15.5/EBF 19397 SMP ESD#5/P/ia64/HP-UX B.11.23/asear155/2568/64-bit/FBO/Fri – antosnowin Feb 26 '13 at 05:17
  • Can you include the code that is giving you the error. It will be easier for people help troubleshoot. – Mike Gardner Feb 27 '13 at 15:25
  • Actually we are doing conversion from sql to sybase..in sql stored procedure we have datatype as text where have xml values. But when i tried same in sybase , m getting error. – antosnowin Feb 28 '13 at 10:13
  • It would still be helpful to see some of the code, but from my reading of the documentation, you can't use text as a variable. You may just have to resort to using a very large *varchar* instead. – Mike Gardner Feb 28 '13 at 16:07

2 Answers2

3

There is text datatype. You can find More information here.

How to use it with procedure:

create procedure settxt
(
  @txt text
)
as
begin
  select @txt
end

How to run the procedure:

declare @txt  text
select @txt = 'Hello world'
execute settxt @txt

The code works for me, but maybe not for everyone.

Here is solution with temporary table:

create table #texttab
(
  txt varchar(100)
)
go
insert into #texttab
values ('Hello ')

insert into #texttab
values  (' wolrd!')
go
create procedure settxt
as
begin
  declare @txt text, 
          @txtval varchar(100)  

  select @txt=' '

  declare curTXT cursor for 
  select txt from #texttab


  open curTXT
  fetch curTXT into @txtval  
  while @@sqlstatus=0 begin
    select @txtval
    select @txt=@txt+@txtval
    select @txt
    fetch curTXT into @txtval
  end
  close curTXT
  deallocate cursor curTXT

  select @txt

end
go
execute settxt
Robert
  • 25,425
  • 8
  • 67
  • 81
  • Yeah ,but this is supported only with tables. This datatype is not supported in SP parameters. My Requirement is to pass xml value from application with maximum size. – antosnowin Feb 26 '13 at 05:13
  • You can use it with procedure. I added an example to the post. – Robert Feb 26 '13 at 06:54
  • Tried same . I m getting below error.. "TEXT,UNITEXT,IMAGE datatypes are invalid for parameters or local variables..sybase error code:2739 " – antosnowin Feb 26 '13 at 07:21
  • we are using sybase version ("Adaptive Server Enterprise/15.5/EBF 19397 SMP ESD#5/P/ia64/HP-UX B.11.23/asear155/2568/64-bit/FBO/Fri").should i need to install somethin to make this work ? i jus ran it in a database which i have dbo access – antosnowin Feb 26 '13 at 07:31
  • @Parado - The example doesn't work, and in the documentation you reference it says you can not use the *text* data type "For parameters to stored procedures, as values passed to these parameters, or for local variables" – Mike Gardner Feb 28 '13 at 15:57
  • 1
    @MichaelGardner Yes, you have right. I don't know how, but the example works for me. Before I added an example I tested it. Do you tested it? What is your DB? – Robert Mar 01 '13 at 07:42
  • I'm running ASE 15.0, and ASE 15.5, and the first example did not work for me either. – Mike Gardner Mar 01 '13 at 14:31
3

Sybase ASE 15.7 and later support text datatype in parameters and variables within stored procedures (here is my support). Earlier versions depend on page size, for example a server with 2k page size is able to work with 16,348 characters maximum. Another idea is to store data in a different way, for example writing large objects to a file and save only filenames in the database.

gd_forHB
  • 71
  • 6
  • 1
    You might still have issues with this depending on which driver you use. For example in ADO.NET the current driver (16.0.3.0) silently truncates at 16384 characters. There doesn't appear to be any way to send a string longer than that to the database. – Bill Barry Sep 21 '17 at 14:44