Re: Encrypt/Decrypt Password Field in Sybase - Database Discussion Boards

Re: SqlConnection - to keep around or dispose

Eddy Vluggen6-Mar-10 1:24

Eddy Vluggen

6-Mar-10 1:24

treefirmy wrote:
Any rationale for either tactic would be greatly appreciated.

I'd open it just before the Connection is used, and close it as soon as I'm done with it; Take a look at tip #3 on Connection Pooling[^].

I are Troll Suspicious | :suss:

Is there a way to change a column property in a table without having to delete the entire table?

tonyonlinux4-Mar-10 8:43

tonyonlinux

4-Mar-10 8:43

I have a database that holds information including isbn numbers for books. I discovered that the isbn is not specifically a 10 digit number it can actually have a character in it so i need to change it to an nvarchar but when i try to do so it says i can't that the table needs to be dropped. Surely I don't have to delete the table and reenter thousands or records just to change that property or do I?
Please help thanks.

Re: Is there a way to change a column property in a table without having to delete the entire table?

Luc Pattyn4-Mar-10 9:05

Luc Pattyn

4-Mar-10 9:05

if you can't change a field, perform these steps:
- add new field (with a new name)
- copy data from old to new field
- delete old field
- optional: rename new field

You can perform these interactively, using SQL Sever Management Studio, PHPmyAdmin, or whatever fits your database.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

Re: Is there a way to change a column property in a table without having to delete the entire table?

tonyonlinux4-Mar-10 9:19

tonyonlinux

4-Mar-10 9:19

wow that worked perfectly. Luc i can't thank you and the other's enough for all your help you have given me. Hopefully one day I will be able to help others and return the favor in which you have given me.
Thanks again.

Re: Is there a way to change a column property in a table without having to delete the entire table?

Tim Carmichael4-Mar-10 10:18

Tim Carmichael

4-Mar-10 10:18

Someday saying thank you... that rated a 5!

Encrypt/Decrypt Password Field in Sybase

MWRivera4-Mar-10 5:42

MWRivera

4-Mar-10 5:42

Hi All,

I ran into an issue when trying to Decrypt a password field that I had previously encrypted and was hoping someone could help. I'm using SQL Anywhere 10.

I encrypted the password field with no issues, using the following trigger when a new record was added to the table:

ALTER TRIGGER "encrypt_new_user_pwd" BEFORE INSERT
ORDER 1 ON "QAS"."tableName"
REFERENCING NEW AS newPwd
FOR EACH ROW
BEGIN
Set newPwd.pwdField = ENCRYPT(newPwd.pwdField , 'key')
END

The problem is I'm unable to decrypt the password of the newly created record (fieldID = 0002), using the following:

SELECT CAST (DECRYPT(pwdField, 'key') AS VARCHAR(100))
FROM "QAS"."tableName" WHERE fieldID = '0002'

In Sybase when trying to execute the above statement I get the following error:

Interactive SQL
The following error occurred while fetching results:
Decryption error: Input must be a multiple of 16 bytes in length for AES
SQLCODE=-851, ODBC 3 State="08001"

Do you have any idea what's causing this error?

The pwdField is of type VARCHAR and size 15.

Thanks,
Mel

Re: Encrypt/Decrypt Password Field in Sybase

David Skelly5-Mar-10 2:06

David Skelly

5-Mar-10 2:06

This is just a guess but if you read the error message it says:

MWRivera wrote:
Input must be a multiple of 16 bytes in length for AES

But you say:

MWRivera wrote:
The pwdField is of type VARCHAR and size 15.

Is 15 a multiple of 16?

Make pwdField bigger and see if that helps.

Re: Encrypt/Decrypt Password Field in Sybase

MWRivera5-Mar-10 9:08

MWRivera

5-Mar-10 9:08

Hi David,

Thanks for the reply. I changed the length of the field to 16 and this stopped the error message from displying, the only problem then was the password was showing up as squares. I then changed the length of the field to 80 (5x16=80) sticking with the multiple of 16 rule and it works great now.

Thanks for your help with this it lead to me working it out. The reason I was recieving squares I asume is because the encrypted version of the password was larger than 16 and so the full encrypted version of it was too big to fit in the password field, therefore when I tried to decrypt it, it wasn't all there to decrypt and hence the nonsense squares.

Thanks again,
Mel

Help on using Transaction

Saiyed Alam4-Mar-10 5:00

Saiyed Alam

4-Mar-10 5:00

Hello friends,

which is the better way to use transaction?
From Database
or from Application?

Re: Help on using Transaction

Not Active4-Mar-10 5:45

Not Active

4-Mar-10 5:45

There is no answer to this, it depends on many factors

I know the language. I've read a book. - _Madmatt

design a database to store an expression tree [modified]

deostroll4-Mar-10 2:06

deostroll

4-Mar-10 2:06

Hi,

I need some help with the database design for storing an expression tree. It should basically store a whole expression.

I'd want to query out data (in one or more result sets as necessary) so that I can finally evaluate it somehow in my front-end application. Just kind of need a table(s) structure to store and represent the relationship.

The expressions I plan to store in the database will be mostly logical expressions. Meaning they'd evaluate to a true or false mostly.

Here is a diagrammatic representation of what I want (as a result set(s)).

modified on Thursday, March 4, 2010 8:31 AM

Re: design a database to store an expression tree

Mschauder5-Mar-10 2:45

Mschauder

5-Mar-10 2:45

Hi,

I often use trees and what you essentially needs is the ID and Parent ID

for example:

ID | ParentID | Name
1 | 0 | A
2 | 1 | AA
3 | 1 | AB
4 | 0 | B
5 | 4 | BA
6 | 4 | BB

looks like:

[]
|
-A
--AA
--AB
-B
--BA
--BB

and so on...

... I hope you mean this D'Oh! | :doh:

ADO.NET Batch Update

MB_KSU3-Mar-10 11:59

MB_KSU

3-Mar-10 11:59

I am using VB.Net 2008 and ADO.Net to do a Batch Update to our Oracle database.
The updates are working, but there is a trigger on the table before the row is updated to enforce a member's termination termination date.
So if I was trying to set the termination date (via the batch update) to 31-Jan-2010 but the member had a claim that was processed on 2-Feb-2010 the trigger would force the termination date to be 2-Feb-2010.
However, the trigger is NOT executing when the batch update runs?

Re: ADO.NET Batch Update

Mycroft Holmes3-Mar-10 21:32

Mycroft Holmes

3-Mar-10 21:32

I would guess that triggers are disabled for batch updates then, probably as a database default. This is a sensible idea, you do NOT want triggers firing in volume and you should be managing your data not relying on a trigger. I hate the bloody things, always causing trouble, hiding under the hood and causing issues bah!

Never underestimate the power of human stupidity
RAH

Re: ADO.NET Batch Update

MB_KSU4-Mar-10 3:56

MB_KSU

4-Mar-10 3:56

Good idea. I'm not a DBA so do you know for sure if there is an admin option that disable a trigger for batch updates?

Re: ADO.NET Batch Update

Mycroft Holmes4-Mar-10 11:14

Mycroft Holmes

4-Mar-10 11:14

MB_KSU wrote:
do you know

No idea, especially on Oracle

Never underestimate the power of human stupidity
RAH

calling stored procedure in case statement - Sql Server 2000

vamsimohan212-Mar-10 23:41

vamsimohan21

2-Mar-10 23:41

Can any one please tell me how to call a stored procedure from case statement.

Thanks In Advance

Re: calling stored procedure in case statement - Sql Server 2000

The Man from U.N.C.L.E.3-Mar-10 1:43

The Man from U.N.C.L.E.

3-Mar-10 1:43

A Case statement in SQL is an embedded part of a Select statement so you can't call a stored procedure in the middle, only a function. However I guess you mean something more like the following:

SQL

If @Test = 1
   Execute [Procedure1];
Else If @Test = 2
   Execute [Procedure2];
Else If @Test = 3
   Execute [Procedure3];
Else
   Execute [Procedure4];

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Re: calling stored procedure in case statement - Sql Server 2000

Pranay Rana3-Mar-10 19:04

Pranay Rana

3-Mar-10 19:04

you can also use case when for this.

Re: calling stored procedure in case statement - Sql Server 2000

Mycroft Holmes3-Mar-10 21:29

Mycroft Holmes

3-Mar-10 21:29

You are still not going to be able to exec another proc from withing the case statement! Case and Exec() do not go together!

Never underestimate the power of human stupidity
RAH

Re: calling stored procedure in case statement - Sql Server 2000

The Man from U.N.C.L.E.3-Mar-10 22:48

The Man from U.N.C.L.E.

3-Mar-10 22:48

No you can't.

To quote the Micosoft documentation[^]:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL)[^].

The question was regarding calling stored procedures and that is clearly not possible from within a case statement.

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Whats wrong with SQL statement?

gengel2-Mar-10 19:47

gengel

2-Mar-10 19:47

Table 1 (UNITS)
ScanID ScanCount OrderID SerialNo ScanStatus ProductionNo
1981 1294 2 147639NZ301774 SHIPPED 10007627289
There are 1300 SerialNo's for ProductionNo That will end with status 'SHIPPED'

Table 2 (Unit_Logs)
LogID SerialNo ProductionNo Old_Status New_Status
1 147639NZ301774 10007627289 PACKED SHIPPED
There will be 1300 records with status changes from PACKED to SHIPPED

Needed RESULT:
ProductionNo PACKED SHIPPED
10007627289 1300 1300

This is what I have tried:
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status
HAVING (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

I added 1 log entry to the table, but the query returns nothing.
Think might need sub query, but not sure where to go from here.

Re: Whats wrong with SQL statement? [modified]

Jörgen Andersson2-Mar-10 21:48

Jörgen Andersson

2-Mar-10 21:48

HAVING is only meant to be used with aggregate functions.
Try something similar to this:

SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED, 
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status

<edit>fixed layout</edit>

"When did ignorance become a point of view" - Dilbert

modified on Wednesday, March 3, 2010 3:54 AM

Re: Whats wrong with SQL statement?

i.j.russell2-Mar-10 22:04

i.j.russell

2-Mar-10 22:04

SELECT UNITS.ProductionNo, 
   COUNT(UNITS.SerialNo) AS SHIPPED,
   COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS 
   JOIN UNIT_Logs
      ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo

Re: Whats wrong with SQL statement?

gengel2-Mar-10 22:46

gengel

2-Mar-10 22:46

Hi,

Thanks for the replies.

This query only seems to make a 1 to 1 join.

So if there is only one log entry it returns both SHIPPED and PACKED as 1 instead of 1294.

ProductionNo SHIPPED PACKED
10007627289 1 1

if i remove:
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

ProductionNo SHIPPED PACKED
10007627289 1294 1294

I tried this mess. Will try a few more options with this.
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED
FROM UNITS
WHERE UNITS.Scanstatus='SHIPPED' AND
(SELECT COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNIT_Logs WHERE UNIT_Logs.Old_Status='PACKED')
GROUP BY UNITS.ProductionNo

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.