Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

UDF SUBSTR dosen't work properly :(

Discuss UDF SUBSTR dosen't work properly :( in the Firebird SQL Development forum on Dev Shed.
UDF SUBSTR dosen't work properly :( Firebird SQL Development forum discussing administration, Firebird SQL syntax, or other Firebird SQL-related topics. Firebird is the evolution of Borland's Interbase product.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old April 22nd, 2004, 04:59 AM
roPopa roPopa is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2004
Location: Bucharest
Posts: 1 roPopa User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Unhappy UDF SUBSTR dosen't work properly :(
so
I have Firebird 1.5
I want to create a SP to parse a string in order to result some numbers as records
CREATE PROCEDURE PARSESTRING(
INPUTSTRING VARCHAR(10000))
RETURNS (
ID INTEGER,
STR VARCHAR(10),
ALLSTRING VARCHAR(10000))
AS
DECLARE VARIABLE varStr VARCHAR(10000);
DECLARE VARIABLE varIndex integer;
declare VARIABLE varTmp varchar(10);
BEGIN
/* Procedure Text */
varStr = :INPUTSTRING;
WHILE (:varStr <> '') DO BEGIN
execute procedure StrPos(',', :varStr) RETURNING_VALUES :varIndex;
ALLSTRING = varStr;
vartmp = SUBSTR(:varStr, 0, :varIndex);
vartmp = rtrim(vartmp);
vartmp = ltrim(vartmp);
str = vartmp;
-- ID = CAST(vartmp as INTEGER);
varStr = SUBSTR(:varStr, :varIndex + 1, StrLen(:varStr));
SUSPEND;
if (varIndex = 0) then break;
END
END

but the SUBSTR dosen't work ...
returning only blanks

the UDF is standard received with Firebird 1.5
thanks
Reply With Quote
  #2  
Old April 22nd, 2004, 09:42 AM
mariuz's Avatar
mariuz mariuz is offline
Bug Hunter
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Transylvania (Romania)
Posts: 274 mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 20 h 12 m 34 sec
Reputation Power: 9
Thumbs up
related to substr size problem (on imput/output parameters) it can use more than 255 chars
Code:
 *
 *      s u b s t r
 *
 *****************************************
 *
 * Functional description:
 *      substr(s,m,n) returns the substring
 *      of s which starts at position m and
 *      ending at position n.


 *      Note: This function is NOT limited to
 *      receiving and returning only 80 characters,
 *      rather, it can use as long as 32767


 *      characters which is the limit on an
 *      INTERBASE character string.
 *      Change by Claudio Valderrama: when n>length(s),
 *      the result will be the original string instead
 *      of NULL as it was originally designed.
 *
 *****************************************/
DECLARE EXTERNAL FUNCTION substr
        CSTRING(80), SMALLINT, SMALLINT
        RETURNS CSTRING(80) FREE_IT
        ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';
__________________
My home page: http://www.firebirdsql.org and work place :http://www.reea.net
Last edited by mariuz : April 23rd, 2004 at 11:04 AM.
Reply With Quote
  #3  
Old April 23rd, 2004, 10:56 AM
mariuz's Avatar
mariuz mariuz is offline
Bug Hunter
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Transylvania (Romania)
Posts: 274 mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 20 h 12 m 34 sec
Reputation Power: 9
tested and doesn't work as expected , but there is a solution
Here is a list thread on the issue

Quote:
Paul Vinkenoog wrote:

> Hi Marius,
>
>
>> in the udf sql script it is written
>>
>> " Note: This function is NOT limited to
>> * receiving and returning only 80 characters,
>> * rather, it can use as long as 32767"
>>
>> it gives this error if i try a varchar with more than 255 it gives
>> this error
>>
>> select substr(test255,1,2) from test255;
>>
>> Statement failed, SQLCODE = -802
>>
>> arithmetic exception, numeric overflow, or string truncation
>
>
>
> This was discussed here a couple of months ago. I remember it because
> back then I was surprised too that in practice it doesn't work like it
> says in the note.
>
> Iirc you can solve the problem by changing the declaration to the
> maximum string length you need for your database. But I'm not sure -
> better search the archives before I put you on the wrong track!


Ok thanks . it works
i defined the function like this

DECLARE EXTERNAL FUNCTION substrlen_more
CSTRING(32767), SMALLINT, SMALLINT
RETURNS CSTRING(32767) FREE_IT
ENTRY_POINT 'IB_UDF_substrlen' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION substr_more
CSTRING(32767), SMALLINT, SMALLINT
RETURNS CSTRING(32767) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';


and then made a query "select substr_more(test255,1,2) from test255;"
it returned the right substrings.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > UDF SUBSTR dosen't work properly :(

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway.