Database

Re: Validate DateTime in storeproc

11-Oct-05 12:14

How do I Validate DateTime in storeproc for a particular format say 'ddmm'?

-Dr_X-13-Oct-05 8:46

-Dr_X-

13-Oct-05 8:46

Look at the IsDate function.

<br />
select IsDate('11/15/05')<br />

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Re: Validate DateTime in storeproc

miah alom13-Oct-05 8:48

Oracle database Question..

13-Oct-05 8:48

Thanks for the reply.

Actually IsDate is not available in SYBASE. So I had to write custom validation.

Thanks again.

Update/Insert Dilema

Vodstok11-Oct-05 3:01

Vodstok

11-Oct-05 3:01

I have a stored procedure that look slike this (it's much bigger, this example is for simplicity):

INSERT INTO Table1 ( Field1,Field2,Field3 )<br />
<br />
SELECT Field1,Field2,Field3 from Table2 where Field4 = @Var

It is grabbing all of the data from one table and importing it into another

here is my problem, i need the proc to be able to tell if the value in Field1 in Table2 already already exists in Table1. If it does, i need it to update table1, otherwise do an insert, as it is new data.

I have seen how to do this with an individual record, but i need to be able to do it with a mass set. the above code currently handles about 29 rows from Table2

______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

Re: Update/Insert Dilema

Michael Potter11-Oct-05 3:54

Michael Potter

11-Oct-05 3:54

Break this down into a SELECT first:

SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)

Then merge them together:

INSERT INTO Table1 (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)

The update is a little more problimatic:

UPDATE Table1
SET Field2 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field2),
SEt Field3 = (SELECT Field3 FROM Table2 WHERE Table1.Field1 = Table2.Field2)
WHERE Table1.Field1 IN (SELECT Field1 FROM Table2)

If you don't want redundant UPDATEs, do the UPDATE call first.

Re: Update/Insert Dilema

Vodstok11-Oct-05 9:15

Vodstok

11-Oct-05 9:15

That is beautiful Big Grin | :-D

Here's a funny story. One of my coworkers came up with a solution for the problem i was trying to address with the above question. (As it turns out, this would not have worked for it).
However, the exact same stored procedure made changes to 2 other tables, and your code here was the perfect solution for them.

For the first time in almost a week, this damn app is actually working the way it was designed Big Grin | :-D

______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

Member 123981310-Oct-05 10:39

Member 1239813

10-Oct-05 10:39

Hi, I have a question in regards to oracle database.. I have a oracle database installed on a server which is a linux system. And now I want to store data into the database which is chinese/jap/korean characters. I used INSERT INTO table VALUES ('X'), where X is a chinese character, but when I retrive the data back from the database it returns as a "?". So, I want to know is there any configuration or anything that I need to set in order to store multi byte char into the database?? I used (VARCHAR) in the database. Thanks

Re: Oracle database Question..

enjoycrack11-Oct-05 16:09

enjoycrack

11-Oct-05 16:09

Hi there,

as I not tried with Oracle before yet. But In SQL, if u want to save unicode characters into DB, the query should be something like "values N('X')"

try with this.

<< >>

Saving and retrieving pictures

Erik Lund10-Oct-05 4:45

Erik Lund

10-Oct-05 4:45

I am working with an access application as front end to a sql server database. One of the tables is a picture table with 2 fields, an Id field and an Image field.

J has problems with saving and retrieving bmp files using the stream object.

I would be happy if anyone has a solution.

The 2 sets of codes are:

Private Sub PickupPicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
Dim MyPath As String
MyPath = CurrentProject.Path & "\Pictures\Temp.bmp"
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MyId & ");"
rs.Open SqlString, db, adOpenStatic, adLockReadOnly
If Dir(MyPath) <> "" Then
Kill MyPath
End If
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.Write rs.Fields("Picture").Value
st.SaveToFile (MyPath) 'full path for bmp file
st.Close
rs.Close
db.Close
UserForm1.Image1.Picture = LoadPicture("")
UserForm1.Image1.Picture = LoadPicture(MyPath)
End Sub

Private Sub SavePicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
SqlString = "SELECT * FROM tbPicture;"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
rs.AddNew
rs!ID = MinId
rs.Update
rs.Close
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MinId & ");"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.LoadFromFile (Image1.Tag) 'full path for bmp file
rs.Fields("Picture").Value = st.Read
rs.Update
st.Close
rs.Close
db.Close
End Sub

Best regard
Erik Lund

Deploying MSDE

steve_m_m10-Oct-05 0:27

steve_m_m

10-Oct-05 0:27

Hello,

I am writing a client server application using VS2003 and MSDE 2000 Rel A. I've created a setup using a VS deployment project to handle both the installation of clients (GUI only) and server (MSDE + GUI) - the user selects an option using a custom dialog. I've also created a custom action to take care of the creation of the database and tables (if the user selected 'server' in the custom dialog). I would like to know whether this is a suitable place to install MSDE if it isn't already installed? I realise that I could use the MSDE Deployment Toolkit but this means maintaining a different setup project for both clients and servers.

Any advice would be much appreciated!

Many thanks,
Steve.

Un-natural sort order...

ModuleKev10-Oct-05 0:20

10-Oct-05 0:20

I have a table in my database called "country"

In it I have "country_id" and "country_name".

I want to write a query that outputs the countryname in order, but the first two would be out of order:

i.e.

UK
USA
Afghanistan
Albania
Algeria
American Somoa
Andora
...
Zambia
Zimbabwe

I know it's possible but stupidly just can't think how. I'd rather do this by SQL than on my ASP page as there will be a couple of places it will be called from.

Please help! Big Grin | :-D

Colin Angus Mackay10-Oct-05 1:09

Colin Angus Mackay

10-Oct-05 1:09

Add an extra column to the table for ordering. For each group have an order number, e.g. UK and USA could be order number 0, everything else could be order number 1. Then in your SQL you can do an ORDER BY OrderNumber, CountryName.

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Nic Rowan10-Oct-05 1:14

Nic Rowan

10-Oct-05 1:14

Aargh! You beat me by two minutes Big Grin | :-D

I like the 0 and 1 idea. I was thinking 1, 2, 3, 4... but I thought that might take too long.

The man who smiles when things go wrong has thought of someone he can blame it on.

If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.

ModuleKev10-Oct-05 1:15

10-Oct-05 1:15

Yeah hadn't thought about doing it that way.

I remember somewhile ago using NULL values in the sql, to be able to play with it, but I've been hammering my head and just can't remember!

Cheers

Nic Rowan10-Oct-05 1:11

Nic Rowan

10-Oct-05 1:11

You could add a "OrderRank" column where you give your own order (which may take a while with countries) and order by that or you could select the two countries into a table variable and then select the rest of the values except for the UK and US. Then just select everything out of the table variable.

-- modified at 7:14 Monday 10th October, 2005

ModuleKev10-Oct-05 1:24

10-Oct-05 1:24

Ok going to be really cheeky.. but what's the basic setup of code?

DECLARE @vCountryName varchar(30), @vCountryName2 varchar(30)

SET @vCountryName = "UK"
SET @vCountryName2 = "USA"

SELECT @vCountryName, @vCountryName2, CountryName
FROM Country
ORDER BY @vCountryName, @vCountryName2, CountryName

I am going to be needing to output it into a single result set.

Nic Rowan10-Oct-05 1:48

Nic Rowan

10-Oct-05 1:48

A table variable is sort of like a tempory table only it doesn't have as much overhead as a temp table.

Here's an example using Northwind:

DECLARE @vTable TABLE (TID Int, TDesc Varchar(255))

INSERT INTO @vTable 
SELECT 
	TerritoryID, 
	TerritoryDescription 
FROM 
	Territories 
WHERE 
	TerritoryDescription = 'Denver'
OR	TerritoryDescription = 'Edison'

INSERT INTO @vTable 
SELECT 
	TerritoryID, 
	TerritoryDescription 
FROM 
	Territories 
WHERE 
	TerritoryDescription <> 'Denver'
OR	TerritoryDescription <> 'Edison'
ORDER BY TerritoryDescription

SELECT * FROM @vTable

(Sorry about the formatting. I've never been able to get it right Smile | :)

)

Personally I would use Colin's method unless there's no way you can add another column...

-- modified at 7:52 Monday 10th October, 2005

ModuleKev10-Oct-05 1:52

10-Oct-05 1:52

Yeah there's a bit more of an overhead with the temp table.

I can put another row in very easily, so will do Colin's route. Was just curious to exactly what you meant.

Thank you very much for your's and Colin's responses though.

Very appreciated.

Big Grin | :-D

miah alom10-Oct-05 5:01

10-Oct-05 5:01

You can use union all to achieve this. This is the sybase syntax. I SQL Server 2000 syntax should be the same.

select * from Country where country_name = 'UK' or country_name = 'USA'
union all
select * from Country where country_name <> 'UK' and country_name <> 'USA' order by country_name

ModuleKev10-Oct-05 5:56

10-Oct-05 5:56

Bingo!!! THANK YOU!!

The final 'order by' on the above in SQL Server, orders the entire rowset. But the above got me thinking, along with Colins original answer of adding a 0, 1 column.

I thought a group by on the individual columns may work, but since my country_id's are all over the place (secondhand data!), that was a no-go. Then thought about adding a 'magic' column to help with the order by.

Finally I've got this, which is exactly what I've been wanting to achieve (n.b. I've got several breakdowns of the UK: United Kingdom (Mainland), United Kingdom (Northern Ireland), etc ):

SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name

I only vaguely remembered that there was a way to do this because I got asked it in an interview earlier in the year, but just couldn't remember how to do it!

Once again THANK YOU!!!!!!!
Smile | :)

miah alom10-Oct-05 6:31