?
Solved

how to overcome string at most 65534 characters long

Posted on 2010-08-30
27
Medium Priority
?
1,883 Views
Last Modified: 2012-05-10
Hi,

I have formula in my CR 2008. It was working okay when there was less data.

However now I get message "a string can be at most 65534 characters long".

Please revise my existig formula to overcome this error.

Formula name: RESULT

stringvar prefix;
stringvar country;
stringvar route;
stringvar Type;
stringvar output;
prefix:={Sheet3_.Access};
country:={Sheet3_.Country} ;
route:= {Sheet3_.Routes};
type:="4";

numbervar x;
numbervar ub;
stringvar array Routes;
routes:=split(route,",");
ub:= ubound(routes);
for x:=1 to ub-1 do

output:= output & "Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('"&Prefix&"',"&"'"&mid(country,1,15)&"',"&"'"&routes[x]&"',"&"'"&totext(x-1,0,"")&"',"&"'0');"& chr(13)& chr(10);

output:= output & "Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('"&Prefix&"',"&"'"&mid(country,1,15)&"',"&"'"&routes[ub]&"',"&"'"&totext(x-1,0,"")&"',"&"'4');"&chr(13)& chr(10);

If you want I can attach report and datasource (in Excel).

Thanks in advance for your help.
0
Comment
Question by:NickHoward
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
  • 5
  • +1
27 Comments
 
LVL 12

Expert Comment

by:JayConverse
ID: 33557714
You need to have less data.  Or, break it up into multiple stringvars and multiple outputs.
0
 

Author Comment

by:NickHoward
ID: 33557907
Data can not be less and may increase.

Can you show how to breakup into multiple outputs?

Thanks.
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 33557965
How are you writing or executing the "output" variable?
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

Author Comment

by:NickHoward
ID: 33558277
In Detail section I put my above formula.

If u want I can attach CR report and excel backend data.
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 33558523
Just the report.
0
 

Author Comment

by:NickHoward
ID: 33558611
Report attached
test.rpt
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 33558689
I think your basic problem is "when all you have is a hammer, everything looks like a nail".  Crystal may not be the tool for this.  

So, what exactly are you trying to do with this output?  

0
 
LVL 11

Expert Comment

by:tickett
ID: 33558800
I wonder if you could make the output variable a string array? Then instead of setting storing the concatenated result in a variable store each iteration in it's own element of the array. Then at the end return the whole array?

L
0
 

Author Comment

by:NickHoward
ID: 33558904
Well said. Actually I am using CR report to create sql insert.

May bit strange for you, but it was doing the great job unless data was less.

Hope you can look at formula and can suggest multiple outputs.

Thanks.
0
 
LVL 11

Expert Comment

by:tickett
ID: 33558970
Yes- you can simply have a SQL statement:

SELECT 'INSERT INTO mytable (field1, field2) VALUES (''' + fielda + ''', ''' + fieldb + ''')' FROM someothertable

You could even do the insert all in one if all you're intending to do is copy/paste the INSERT statements?

L
0
 

Author Comment

by:NickHoward
ID: 33560050
tickett,

Sorry for not being understood.

Looks like no solution to avoid  65534 long characters for my existing requirement.

Shall I close the question?
0
 
LVL 12

Expert Comment

by:JayConverse
ID: 33560111
But how are you getting from the Crystal report to the SQL statement?  Exporting to a text file?
0
 

Author Comment

by:NickHoward
ID: 33560171
Yeah into CSV file and running from SQL DTS.
0
 
LVL 11

Expert Comment

by:tickett
ID: 33560897
Wow- that seems seriously long winded. I think i may have already answered "this question" about how to get around the error.

I think you need to post a new question with regard to the task you are trying to achieve with this current crystal/dts solution? Some of us experts can probably suggest smarter ways to attack it.

L
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33563358
YOu can use an array of strings but the handling would be difficult.

Basic idea
NumberVar StringIndex := 1;
StringVar Array MyStrings[1];

for x:=1 to ub-1 do
If Length(MyStrings[StringIndex]) >= 65000 then
(
    StringIndex := StringIndex + 1;
    ReDim Preserve MyStrings[StringIndex];
)
MyStrings[StringIndex] := MyStrings[StringIndex] & "Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('"&Prefix&"',"&"'"&mid(country,1,15)&"',"&"'"&routes[x]&"',"&"'"&totext(x-1,0,"")&"',"&"'0');"& chr(13)& chr(10);

You would then need separate formulas to print/display each member of the MyStrings array

mlmcc
0
 

Author Comment

by:NickHoward
ID: 33566984
mlmcc,

Thanks for the tip. I tried to impment but I guess I am doing something wrong.

Can you check my report and suggest complete formula1, formula2 so that I adopt it?

Sorry for being so naive.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33573097
If you can upload the report, I will be happy to try and fix it.

You could also just copy and paste the formulas.

mlmcc
0
 

Author Comment

by:NickHoward
ID: 33574291
Thanks mlmcc. Actually I am fan of yours. You did help me in past.

Formula my report is: "output"

stringvar prefix;
stringvar country;
stringvar route;
stringvar Type;
stringvar output;
prefix:={Sheet3_.Access};
country:={Sheet3_.Country} ;
route:= {Sheet3_.Routes};
type:="4";

numbervar x;
numbervar ub;
stringvar array Routes;
routes:=split(route,",");
ub:= ubound(routes);
for x:=1 to ub-1 do

output:= output & "Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('"&Prefix&"',"&"'"&mid(country,1,15)&"',"&"'"&routes[x]&"',"&"'"&totext(x-1,0,"")&"',"&"'0');"& chr(13)& chr(10);
output:= output & "Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('"&Prefix&"',"&"'"&mid(country,1,15)&"',"&"'"&routes[ub]&"',"&"'"&totext(x-1,0,"")&"',"&"'4');"&chr(13)& chr(10);


I am attaching report with data saved in it.

Let me know if data source (excel file also required).

Thanks.
test.rpt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33580784
Check this revision

mlmcc
test1rev.rpt
0
 

Author Comment

by:NickHoward
ID: 33599756
Hi mlmcc,

I tried your revised report but it was only bringing last record from datasource and was repeating 3 times.

I think using sql statements as output is getting this difficult to solve.

You have helped me in past check:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_25010605.html

I have same thing now but my data grew and therefore getting limitations.

I am attaching new report with data in excel to test it.

Hope it is easier know to look at output.

Thanks in advance.
Prefixes-Routing.xls
test.rpt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33607703
Does this report do what you want?

mlmcc
testRev1.rpt
0
 

Author Comment

by:NickHoward
ID: 33609960
Checking and thanks.
0
 

Author Comment

by:NickHoward
ID: 33610463
mlmcc,

You almost solved it. Only change required is that for at end of each array, value of Type should be '4' instead of '0'

For instance, select prefix 0032:

Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','14','0','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','19','1','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','2','2','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','15','3','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','4','4','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','13','5','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','6','6','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','17','7','0');
Insert into dbo.s_route_vs_tmp (prefix, country, route, Priority, Type) VALUES('0032','Belgium','22','8','4');

You can see that for each prefix last entry ends with type '4'.
I tried to achieve it by modifying output formula but no luck.
Thanks and already grateful for your assistance.


0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 33621536
I missed why you had the line outside the loop

mlmcc
testRev2.rpt
0
 

Author Comment

by:NickHoward
ID: 33621990
mlmcc ROCKS !!!

This solved the issue.

I wish I can raise points from 500 to 1000.

Bundle of thanks for this help,
0
 

Author Closing Comment

by:NickHoward
ID: 33621998
No match with mlmcc. When no one there to help, mlmcc steps in and solve most difficulet tasks. God Bless.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33622073
Thanks for the kind words.  

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question