Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Stored Procedure And Blob Fields

Posted on 2005-03-20
22
Medium Priority
?
1,652 Views
Last Modified: 2013-12-09
I am having a problem where the stored procedure that I am using does not retreive the proper blob field value. It just gives me a bunch of garbage. Any ideas as to why. Thanks
0
Comment
Question by:sk33v3
  • 11
  • 5
  • 4
  • +1
22 Comments
 
LVL 19

Expert Comment

by:NickUpson
ID: 13589149
please post the stored procedure
0
 
LVL 10

Expert Comment

by:kacor
ID: 13592889
Please post the used database server version, the op system, the stored procedure and the declarations for the fields used in the SP. After this will be easier to help you.

wbr kacor
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13598232
Firebird 1.5
ODBC Drive 1.02.69
Windows 2000 Pro

in table tblPages I have a field called content. Field content is declared as follows
CONTENT BLOB sub_type 2 segment size 1024

Below is the stored procedure.

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255) CHARACTER SET WIN1251,
    CONTENT VARCHAR (30000) CHARACTER SET WIN1251)
AS
BEGIN
  /* Procedure body */
  SELECT Description, Content FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END


More info
I am using EMS to do database management. The machine that is running EMS is not on the same machine as the database. In EMs the data comes through correctly. I have tried changing content to a varchar but I get the same result.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:NickUpson
ID: 13599145
just to make sure I understand:

when you run the select on it's own in EMS it works but when you run this SP in EMS you get garbage?

firebird version 1.5.? - if the last figure is not 2 please consider upgrading
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13601842
No, the stored procedure works fine. Teh SQL, And stored procedure both return correct text in ems. When I go to access it Via C/C++, VB, or ASP I get the garbage.
0
 
LVL 10

Expert Comment

by:kacor
ID: 13602086
By which procedure would you like to reach the data (in C/C++ / VB / ASP) ?
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13602490
I use the Following command to get the data.

SELECT Description, Content FROM SP_GetPage(1)

From my understanding that should work with the stored procedure.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 13604411
your invocation of the SP is ok but how are you doing it in, say, C.
what variable type is receiving the data?
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13607752
well here is the VB Code

Dim RS as recordset
set rs=connection.execute(strsql)

connection is an open adoDB.connection object and strsql is the sql above.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 13609089
unfortunately I don't know VB, which is why I suggested posting the C version which I do know
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 13609300
Are you using a text blob?
ie, when you look at the the tblPages DDL
does it say

RECREATE TABLE tblPages
(
 Description   BLOB SUB_TYPE 1,    
Content   BLOB SUB_TYPE 1,  
.......
)

If you are using binary subtype 0 blobs then that would explain the problem.


If you are, then try change you SP to

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255,
    CONTENT VARCHAR (30000)
AS
BEGIN
  /* Procedure body */
  SELECT Description, Content FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END

to elimanate UNICODE issues,

and then try changing it to

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255),
    CONTENT VARCHAR (16384)
AS
BEGIN
  /* Procedure body */
  SELECT
substring( cast(description as varchar(255)),1,254),
substring( cast(Content as varchar(16384)),1,16384)
FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END

just to see if there are any overflow errors.
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13612469
The subtype is 2. However I am going to try your SQL statements.
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 13612917
second one should have read as follows

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255),
    CONTENT VARCHAR (16384)
AS
BEGIN
  /* Procedure body */
  SELECT
substring( cast(description as varchar(255)) from 1 for 254),
substring( cast(Content as varchar(16384)) from 1 for 16384)
FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END

you can also try since 30000 varchars seem to be handled fine when I tested it now

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255),
    CONTENT VARCHAR (30000)
AS
BEGIN
  /* Procedure body */
  SELECT
cast(description as varchar(255)),
cast(Content as varchar(30000))
FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13616730
I have not yet tried your answer Hobbit but Are there any issues with the server being on windows XP Pro or Windows 2003 Server and trying to access the data from Windows 2000?
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13618581
ok tried those procedures and they both give me the same data. a bunch of garbled junk.
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13618585
Is it the blob subtype I am using?
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 13619245
There are no issues with cross platform access. I use over 60 combinations of win98, win NT, 2000, XP and 2003 daily with no problems.
Blob subtypes actually have nothing to do with the way firebird delivers the data to you, they are just to help applications decide what to do with the data (binary,text etc)

Just to clarify, if you change your adoDB.connection object strsql query to

SELECT Description, Content FROM tblPages WHERE ID=1

do you still get garbage?

If yes, the

How was the data populated in the first place? By you or by an application?

If you run the following
update tblPages set content = 'Some text that hopefully wont be garbage' where ID = 1;
and then run
SELECT Content FROM tblPages WHERE ID=1;

do you get text or garbage?


0
 
LVL 9

Author Comment

by:sk33v3
ID: 13623567
When I do a direct SQL query I get the garbage also. The data was populated by me. I manually inserted the data through EMS. Inside of EMS the data is fine. I will try your Update SQL in a little bit.
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13627426
the data that is displayed after running yor sql is as follows
????4?????????4?????

still garbage...
0
 
LVL 9

Author Comment

by:sk33v3
ID: 13627786
AS an additional Note I tried Dropping the table and creating a new one where the content field is changed to varchar(30000). What I noticed is the description field which is Varchar(250) comes out properly however the content field is still garbage. Is there possibly an issue with length?
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 13628278
A single varchar column is limited to 32000 bytes, but that is further restricted to 10000 bytes when you are storing unicode characters. A Blob doesnt have the same limit and can handle megabytes up to gigabytes depending on page size. I suspect the data stored in the blob was ok but it got corrupted when you converted it to varchar(30000);

try running this on your original table

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255),
    CONTENT VARCHAR (10000)
AS
BEGIN
  /* Procedure body */
  SELECT
description,
cast(Content as varchar(10000))
FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END

or just simply

SELECT
description,
cast(Content as varchar(10000))
FROM tblPages WHERE ID=1

and see if it solves the problem.
0
 
LVL 19

Accepted Solution

by:
Gary Benade earned 2000 total points
ID: 13628295
just remembered that you cant cast a blob, so try this

CREATE PROCEDURE SP_GETPAGE (
    PAGEID INTEGER)
RETURNS (
    DESCRIPTION VARCHAR (255),
    CONTENT VARCHAR (10000)
AS
BEGIN
  /* Procedure body */
  SELECT
description,
substring(Content from 1 for 10000)
FROM tblPages WHERE ID=:pageid INTO :Description, :Content;
  SUSPEND;
END

SELECT
description,
substring(Content from 1 for 10000) as content
FROM tblPages WHERE ID=1

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

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

571 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