ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Accessing DB: 'We still think we have a valid database conn'

Post new topic  Reply to topic
 Accessing DB: 'We still think we have a valid database conn' « View previous topic :: View next topic » 
Author Message
jb3
PostPosted: Tue Jun 05, 2018 7:16 am    Post subject: Accessing DB: 'We still think we have a valid database conn' Reply with quote

Apprentice

Joined: 18 Aug 2017
Posts: 26

Hi all,

I need to read from an XML once everyday and insert rows into database.

The controlgoes through the below code about 40 times, and successfully adds 40 rows to the table.

Quote:
INSERT INTO Database.TableName1(Field1, Field2, Field3) VALUES (Field1, Field2, Field3);


I then need to remove any previous day data.

Quote:
DELETE FROM Database.TableName1 WHERE InsertDate <> '2018-06-05';


While preparing the delete statement, since there is already a database connection, the mqsilogs show as:

Quote:
DELETE FROM Database.TableName1 AS TableName1 WHERE InsertDate <> '2018-06-05'


The controls then exists as error

Quote:

2018-06-05 16:09:33.438538 6024 >> RecoverableException BIP2488E: ('.MidasCurrencyImport_DatabaseInsert.Main', '57.3') Error detected whilst executing the SQL statement ''DELETE FROM Database.MidasCurrencyImport AS MidasCurrencyImport WHERE InsertDate <> '2018-06-05';''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2018-06-05 16:09:33.438546 6024 >> ImbOdbcStatement::checkRcInner file:F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp line:3635 message:2321.BIPmsgs 'Root SQL exception' , -1, 'odbc32.dll'
2018-06-05 16:09:33.438546 6024 >> DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2018-06-05 16:09:33.438552 6024 >> ImbOdbcStatement::checkRcInner file:F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp line:3850 message:2322.BIPmsgs 'Child SQL exception' , '42000', 1003, '[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.'
2018-06-05 16:09:33.438552 6024 >> DatabaseException BIP2322E: Database error: SQL State ''42000''; Native Error Code '1003'; Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.''.
The error has the following diagnostic information: SQL State ''42000'' SQL Native Error Code '1003' SQL Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. Use the mqsicvp command to test connectivity to this database.
2018-06-05 16:09:33.438556 6024 >> ImbOdbcStatement::checkRcInner file:F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp line:3850 message:2322.BIPmsgs 'Child SQL exception' , '42000', 8180, '[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.'
2018-06-05 16:09:33.438556 6024 >> DatabaseException BIP2322E: Database error: SQL State ''42000''; Native Error Code '8180'; Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.''.
The error has the following diagnostic information: SQL State ''42000'' SQL Native Error Code '8180' SQL Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. Use the mqsicvp command to test connectivity to this database.


I don't know if it is possible to release the connection to Database from ESQL, or i should try to change the design implementation.

Any help would be appreciated.

Thanks
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Jun 05, 2018 7:45 am    Post subject: Re: Accessing DB: 'We still think we have a valid database c Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

jb3 wrote:
I don't know if it is possible to release the connection to Database from ESQL, or i should try to change the design implementation.

Any help would be appreciated.


In no particular order:

- why do you think releasing the connection will help?

- why are you removing yesterday's data using IIB rather than of the many database techniques that can do this?

- why are you trying to fix this when the error being reported back from SQL Server refers to a FOR UPDATE statement, which indicates a database trigger is the source of the problem?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
jb3
PostPosted: Thu Jun 07, 2018 2:15 am    Post subject: Reply with quote

Apprentice

Joined: 18 Aug 2017
Posts: 26

Hi Vitor,

Thanks for your reply the other day. Your questions made me think whether my design was efficient.

Previously, i was doing a daily insert of about 40 rows of lookup data into ESB database, and right after the insert, i was deleting the previous day data. This way i didn't have to compare the data entries in the table with the data coming through daily upload. However i wasn't able to get the Delete to work. In my design, i wanted the delete to happen just after the insert, and did not want to schedule it in sql server at a later time.

But then reading your questions, i have changed the design to check for each incoming data item if it exists in DB, and accordingly update or insert the row. (added insertdata and updatedate columns in table)
This way i will know, if any new dataitem starts coming and if any dataitem stops coming.


Many Thanks,
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Accessing DB: 'We still think we have a valid database conn'
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.