?
Solved

Stored Procedure And Blob Fields

Posted on 2005-03-20
22
Medium Priority
?
1,645 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
[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
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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