Error with DBGET - Need Urgent Help

Answered Question
Apr 21st, 2010
User Badges:

Hi,


I am using a query to read(DBREAD) an encrypted password from an SQL DB, and decrypting while reading as per following query:


Select test.dbo.BlowFishDecode(password,data.dbo.BlowFishKey()),EAvenue,AccountStatus from test.dbo.FMP002_EncryptPWD where

CHLDNO = $Unit_Holder_No


But when next I use DBGET function(in the successful branch of DBREAD) to assign values from the field password,EAvenue,AccountStatus to local variables, the control goes to SQL error branch.


Question: What are the possbible reasons for error. The Cisco web site says this means


"There was an error in the SQL command"  . But my SQL statement is correct!!


Thanks

You have to be careful with DBGET.


SELECT test.dbo.BlowFishDecode(password,data.dbo.BlowFishKey()), EAvenue, AccountStatus

FROM test.dbo.FMP002_EncryptPWD

WHERE CHLDNO = $Unit_Holder_No


You are trying to run a function in SQL. The output of this function needs to have a column name that DBGET can work with.


You could try


SELECT test.dbo.BlowFishDecode(password,data.dbo.BlowFishKey()) AS PlainTextPwd, EAvenue, AccountStatus

FROM test.dbo.FMP002_EncryptPWD

WHERE CHLDNO = $Unit_Holder_No


and do a DBGET on PlainTextPwd.


Regards,

Geoff

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Correct Answer

You have to be careful with DBGET.


SELECT test.dbo.BlowFishDecode(password,data.dbo.BlowFishKey()), EAvenue, AccountStatus

FROM test.dbo.FMP002_EncryptPWD

WHERE CHLDNO = $Unit_Holder_No


You are trying to run a function in SQL. The output of this function needs to have a column name that DBGET can work with.


You could try


SELECT test.dbo.BlowFishDecode(password,data.dbo.BlowFishKey()) AS PlainTextPwd, EAvenue, AccountStatus

FROM test.dbo.FMP002_EncryptPWD

WHERE CHLDNO = $Unit_Holder_No


and do a DBGET on PlainTextPwd.


Regards,

Geoff

ra_jeshkalra_2 Wed, 04/21/2010 - 09:22
User Badges:

I Geoff,


Thanks, You got it correct.


I also just managed to do some research and come out with the following:


Select BlowFishDecode(password,test.dbo.BlowFishKey()) as password,EAvenue,AccountStatus from test.dbo.FMP002_EncryptPWD where CHLDNO = $Unit_Holder_No


and doing DBGET on password   now


Thanks

ra_jeshkalra_2 Wed, 04/21/2010 - 23:02
User Badges:

Geoff,


"do a DBGET on PlainTextPwd."


How to do this? Since in th DBGET Filedselection TAB ,  for the table/view, when I click ADD to add the fileds, I don't see this. I only see the existing fileds of the table.


Thanks

If I recall correctly, you have to make CRS believe there is a column somewhere in the DB with that name so DBGET allows you to select it.


There have been similar questions asked here on how to deal with stored procedures, and there have been recommendations - I think it involves creating a table in the database with a column that matches the name you use in the "AS PlainTxtPwd" clause then DBGET can see it.


See if you can find those posts.


Regards,

Geoff

Actions

This Discussion