Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error

10 posts in Database Last posting was on 2010-05-15 14:12:39Z
Linda Butler Posted on 2010-05-05 14:08:29Z
From: "Linda Butler" <lbutler@idoc.state.il.us>
Newsgroups: sybase.public.powerbuilder.database
Subject: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 35
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be17bdd$1@forums-1-dub>
Date: 5 May 2010 07:08:29 -0700
X-Trace: forums-1-dub 1273068509 10.22.241.152 (5 May 2010 07:08:29 -0700)
X-Original-Trace: 5 May 2010 07:08:29 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30848
Article PK: 322946

We are having problems with the UPDATEBLOB statement against MS SQL
Server 2008. We use ODBC for our database connections.

Below is what our connection string contains:
this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
Client 10.0" &
+ ";Trusted_Connection=yes" &
+ ";App=" + this.of_GetAppName() &
+ ";Server=" + ls_Server &
+ ";Database=" + ls_DB + "'" &
+ ",DelimitIdentifier='Yes',DisableBind=1"

this.itr_Archive_MSS.AUTOCOMMIT = True

Here is the UPDATEBLOB statement:

UPDATEBLOB dbo.Intel_Photos
SET Image = :lbl_Image
WHERE Intel_Master_Id = :ls_Id and
Item_No = :li_Item and
Seq_No = :li_Seq
USING gnv_app.itr_Archive_MSS ;

The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error

We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft SQL
Native driver 10.0

Is there another connection option that we should be setting? Any
workaround/suggestionns to this problem would be much appreciated.

Thank you.
Linda Butler


Chris Pollach Posted on 2010-05-06 00:24:15Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub>
In-Reply-To: <4be17bdd$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 1
Organization: STD Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be20c2f$1@forums-1-dub>
Date: 5 May 2010 17:24:15 -0700
X-Trace: forums-1-dub 1273105455 10.22.241.152 (5 May 2010 17:24:15 -0700)
X-Original-Trace: 5 May 2010 17:24:15 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30850
Article PK: 322948

Hi Linda;

Exactly what is the error code and message in your Transaction Object at
the time of failure?

--

Regards ... Chris
President: STD Inc.
ISUG - Communications Director
http://chrispollach.pbdjmagazine.com

"Linda Butler" <lbutler@idoc.state.il.us> wrote in message
news:4be17bdd$1@forums-1-dub...
> We are having problems with the UPDATEBLOB statement against MS SQL
> Server 2008. We use ODBC for our database connections.
>
> Below is what our connection string contains:
> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
> Client 10.0" &
> + ";Trusted_Connection=yes" &
> + ";App=" + this.of_GetAppName() &
> + ";Server=" + ls_Server &
> + ";Database=" + ls_DB + "'" &
> + ",DelimitIdentifier='Yes',DisableBind=1"
>
> this.itr_Archive_MSS.AUTOCOMMIT = True
>
> Here is the UPDATEBLOB statement:
>
> UPDATEBLOB dbo.Intel_Photos
> SET Image = :lbl_Image
> WHERE Intel_Master_Id = :ls_Id and
> Item_No = :li_Item and
> Seq_No = :li_Seq
> USING gnv_app.itr_Archive_MSS ;
>
> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>
> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft SQL
> Native driver 10.0
>
> Is there another connection option that we should be setting? Any
> workaround/suggestionns to this problem would be much appreciated.
>
> Thank you.
> Linda Butler
>
>


Linda Butler Posted on 2010-05-06 12:39:20Z
From: "Linda Butler" <lbutler@idoc.state.il.us>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 69
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be2b878$1@forums-1-dub>
Date: 6 May 2010 05:39:20 -0700
X-Trace: forums-1-dub 1273149560 10.22.241.152 (6 May 2010 05:39:20 -0700)
X-Original-Trace: 6 May 2010 05:39:20 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30851
Article PK: 322949

Chris,

This is what our message returns:

gnv_app.itr_Archive_MSS.SQLCODE = -1
gnv_app.itr_Archive_MSS.SQLDBCODE = 999
gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
[Microsoft][ODBC Driver Manager] Function sequence error.

Thanks,
Linda

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4be20c2f$1@forums-1-dub...
> Hi Linda;
>
> Exactly what is the error code and message in your Transaction Object at
> the time of failure?
>
> --
>
> Regards ... Chris
> President: STD Inc.
> ISUG - Communications Director
> http://chrispollach.pbdjmagazine.com
>
>
>
>
> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
> news:4be17bdd$1@forums-1-dub...
>> We are having problems with the UPDATEBLOB statement against MS SQL
>> Server 2008. We use ODBC for our database connections.
>>
>> Below is what our connection string contains:
>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
>> Client 10.0" &
>> + ";Trusted_Connection=yes" &
>> + ";App=" + this.of_GetAppName() &
>> + ";Server=" + ls_Server &
>> + ";Database=" + ls_DB + "'" &
>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>
>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>
>> Here is the UPDATEBLOB statement:
>>
>> UPDATEBLOB dbo.Intel_Photos
>> SET Image = :lbl_Image
>> WHERE Intel_Master_Id = :ls_Id and
>> Item_No = :li_Item and
>> Seq_No = :li_Seq
>> USING gnv_app.itr_Archive_MSS ;
>>
>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>
>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft SQL
>> Native driver 10.0
>>
>> Is there another connection option that we should be setting? Any
>> workaround/suggestionns to this problem would be much appreciated.
>>
>> Thank you.
>> Linda Butler
>>
>>


Chris Pollach Posted on 2010-05-07 01:04:25Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub>
In-Reply-To: <4be2b878$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 1
Organization: STD Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be36719$1@forums-1-dub>
Date: 6 May 2010 18:04:25 -0700
X-Trace: forums-1-dub 1273194265 10.22.241.152 (6 May 2010 18:04:25 -0700)
X-Original-Trace: 6 May 2010 18:04:25 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30853
Article PK: 322951

Hmmmm ....

1) What data type is the column name "Image"?
2) Did you insert the record without the blob column?
3) Is there a Trigger on the table interfering with the UpdateBlob maybe?
4) Check your PBODB105.ini file settings in the [Microsoft SQL Server]
section.
I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter is
set too low. Make sure you also distribute this file with your application
EXE

"Linda Butler" <lbutler@idoc.state.il.us> wrote in message
news:4be2b878$1@forums-1-dub...
> Chris,
>
> This is what our message returns:
>
> gnv_app.itr_Archive_MSS.SQLCODE = -1
> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
> [Microsoft][ODBC Driver Manager] Function sequence error.
>
> Thanks,
> Linda
>
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4be20c2f$1@forums-1-dub...
>> Hi Linda;
>>
>> Exactly what is the error code and message in your Transaction Object at
>> the time of failure?
>>
>> --
>>
>> Regards ... Chris
>> President: STD Inc.
>> ISUG - Communications Director
>> http://chrispollach.pbdjmagazine.com
>>
>>
>>
>>
>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>> news:4be17bdd$1@forums-1-dub...
>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>> Server 2008. We use ODBC for our database connections.
>>>
>>> Below is what our connection string contains:
>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
>>> Client 10.0" &
>>> + ";Trusted_Connection=yes" &
>>> + ";App=" + this.of_GetAppName() &
>>> + ";Server=" + ls_Server &
>>> + ";Database=" + ls_DB + "'" &
>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>
>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>
>>> Here is the UPDATEBLOB statement:
>>>
>>> UPDATEBLOB dbo.Intel_Photos
>>> SET Image = :lbl_Image
>>> WHERE Intel_Master_Id = :ls_Id and
>>> Item_No = :li_Item and
>>> Seq_No = :li_Seq
>>> USING gnv_app.itr_Archive_MSS ;
>>>
>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>
>>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft SQL
>>> Native driver 10.0
>>>
>>> Is there another connection option that we should be setting? Any
>>> workaround/suggestionns to this problem would be much appreciated.
>>>
>>> Thank you.
>>> Linda Butler
>>>
>>>
>
>


Linda Butler Posted on 2010-05-07 13:30:48Z
From: "Linda Butler" <lbutler@idoc.state.il.us>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 137
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be41608$1@forums-1-dub>
Date: 7 May 2010 06:30:48 -0700
X-Trace: forums-1-dub 1273239048 10.22.241.152 (7 May 2010 06:30:48 -0700)
X-Original-Trace: 7 May 2010 06:30:48 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30854
Article PK: 322953

Hi Chris,

Here are answers:

1) The data type is varbinary(max) - Microsoft suggests not using Image data
type as it will be going away
2) Yes. An insert was done prior to the updateblob statement. The 'image'
column allows nulls.
3) No. There is no trigger on this table.
4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
earlier in order to get the larger photos read in properly. I have this set
to 3000000 within the PBODBC105.ini that is on my machine (under c:\program
files\sybase\shared\PowerBuilder folder). Per your suggestion I just
changed the PBMaxTextSize to 3000000 as well. This did not help. Note: I
am running the application from within PowerBuilder and not an executable.
Here is what settings are in my PBODBC105.ini for the Microsoft SQL Server
section:

[Microsoft SQL Server]
PBCatalogOwner='dbo'
PBSyntax='MS_SQLSERVER_SYNTAX'
PBDateTime='MSSQLSERVER_DATETIME'
PBFunctions='SYBASE_FUNCTIONS'
PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
PBObjectIDs='YES'
PBMaxBlobSize='3000000'
PBMaxTextSize='3000000'
PBDWDynamic='NO'
PBPrimaryKeyName='YES'
Block=1
SQLSrvrTSPrefix='0x'
PBUseProcOwner='YES'
PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
PBDefaultCreate='YES'
PBDefaultAlter='NO'
PBDefaultExpressions='YES'

Just to make sure there was nothing on the database side that is preventing
the image from being stored, I did an insert into this table with a .jpg
file in the Microsoft SQL Studio using the 'BulkColumn FROM OPENROWSET'
method. This worked.

Do you have any ideas of what else I may look at?

Thanks for your help.

Linda

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4be36719$1@forums-1-dub...
> Hmmmm ....
>
> 1) What data type is the column name "Image"?
> 2) Did you insert the record without the blob column?
> 3) Is there a Trigger on the table interfering with the UpdateBlob maybe?
> 4) Check your PBODB105.ini file settings in the [Microsoft SQL Server]
> section.
> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter is
> set too low. Make sure you also distribute this file with your application
> EXE
>
>
>
> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
> news:4be2b878$1@forums-1-dub...
>> Chris,
>>
>> This is what our message returns:
>>
>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>
>> Thanks,
>> Linda
>>
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4be20c2f$1@forums-1-dub...
>>> Hi Linda;
>>>
>>> Exactly what is the error code and message in your Transaction Object
>>> at the time of failure?
>>>
>>> --
>>>
>>> Regards ... Chris
>>> President: STD Inc.
>>> ISUG - Communications Director
>>> http://chrispollach.pbdjmagazine.com
>>>
>>>
>>>
>>>
>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>> news:4be17bdd$1@forums-1-dub...
>>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>>> Server 2008. We use ODBC for our database connections.
>>>>
>>>> Below is what our connection string contains:
>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
>>>> Client 10.0" &
>>>> + ";Trusted_Connection=yes" &
>>>> + ";App=" + this.of_GetAppName() &
>>>> + ";Server=" + ls_Server &
>>>> + ";Database=" + ls_DB + "'" &
>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>
>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>
>>>> Here is the UPDATEBLOB statement:
>>>>
>>>> UPDATEBLOB dbo.Intel_Photos
>>>> SET Image = :lbl_Image
>>>> WHERE Intel_Master_Id = :ls_Id and
>>>> Item_No = :li_Item and
>>>> Seq_No = :li_Seq
>>>> USING gnv_app.itr_Archive_MSS ;
>>>>
>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>
>>>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft
>>>> SQL
>>>> Native driver 10.0
>>>>
>>>> Is there another connection option that we should be setting? Any
>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>
>>>> Thank you.
>>>> Linda Butler
>>>>
>>>>
>>
>>


Chris Pollach Posted on 2010-05-08 02:06:25Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub> <4be41608$1@forums-1-dub>
In-Reply-To: <4be41608$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 4
Organization: STD Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be4c721$1@forums-1-dub>
Date: 7 May 2010 19:06:25 -0700
X-Trace: forums-1-dub 1273284385 10.22.241.152 (7 May 2010 19:06:25 -0700)
X-Original-Trace: 7 May 2010 19:06:25 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30855
Article PK: 322952

1) VarBinary ( Max ) is great.
4) PBMaxBlobSize is the key one for you on this one

Hmmmm ... I am running out of ideas. I normally use SNC for SS2008
connectivity not ODBC. The only idea I have for you now is to do an SQL
trace in your PB application and see what happens when you get the S1010
error in the PBTRACE.log file. Maybe this will give us more of a clue?

"Linda Butler" <lbutler@idoc.state.il.us> wrote in message
news:4be41608$1@forums-1-dub...
> Hi Chris,
>
> Here are answers:
>
> 1) The data type is varbinary(max) - Microsoft suggests not using Image
> data type as it will be going away
> 2) Yes. An insert was done prior to the updateblob statement. The
> 'image' column allows nulls.
> 3) No. There is no trigger on this table.
> 4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
> earlier in order to get the larger photos read in properly. I have this
> set to 3000000 within the PBODBC105.ini that is on my machine (under
> c:\program files\sybase\shared\PowerBuilder folder). Per your suggestion
> I just changed the PBMaxTextSize to 3000000 as well. This did not help.
> Note: I am running the application from within PowerBuilder and not an
> executable. Here is what settings are in my PBODBC105.ini for the
> Microsoft SQL Server section:
>
> [Microsoft SQL Server]
> PBCatalogOwner='dbo'
> PBSyntax='MS_SQLSERVER_SYNTAX'
> PBDateTime='MSSQLSERVER_DATETIME'
> PBFunctions='SYBASE_FUNCTIONS'
> PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
> PBObjectIDs='YES'
> PBMaxBlobSize='3000000'
> PBMaxTextSize='3000000'
> PBDWDynamic='NO'
> PBPrimaryKeyName='YES'
> Block=1
> SQLSrvrTSPrefix='0x'
> PBUseProcOwner='YES'
> PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
> PBDefaultCreate='YES'
> PBDefaultAlter='NO'
> PBDefaultExpressions='YES'
>
> Just to make sure there was nothing on the database side that is
> preventing the image from being stored, I did an insert into this table
> with a .jpg file in the Microsoft SQL Studio using the 'BulkColumn FROM
> OPENROWSET' method. This worked.
>
> Do you have any ideas of what else I may look at?
>
> Thanks for your help.
>
> Linda
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4be36719$1@forums-1-dub...
>> Hmmmm ....
>>
>> 1) What data type is the column name "Image"?
>> 2) Did you insert the record without the blob column?
>> 3) Is there a Trigger on the table interfering with the UpdateBlob maybe?
>> 4) Check your PBODB105.ini file settings in the [Microsoft SQL Server]
>> section.
>> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter is
>> set too low. Make sure you also distribute this file with your
>> application EXE
>>
>>
>>
>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>> news:4be2b878$1@forums-1-dub...
>>> Chris,
>>>
>>> This is what our message returns:
>>>
>>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>>
>>> Thanks,
>>> Linda
>>>
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4be20c2f$1@forums-1-dub...
>>>> Hi Linda;
>>>>
>>>> Exactly what is the error code and message in your Transaction Object
>>>> at the time of failure?
>>>>
>>>> --
>>>>
>>>> Regards ... Chris
>>>> President: STD Inc.
>>>> ISUG - Communications Director
>>>> http://chrispollach.pbdjmagazine.com
>>>>
>>>>
>>>>
>>>>
>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>> news:4be17bdd$1@forums-1-dub...
>>>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>>>> Server 2008. We use ODBC for our database connections.
>>>>>
>>>>> Below is what our connection string contains:
>>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server Native
>>>>> Client 10.0" &
>>>>> + ";Trusted_Connection=yes" &
>>>>> + ";App=" + this.of_GetAppName() &
>>>>> + ";Server=" + ls_Server &
>>>>> + ";Database=" + ls_DB + "'" &
>>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>>
>>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>>
>>>>> Here is the UPDATEBLOB statement:
>>>>>
>>>>> UPDATEBLOB dbo.Intel_Photos
>>>>> SET Image = :lbl_Image
>>>>> WHERE Intel_Master_Id = :ls_Id and
>>>>> Item_No = :li_Item and
>>>>> Seq_No = :li_Seq
>>>>> USING gnv_app.itr_Archive_MSS ;
>>>>>
>>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>>
>>>>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft
>>>>> SQL
>>>>> Native driver 10.0
>>>>>
>>>>> Is there another connection option that we should be setting? Any
>>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>>
>>>>> Thank you.
>>>>> Linda Butler
>>>>>
>>>>>
>>>
>>>
>
>


Linda Butler Posted on 2010-05-11 17:55:25Z
From: "Linda Butler" <lbutler@idoc.state.il.us>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub> <4be41608$1@forums-1-dub> <4be4c721$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 197
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be99a0d$1@forums-1-dub>
Date: 11 May 2010 10:55:25 -0700
X-Trace: forums-1-dub 1273600525 10.22.241.152 (11 May 2010 10:55:25 -0700)
X-Original-Trace: 11 May 2010 10:55:25 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30858
Article PK: 322961

I did some more testing and found that I can get the UpdateBlob to work for
a smaller image. I was able to get a 5K image stored in the database using
the UpdateBlob statement. A 30K image does NOT work - i.e. I get the
'Function Sequence Error'.

I ran some traces using the Database trace tool and SQL Statement Trace
tool. The SQL statements looked fine.

In order to see what SQL is actually being processed on the SQL Server
database, I ran a DBCC INPUTBUFFER (SPID) within MS SQL Server Studio right
after the UpdateBlob statement for a 30K image had run from our PowerBuilder
application. This is what it showed:
(@P1 varbinary(8000))update dbo.Intel_Photos set Image = @P1 WHERE
Intel_Master_Id ='TAM200700071' and Item_No =12 and Seq_No =1

From the above, it appears that something is limiting the varbinary datatype
to 8000 bytes.

For additional experimentation, I added another column to our table
dbo.Intel_Photos and declared it as 'image' data type. The UpdateBlob on
this new column worked for a 30K image. The DBCC INPUTBUFFER(SPID) showed
the following after running this statement:

(@P1 image)update dbo.Intel_Photos set ImageNew = @P1 WHERE Intel_Master_Id
='TAM200700071' and Item_No =16 and Seq_No =1

Unless we can find out how we can increase the 8000 byte limit on varbinary
datatypes in the ODBC connection, we may be forced to change our column
datatype to 'image'.

Do you have any ideas of where to look for this limitation on varbinary
datatypes?

Thanks for your help.

Linda

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4be4c721$1@forums-1-dub...
> 1) VarBinary ( Max ) is great.
> 4) PBMaxBlobSize is the key one for you on this one
>
> Hmmmm ... I am running out of ideas. I normally use SNC for SS2008
> connectivity not ODBC. The only idea I have for you now is to do an SQL
> trace in your PB application and see what happens when you get the S1010
> error in the PBTRACE.log file. Maybe this will give us more of a clue?
>
>
>
>
> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
> news:4be41608$1@forums-1-dub...
>> Hi Chris,
>>
>> Here are answers:
>>
>> 1) The data type is varbinary(max) - Microsoft suggests not using Image
>> data type as it will be going away
>> 2) Yes. An insert was done prior to the updateblob statement. The
>> 'image' column allows nulls.
>> 3) No. There is no trigger on this table.
>> 4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
>> earlier in order to get the larger photos read in properly. I have this
>> set to 3000000 within the PBODBC105.ini that is on my machine (under
>> c:\program files\sybase\shared\PowerBuilder folder). Per your suggestion
>> I just changed the PBMaxTextSize to 3000000 as well. This did not help.
>> Note: I am running the application from within PowerBuilder and not an
>> executable. Here is what settings are in my PBODBC105.ini for the
>> Microsoft SQL Server section:
>>
>> [Microsoft SQL Server]
>> PBCatalogOwner='dbo'
>> PBSyntax='MS_SQLSERVER_SYNTAX'
>> PBDateTime='MSSQLSERVER_DATETIME'
>> PBFunctions='SYBASE_FUNCTIONS'
>> PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
>> PBObjectIDs='YES'
>> PBMaxBlobSize='3000000'
>> PBMaxTextSize='3000000'
>> PBDWDynamic='NO'
>> PBPrimaryKeyName='YES'
>> Block=1
>> SQLSrvrTSPrefix='0x'
>> PBUseProcOwner='YES'
>> PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
>> PBDefaultCreate='YES'
>> PBDefaultAlter='NO'
>> PBDefaultExpressions='YES'
>>
>> Just to make sure there was nothing on the database side that is
>> preventing the image from being stored, I did an insert into this table
>> with a .jpg file in the Microsoft SQL Studio using the 'BulkColumn FROM
>> OPENROWSET' method. This worked.
>>
>> Do you have any ideas of what else I may look at?
>>
>> Thanks for your help.
>>
>> Linda
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4be36719$1@forums-1-dub...
>>> Hmmmm ....
>>>
>>> 1) What data type is the column name "Image"?
>>> 2) Did you insert the record without the blob column?
>>> 3) Is there a Trigger on the table interfering with the UpdateBlob
>>> maybe?
>>> 4) Check your PBODB105.ini file settings in the [Microsoft SQL Server]
>>> section.
>>> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter
>>> is
>>> set too low. Make sure you also distribute this file with your
>>> application EXE
>>>
>>>
>>>
>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>> news:4be2b878$1@forums-1-dub...
>>>> Chris,
>>>>
>>>> This is what our message returns:
>>>>
>>>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>>>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>>>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>>>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>>>
>>>> Thanks,
>>>> Linda
>>>>
>>>>
>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>> news:4be20c2f$1@forums-1-dub...
>>>>> Hi Linda;
>>>>>
>>>>> Exactly what is the error code and message in your Transaction Object
>>>>> at the time of failure?
>>>>>
>>>>> --
>>>>>
>>>>> Regards ... Chris
>>>>> President: STD Inc.
>>>>> ISUG - Communications Director
>>>>> http://chrispollach.pbdjmagazine.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>> news:4be17bdd$1@forums-1-dub...
>>>>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>>>>> Server 2008. We use ODBC for our database connections.
>>>>>>
>>>>>> Below is what our connection string contains:
>>>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server
>>>>>> Native Client 10.0" &
>>>>>> + ";Trusted_Connection=yes" &
>>>>>> + ";App=" + this.of_GetAppName() &
>>>>>> + ";Server=" + ls_Server &
>>>>>> + ";Database=" + ls_DB + "'" &
>>>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>>>
>>>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>>>
>>>>>> Here is the UPDATEBLOB statement:
>>>>>>
>>>>>> UPDATEBLOB dbo.Intel_Photos
>>>>>> SET Image = :lbl_Image
>>>>>> WHERE Intel_Master_Id = :ls_Id and
>>>>>> Item_No = :li_Item and
>>>>>> Seq_No = :li_Seq
>>>>>> USING gnv_app.itr_Archive_MSS ;
>>>>>>
>>>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>>>
>>>>>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft
>>>>>> SQL
>>>>>> Native driver 10.0
>>>>>>
>>>>>> Is there another connection option that we should be setting? Any
>>>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>>>
>>>>>> Thank you.
>>>>>> Linda Butler
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>


Chris Pollach Posted on 2010-05-12 01:34:41Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub> <4be41608$1@forums-1-dub> <4be4c721$1@forums-1-dub> <4be99a0d$1@forums-1-dub>
In-Reply-To: <4be99a0d$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 2
Organization: STD Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bea05b1$1@forums-1-dub>
Date: 11 May 2010 18:34:41 -0700
X-Trace: forums-1-dub 1273628081 10.22.241.152 (11 May 2010 18:34:41 -0700)
X-Original-Trace: 11 May 2010 18:34:41 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30860
Article PK: 322960

That sounds like the default PBMaxBlobSize value in the PBODBnnnn.ini file.
Are you sure that you changed the right instance on the INI?

"Linda Butler" <lbutler@idoc.state.il.us> wrote in message
news:4be99a0d$1@forums-1-dub...
> I did some more testing and found that I can get the UpdateBlob to work
> for a smaller image. I was able to get a 5K image stored in the database
> using the UpdateBlob statement. A 30K image does NOT work - i.e. I get
> the 'Function Sequence Error'.
>
> I ran some traces using the Database trace tool and SQL Statement Trace
> tool. The SQL statements looked fine.
>
> In order to see what SQL is actually being processed on the SQL Server
> database, I ran a DBCC INPUTBUFFER (SPID) within MS SQL Server Studio
> right after the UpdateBlob statement for a 30K image had run from our
> PowerBuilder application. This is what it showed:
> (@P1 varbinary(8000))update dbo.Intel_Photos set Image = @P1 WHERE
> Intel_Master_Id ='TAM200700071' and Item_No =12 and Seq_No =1
>
> From the above, it appears that something is limiting the varbinary
> datatype to 8000 bytes.
>
> For additional experimentation, I added another column to our table
> dbo.Intel_Photos and declared it as 'image' data type. The UpdateBlob on
> this new column worked for a 30K image. The DBCC INPUTBUFFER(SPID) showed
> the following after running this statement:
>
> (@P1 image)update dbo.Intel_Photos set ImageNew = @P1 WHERE
> Intel_Master_Id ='TAM200700071' and Item_No =16 and Seq_No =1
>
> Unless we can find out how we can increase the 8000 byte limit on
> varbinary datatypes in the ODBC connection, we may be forced to change our
> column datatype to 'image'.
>
> Do you have any ideas of where to look for this limitation on varbinary
> datatypes?
>
> Thanks for your help.
>
> Linda
>
>
>
>
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4be4c721$1@forums-1-dub...
>> 1) VarBinary ( Max ) is great.
>> 4) PBMaxBlobSize is the key one for you on this one
>>
>> Hmmmm ... I am running out of ideas. I normally use SNC for SS2008
>> connectivity not ODBC. The only idea I have for you now is to do an SQL
>> trace in your PB application and see what happens when you get the S1010
>> error in the PBTRACE.log file. Maybe this will give us more of a clue?
>>
>>
>>
>>
>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>> news:4be41608$1@forums-1-dub...
>>> Hi Chris,
>>>
>>> Here are answers:
>>>
>>> 1) The data type is varbinary(max) - Microsoft suggests not using Image
>>> data type as it will be going away
>>> 2) Yes. An insert was done prior to the updateblob statement. The
>>> 'image' column allows nulls.
>>> 3) No. There is no trigger on this table.
>>> 4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
>>> earlier in order to get the larger photos read in properly. I have this
>>> set to 3000000 within the PBODBC105.ini that is on my machine (under
>>> c:\program files\sybase\shared\PowerBuilder folder). Per your
>>> suggestion I just changed the PBMaxTextSize to 3000000 as well. This
>>> did not help. Note: I am running the application from within
>>> PowerBuilder and not an executable. Here is what settings are in my
>>> PBODBC105.ini for the Microsoft SQL Server section:
>>>
>>> [Microsoft SQL Server]
>>> PBCatalogOwner='dbo'
>>> PBSyntax='MS_SQLSERVER_SYNTAX'
>>> PBDateTime='MSSQLSERVER_DATETIME'
>>> PBFunctions='SYBASE_FUNCTIONS'
>>> PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
>>> PBObjectIDs='YES'
>>> PBMaxBlobSize='3000000'
>>> PBMaxTextSize='3000000'
>>> PBDWDynamic='NO'
>>> PBPrimaryKeyName='YES'
>>> Block=1
>>> SQLSrvrTSPrefix='0x'
>>> PBUseProcOwner='YES'
>>> PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
>>> PBDefaultCreate='YES'
>>> PBDefaultAlter='NO'
>>> PBDefaultExpressions='YES'
>>>
>>> Just to make sure there was nothing on the database side that is
>>> preventing the image from being stored, I did an insert into this table
>>> with a .jpg file in the Microsoft SQL Studio using the 'BulkColumn FROM
>>> OPENROWSET' method. This worked.
>>>
>>> Do you have any ideas of what else I may look at?
>>>
>>> Thanks for your help.
>>>
>>> Linda
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4be36719$1@forums-1-dub...
>>>> Hmmmm ....
>>>>
>>>> 1) What data type is the column name "Image"?
>>>> 2) Did you insert the record without the blob column?
>>>> 3) Is there a Trigger on the table interfering with the UpdateBlob
>>>> maybe?
>>>> 4) Check your PBODB105.ini file settings in the [Microsoft SQL Server]
>>>> section.
>>>> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter
>>>> is
>>>> set too low. Make sure you also distribute this file with your
>>>> application EXE
>>>>
>>>>
>>>>
>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>> news:4be2b878$1@forums-1-dub...
>>>>> Chris,
>>>>>
>>>>> This is what our message returns:
>>>>>
>>>>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>>>>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>>>>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>>>>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>>>>
>>>>> Thanks,
>>>>> Linda
>>>>>
>>>>>
>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>> news:4be20c2f$1@forums-1-dub...
>>>>>> Hi Linda;
>>>>>>
>>>>>> Exactly what is the error code and message in your Transaction
>>>>>> Object at the time of failure?
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Regards ... Chris
>>>>>> President: STD Inc.
>>>>>> ISUG - Communications Director
>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>>> news:4be17bdd$1@forums-1-dub...
>>>>>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>>>>>> Server 2008. We use ODBC for our database connections.
>>>>>>>
>>>>>>> Below is what our connection string contains:
>>>>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server
>>>>>>> Native Client 10.0" &
>>>>>>> + ";Trusted_Connection=yes" &
>>>>>>> + ";App=" + this.of_GetAppName() &
>>>>>>> + ";Server=" + ls_Server &
>>>>>>> + ";Database=" + ls_DB + "'" &
>>>>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>>>>
>>>>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>>>>
>>>>>>> Here is the UPDATEBLOB statement:
>>>>>>>
>>>>>>> UPDATEBLOB dbo.Intel_Photos
>>>>>>> SET Image = :lbl_Image
>>>>>>> WHERE Intel_Master_Id = :ls_Id and
>>>>>>> Item_No = :li_Item and
>>>>>>> Seq_No = :li_Seq
>>>>>>> USING gnv_app.itr_Archive_MSS ;
>>>>>>>
>>>>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>>>>
>>>>>>> We are using PowerBuilder version 10.5.1. Build (6565) and Microsoft
>>>>>>> SQL
>>>>>>> Native driver 10.0
>>>>>>>
>>>>>>> Is there another connection option that we should be setting? Any
>>>>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>>>>
>>>>>>> Thank you.
>>>>>>> Linda Butler
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>


Linda Butler Posted on 2010-05-12 13:17:15Z
From: "Linda Butler" <lbutler@idoc.state.il.us>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub> <4be41608$1@forums-1-dub> <4be4c721$1@forums-1-dub> <4be99a0d$1@forums-1-dub> <4bea05b1$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 231
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4beaaa5b$1@forums-1-dub>
Date: 12 May 2010 06:17:15 -0700
X-Trace: forums-1-dub 1273670235 10.22.241.152 (12 May 2010 06:17:15 -0700)
X-Original-Trace: 12 May 2010 06:17:15 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30861
Article PK: 322956

Yes. I believe I am changing the right instance of PBODB105.ini file. Just
to make sure, I changed the PBMAXBlobSize down to 3000 to see how this
effected the SelectBlob. (Note: We originally discovered this setting when
we were experiencing problems with not seeing whole images (blobs) after
doing a SelectBlob. Increasing this setting fixed that problem.) After
decreasing this to 3000, I was unable to see the full photos. This tells me
that it is picking up this setting.

But it appears that it is not using the PBMaxBlobSize setting for the
UPDATEBLOB statement. With this setting still set at 3000, I did another
UPDATEBLOB on a <5k image. I ran DBCC INPUTBUFFER to see what SQL Statement
was being processed by the database. This is what it showed:
(@P1 varbinary(4543))update dbo.Intel_Photos set Image = @P1 WHERE
Intel_Master_Id ='TAM200700071' and Item_No =18 and Seq_No =1

It did not decrease the varbinary declaration size to 3000.

Thanks again for your thoughts.
Linda

"Chris Pollach" <cpollach@travel-net.com> wrote in message
news:4bea05b1$1@forums-1-dub...
>
> That sounds like the default PBMaxBlobSize value in the PBODBnnnn.ini
> file.
> Are you sure that you changed the right instance on the INI?
>
>
>
>
> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
> news:4be99a0d$1@forums-1-dub...
>> I did some more testing and found that I can get the UpdateBlob to work
>> for a smaller image. I was able to get a 5K image stored in the database
>> using the UpdateBlob statement. A 30K image does NOT work - i.e. I get
>> the 'Function Sequence Error'.
>>
>> I ran some traces using the Database trace tool and SQL Statement Trace
>> tool. The SQL statements looked fine.
>>
>> In order to see what SQL is actually being processed on the SQL Server
>> database, I ran a DBCC INPUTBUFFER (SPID) within MS SQL Server Studio
>> right after the UpdateBlob statement for a 30K image had run from our
>> PowerBuilder application. This is what it showed:
>> (@P1 varbinary(8000))update dbo.Intel_Photos set Image = @P1 WHERE
>> Intel_Master_Id ='TAM200700071' and Item_No =12 and Seq_No =1
>>
>> From the above, it appears that something is limiting the varbinary
>> datatype to 8000 bytes.
>>
>> For additional experimentation, I added another column to our table
>> dbo.Intel_Photos and declared it as 'image' data type. The UpdateBlob on
>> this new column worked for a 30K image. The DBCC INPUTBUFFER(SPID)
>> showed the following after running this statement:
>>
>> (@P1 image)update dbo.Intel_Photos set ImageNew = @P1 WHERE
>> Intel_Master_Id ='TAM200700071' and Item_No =16 and Seq_No =1
>>
>> Unless we can find out how we can increase the 8000 byte limit on
>> varbinary datatypes in the ODBC connection, we may be forced to change
>> our column datatype to 'image'.
>>
>> Do you have any ideas of where to look for this limitation on varbinary
>> datatypes?
>>
>> Thanks for your help.
>>
>> Linda
>>
>>
>>
>>
>>
>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>> news:4be4c721$1@forums-1-dub...
>>> 1) VarBinary ( Max ) is great.
>>> 4) PBMaxBlobSize is the key one for you on this one
>>>
>>> Hmmmm ... I am running out of ideas. I normally use SNC for SS2008
>>> connectivity not ODBC. The only idea I have for you now is to do an SQL
>>> trace in your PB application and see what happens when you get the S1010
>>> error in the PBTRACE.log file. Maybe this will give us more of a clue?
>>>
>>>
>>>
>>>
>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>> news:4be41608$1@forums-1-dub...
>>>> Hi Chris,
>>>>
>>>> Here are answers:
>>>>
>>>> 1) The data type is varbinary(max) - Microsoft suggests not using Image
>>>> data type as it will be going away
>>>> 2) Yes. An insert was done prior to the updateblob statement. The
>>>> 'image' column allows nulls.
>>>> 3) No. There is no trigger on this table.
>>>> 4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
>>>> earlier in order to get the larger photos read in properly. I have
>>>> this set to 3000000 within the PBODBC105.ini that is on my machine
>>>> (under c:\program files\sybase\shared\PowerBuilder folder). Per your
>>>> suggestion I just changed the PBMaxTextSize to 3000000 as well. This
>>>> did not help. Note: I am running the application from within
>>>> PowerBuilder and not an executable. Here is what settings are in my
>>>> PBODBC105.ini for the Microsoft SQL Server section:
>>>>
>>>> [Microsoft SQL Server]
>>>> PBCatalogOwner='dbo'
>>>> PBSyntax='MS_SQLSERVER_SYNTAX'
>>>> PBDateTime='MSSQLSERVER_DATETIME'
>>>> PBFunctions='SYBASE_FUNCTIONS'
>>>> PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
>>>> PBObjectIDs='YES'
>>>> PBMaxBlobSize='3000000'
>>>> PBMaxTextSize='3000000'
>>>> PBDWDynamic='NO'
>>>> PBPrimaryKeyName='YES'
>>>> Block=1
>>>> SQLSrvrTSPrefix='0x'
>>>> PBUseProcOwner='YES'
>>>> PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
>>>> PBDefaultCreate='YES'
>>>> PBDefaultAlter='NO'
>>>> PBDefaultExpressions='YES'
>>>>
>>>> Just to make sure there was nothing on the database side that is
>>>> preventing the image from being stored, I did an insert into this table
>>>> with a .jpg file in the Microsoft SQL Studio using the 'BulkColumn FROM
>>>> OPENROWSET' method. This worked.
>>>>
>>>> Do you have any ideas of what else I may look at?
>>>>
>>>> Thanks for your help.
>>>>
>>>> Linda
>>>>
>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>> news:4be36719$1@forums-1-dub...
>>>>> Hmmmm ....
>>>>>
>>>>> 1) What data type is the column name "Image"?
>>>>> 2) Did you insert the record without the blob column?
>>>>> 3) Is there a Trigger on the table interfering with the UpdateBlob
>>>>> maybe?
>>>>> 4) Check your PBODB105.ini file settings in the [Microsoft SQL
>>>>> Server] section.
>>>>> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize parameter
>>>>> is
>>>>> set too low. Make sure you also distribute this file with your
>>>>> application EXE
>>>>>
>>>>>
>>>>>
>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>> news:4be2b878$1@forums-1-dub...
>>>>>> Chris,
>>>>>>
>>>>>> This is what our message returns:
>>>>>>
>>>>>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>>>>>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>>>>>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>>>>>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>>>>>
>>>>>> Thanks,
>>>>>> Linda
>>>>>>
>>>>>>
>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>> news:4be20c2f$1@forums-1-dub...
>>>>>>> Hi Linda;
>>>>>>>
>>>>>>> Exactly what is the error code and message in your Transaction
>>>>>>> Object at the time of failure?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> Regards ... Chris
>>>>>>> President: STD Inc.
>>>>>>> ISUG - Communications Director
>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>>>> news:4be17bdd$1@forums-1-dub...
>>>>>>>> We are having problems with the UPDATEBLOB statement against MS SQL
>>>>>>>> Server 2008. We use ODBC for our database connections.
>>>>>>>>
>>>>>>>> Below is what our connection string contains:
>>>>>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server
>>>>>>>> Native Client 10.0" &
>>>>>>>> + ";Trusted_Connection=yes" &
>>>>>>>> + ";App=" + this.of_GetAppName() &
>>>>>>>> + ";Server=" + ls_Server &
>>>>>>>> + ";Database=" + ls_DB + "'" &
>>>>>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>>>>>
>>>>>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>>>>>
>>>>>>>> Here is the UPDATEBLOB statement:
>>>>>>>>
>>>>>>>> UPDATEBLOB dbo.Intel_Photos
>>>>>>>> SET Image = :lbl_Image
>>>>>>>> WHERE Intel_Master_Id = :ls_Id and
>>>>>>>> Item_No = :li_Item and
>>>>>>>> Seq_No = :li_Seq
>>>>>>>> USING gnv_app.itr_Archive_MSS ;
>>>>>>>>
>>>>>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>>>>>
>>>>>>>> We are using PowerBuilder version 10.5.1. Build (6565) and
>>>>>>>> Microsoft SQL
>>>>>>>> Native driver 10.0
>>>>>>>>
>>>>>>>> Is there another connection option that we should be setting? Any
>>>>>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>>>>>
>>>>>>>> Thank you.
>>>>>>>> Linda Butler
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>


Tyler Cruse Posted on 2010-05-15 14:12:39Z
From: "Tyler Cruse" <tcruse@trlx.com>
Newsgroups: sybase.public.powerbuilder.database
References: <4be17bdd$1@forums-1-dub> <4be20c2f$1@forums-1-dub> <4be2b878$1@forums-1-dub> <4be36719$1@forums-1-dub> <4be41608$1@forums-1-dub> <4be4c721$1@forums-1-dub> <4be99a0d$1@forums-1-dub> <4bea05b1$1@forums-1-dub> <4beaaa5b$1@forums-1-dub>
In-Reply-To: <4beaaa5b$1@forums-1-dub>
Subject: Re: UPDATEBLOB on MS SQL Server 2008 - Function Sequence Error
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8064.206
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8064.206
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4beeabd7$1@forums-1-dub>
Date: 15 May 2010 07:12:39 -0700
X-Trace: forums-1-dub 1273932759 10.22.241.152 (15 May 2010 07:12:39 -0700)
X-Original-Trace: 15 May 2010 07:12:39 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.database:30862
Article PK: 322957

I have not done anything with ODBC for many years, however, a varchar(max)
column type using SNC is treated as a varchar field not as an image or text
field. So, you might try a normal insert or update statement.

"Linda Butler" <lbutler@idoc.state.il.us> wrote in message
news:4beaaa5b$1@forums-1-dub...
> Yes. I believe I am changing the right instance of PBODB105.ini file.
> Just to make sure, I changed the PBMAXBlobSize down to 3000 to see how
> this effected the SelectBlob. (Note: We originally discovered this
> setting when we were experiencing problems with not seeing whole images
> (blobs) after doing a SelectBlob. Increasing this setting fixed that
> problem.) After decreasing this to 3000, I was unable to see the full
> photos. This tells me that it is picking up this setting.
>
> But it appears that it is not using the PBMaxBlobSize setting for the
> UPDATEBLOB statement. With this setting still set at 3000, I did another
> UPDATEBLOB on a <5k image. I ran DBCC INPUTBUFFER to see what SQL
> Statement was being processed by the database. This is what it showed:
> (@P1 varbinary(4543))update dbo.Intel_Photos set Image = @P1 WHERE
> Intel_Master_Id ='TAM200700071' and Item_No =18 and Seq_No =1
>
> It did not decrease the varbinary declaration size to 3000.
>
> Thanks again for your thoughts.
> Linda
>
> "Chris Pollach" <cpollach@travel-net.com> wrote in message
> news:4bea05b1$1@forums-1-dub...
>>
>> That sounds like the default PBMaxBlobSize value in the PBODBnnnn.ini
>> file.
>> Are you sure that you changed the right instance on the INI?
>>
>>
>>
>>
>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>> news:4be99a0d$1@forums-1-dub...
>>> I did some more testing and found that I can get the UpdateBlob to work
>>> for a smaller image. I was able to get a 5K image stored in the
>>> database using the UpdateBlob statement. A 30K image does NOT work -
>>> i.e. I get the 'Function Sequence Error'.
>>>
>>> I ran some traces using the Database trace tool and SQL Statement Trace
>>> tool. The SQL statements looked fine.
>>>
>>> In order to see what SQL is actually being processed on the SQL Server
>>> database, I ran a DBCC INPUTBUFFER (SPID) within MS SQL Server Studio
>>> right after the UpdateBlob statement for a 30K image had run from our
>>> PowerBuilder application. This is what it showed:
>>> (@P1 varbinary(8000))update dbo.Intel_Photos set Image = @P1 WHERE
>>> Intel_Master_Id ='TAM200700071' and Item_No =12 and Seq_No =1
>>>
>>> From the above, it appears that something is limiting the varbinary
>>> datatype to 8000 bytes.
>>>
>>> For additional experimentation, I added another column to our table
>>> dbo.Intel_Photos and declared it as 'image' data type. The UpdateBlob
>>> on this new column worked for a 30K image. The DBCC INPUTBUFFER(SPID)
>>> showed the following after running this statement:
>>>
>>> (@P1 image)update dbo.Intel_Photos set ImageNew = @P1 WHERE
>>> Intel_Master_Id ='TAM200700071' and Item_No =16 and Seq_No =1
>>>
>>> Unless we can find out how we can increase the 8000 byte limit on
>>> varbinary datatypes in the ODBC connection, we may be forced to change
>>> our column datatype to 'image'.
>>>
>>> Do you have any ideas of where to look for this limitation on varbinary
>>> datatypes?
>>>
>>> Thanks for your help.
>>>
>>> Linda
>>>
>>>
>>>
>>>
>>>
>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>> news:4be4c721$1@forums-1-dub...
>>>> 1) VarBinary ( Max ) is great.
>>>> 4) PBMaxBlobSize is the key one for you on this one
>>>>
>>>> Hmmmm ... I am running out of ideas. I normally use SNC for SS2008
>>>> connectivity not ODBC. The only idea I have for you now is to do an SQL
>>>> trace in your PB application and see what happens when you get the
>>>> S1010 error in the PBTRACE.log file. Maybe this will give us more of a
>>>> clue?
>>>>
>>>>
>>>>
>>>>
>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>> news:4be41608$1@forums-1-dub...
>>>>> Hi Chris,
>>>>>
>>>>> Here are answers:
>>>>>
>>>>> 1) The data type is varbinary(max) - Microsoft suggests not using
>>>>> Image data type as it will be going away
>>>>> 2) Yes. An insert was done prior to the updateblob statement. The
>>>>> 'image' column allows nulls.
>>>>> 3) No. There is no trigger on this table.
>>>>> 4) We had increased the size of PBMaxBlobSize within the PBODBC105.ini
>>>>> earlier in order to get the larger photos read in properly. I have
>>>>> this set to 3000000 within the PBODBC105.ini that is on my machine
>>>>> (under c:\program files\sybase\shared\PowerBuilder folder). Per your
>>>>> suggestion I just changed the PBMaxTextSize to 3000000 as well. This
>>>>> did not help. Note: I am running the application from within
>>>>> PowerBuilder and not an executable. Here is what settings are in my
>>>>> PBODBC105.ini for the Microsoft SQL Server section:
>>>>>
>>>>> [Microsoft SQL Server]
>>>>> PBCatalogOwner='dbo'
>>>>> PBSyntax='MS_SQLSERVER_SYNTAX'
>>>>> PBDateTime='MSSQLSERVER_DATETIME'
>>>>> PBFunctions='SYBASE_FUNCTIONS'
>>>>> PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
>>>>> PBObjectIDs='YES'
>>>>> PBMaxBlobSize='3000000'
>>>>> PBMaxTextSize='3000000'
>>>>> PBDWDynamic='NO'
>>>>> PBPrimaryKeyName='YES'
>>>>> Block=1
>>>>> SQLSrvrTSPrefix='0x'
>>>>> PBUseProcOwner='YES'
>>>>> PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
>>>>> PBDefaultCreate='YES'
>>>>> PBDefaultAlter='NO'
>>>>> PBDefaultExpressions='YES'
>>>>>
>>>>> Just to make sure there was nothing on the database side that is
>>>>> preventing the image from being stored, I did an insert into this
>>>>> table with a .jpg file in the Microsoft SQL Studio using the
>>>>> 'BulkColumn FROM OPENROWSET' method. This worked.
>>>>>
>>>>> Do you have any ideas of what else I may look at?
>>>>>
>>>>> Thanks for your help.
>>>>>
>>>>> Linda
>>>>>
>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>> news:4be36719$1@forums-1-dub...
>>>>>> Hmmmm ....
>>>>>>
>>>>>> 1) What data type is the column name "Image"?
>>>>>> 2) Did you insert the record without the blob column?
>>>>>> 3) Is there a Trigger on the table interfering with the UpdateBlob
>>>>>> maybe?
>>>>>> 4) Check your PBODB105.ini file settings in the [Microsoft SQL
>>>>>> Server] section.
>>>>>> I would suspect that your PBMaxBlobSize and/or PBMaxTextSize
>>>>>> parameter is
>>>>>> set too low. Make sure you also distribute this file with your
>>>>>> application EXE
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>>> news:4be2b878$1@forums-1-dub...
>>>>>>> Chris,
>>>>>>>
>>>>>>> This is what our message returns:
>>>>>>>
>>>>>>> gnv_app.itr_Archive_MSS.SQLCODE = -1
>>>>>>> gnv_app.itr_Archive_MSS.SQLDBCODE = 999
>>>>>>> gnv_app.itr_Archive_MSS.SQLERRTEXT = SQLSTATE = S1010
>>>>>>> [Microsoft][ODBC Driver Manager] Function sequence error.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Linda
>>>>>>>
>>>>>>>
>>>>>>> "Chris Pollach" <cpollach@travel-net.com> wrote in message
>>>>>>> news:4be20c2f$1@forums-1-dub...
>>>>>>>> Hi Linda;
>>>>>>>>
>>>>>>>> Exactly what is the error code and message in your Transaction
>>>>>>>> Object at the time of failure?
>>>>>>>>
>>>>>>>> --
>>>>>>>>
>>>>>>>> Regards ... Chris
>>>>>>>> President: STD Inc.
>>>>>>>> ISUG - Communications Director
>>>>>>>> http://chrispollach.pbdjmagazine.com
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "Linda Butler" <lbutler@idoc.state.il.us> wrote in message
>>>>>>>> news:4be17bdd$1@forums-1-dub...
>>>>>>>>> We are having problems with the UPDATEBLOB statement against MS
>>>>>>>>> SQL
>>>>>>>>> Server 2008. We use ODBC for our database connections.
>>>>>>>>>
>>>>>>>>> Below is what our connection string contains:
>>>>>>>>> this.itr_Archive_MSS.DBPARM = "connectstring='Driver=SQL Server
>>>>>>>>> Native Client 10.0" &
>>>>>>>>> + ";Trusted_Connection=yes" &
>>>>>>>>> + ";App=" + this.of_GetAppName() &
>>>>>>>>> + ";Server=" + ls_Server &
>>>>>>>>> + ";Database=" + ls_DB + "'" &
>>>>>>>>> + ",DelimitIdentifier='Yes',DisableBind=1"
>>>>>>>>>
>>>>>>>>> this.itr_Archive_MSS.AUTOCOMMIT = True
>>>>>>>>>
>>>>>>>>> Here is the UPDATEBLOB statement:
>>>>>>>>>
>>>>>>>>> UPDATEBLOB dbo.Intel_Photos
>>>>>>>>> SET Image = :lbl_Image
>>>>>>>>> WHERE Intel_Master_Id = :ls_Id and
>>>>>>>>> Item_No = :li_Item and
>>>>>>>>> Seq_No = :li_Seq
>>>>>>>>> USING gnv_app.itr_Archive_MSS ;
>>>>>>>>>
>>>>>>>>> The UPDATEBLOB fails with SQLSTATE=S1010 Function Sequence Error
>>>>>>>>>
>>>>>>>>> We are using PowerBuilder version 10.5.1. Build (6565) and
>>>>>>>>> Microsoft SQL
>>>>>>>>> Native driver 10.0
>>>>>>>>>
>>>>>>>>> Is there another connection option that we should be setting? Any
>>>>>>>>> workaround/suggestionns to this problem would be much appreciated.
>>>>>>>>>
>>>>>>>>> Thank you.
>>>>>>>>> Linda Butler
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>