Database

TABLE VALUE FUNCTION for MySQL?

15-Apr-09 13:25

THanks guys

dev

devvvy14-Apr-09 16:20

14-Apr-09 16:20

hello

How do I create a table value function? i.e. return a TABLE instead of say an integer or varchar...

Thanks!

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html[^]

dev

File logging.

Ivan200914-Apr-09 8:50

Ivan2009

14-Apr-09 8:50

Can somebody shed light on what is file logging? Does it stand alone or comes as a part of some javascript?
Is it just a set of some classes used to log errors and events? Waiting for some direction..
Thanks

Re: File logging.

Ashfield14-Apr-09 21:03

Ashfield

14-Apr-09 21:03

And what does it have to do with a database forum?

File logging is just a generic term for logging information to a file, as opposed to say a database. There are loads of examples & free code out there to do it, or just roll your own, its simple enough - just think ahead if you are multi-threading.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: File logging.

Ivan200915-Apr-09 6:09

Ivan2009

15-Apr-09 6:09

Thank you for your reply.

Query

CodingYoshi14-Apr-09 6:23

CodingYoshi

14-Apr-09 6:23

How can I write a query which updates the top 200 rows of a table and then selects those rows? Here is what I have so far:

UPDATE TableX
SET Column2= 'XXXXX'
FROM
(
SELECT TOP 200 Column1, Column2, Column3, Column4
FROM TableX
WHERE Column2 IS NULL
)AS RetrievedRows

WHERE TableX.Column2= RetrievedRows.Column2

In the subquery I grab the top 200 rows, in the parent query I update those rows. But how can I select those rows that were just updated?

CodingYoshi

Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07

Re: Query

Eddy Vluggen14-Apr-09 10:10

Eddy Vluggen

14-Apr-09 10:10

You could consider adding a 'last updated' timestamp, if you don't mind the bit extra overhead. That would simplify selecting the last updated records a tad Smile | :)

I are troll Smile | :)

datacolumn boxing

Maverickcool13-Apr-09 22:02

Maverickcool

13-Apr-09 22:02

hi I have a general question

if I write

row[0][0] = 5;

does a boxing occurs ?

should I write (from perfomance look )

row[0][0] = (object)5;

Mycroft Holmes14-Apr-09 17:05

Mycroft Holmes

14-Apr-09 17:05

What database are you using
what is boxing - not in any SQL I know of

Never underestimate the power of human stupidity
RAH

Maverickcool14-Apr-09 23:55

Maverickcool

14-Apr-09 23:55

i'm using SQL 2005
but the boxing is not done by the SQL server
it's done by the CLR

Mycroft Holmes15-Apr-09 1:18

Mycroft Holmes

15-Apr-09 1:18

Sorry, can't help you there I don't use the cls, I know of no one who does. Stupid idea really, another layer of abstraction so the poor dev does not need to learn TSQL.

However I use convert.to????? in C# rather than boxing.

Never underestimate the power of human stupidity
RAH

multiline dynamic tsql - recognize line break?

jai_10124-Apr-09 21:49

jai_101

24-Apr-09 21:49

In C# there are two types of Convertion is used.

<b>Boxing :convert ValueTypes to Reference Types
UnBoxing :Reference Types to convert ValueTypes </b>

From Your code

row[0][0] = 5; ---> This is implicit Boxing
row[0][0] = (object)5; ---> This is Explicit Boxing

You can use any of these to the Conversion.

This is General DataBase MessageBoard. So don't ask C# Questions here use C# MessageBoard.

Bye,
Jai

Rose | [Rose]

devvvy13-Apr-09 20:22

Re: multiline dynamic tsql - recognize line break?

13-Apr-09 20:22

hello

How can you get a complex multiline dynamic T-SQL to execute, I tried ";" don't work, SQL Anaylzer doesn't recognize line breaks.

SET @SQL =
       'DECLARE @ThisParentId bigint;
        DECLARE @ThisParentType varchar(255);
        DECLARE @CountParent int;

        DECLARE $CURSOR_NAME$ CURSOR FOR
        SELECT ParentId,ParentType
        FROM HIERARCHYMAP
        WHERE
            ChildId = $OBJECTID$
            AND ChildType = ''$OBJECTCLASSIFIER$'';

            OPEN $CURSOR_NAME$;
            FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;

            WHILE @@FETCH_STATUS = 0;
            BEGIN

                SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType;
                IF (@CountParent>0)
                    BEGIN
                    EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$'';
                    END;
                FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;
            END;

            CLOSE $CURSOR_NAME$;
            DEALLOCATE $CURSOR_NAME$;
            '
    SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)
    SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )
    SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )
    SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )

    PRINT @SQL

    EXECUTE @SQL

Thanks

dev

devvvy13-Apr-09 20:46

Difference between datatype uniqueidentifier and int for a primary key?

13-Apr-09 20:46

Problem solved - I am smarter than you all!

<br />
<br />
CREATE PROCEDURE spRecursiveGetParents <br />
(<br />
    @ObjectId bigint, <br />
    @ObjectClassifier varchar(255),<br />
    @TempId varchar(36)<br />
)<br />
AS<br />
BEGIN<br />
	DECLARE @CursorName varchar(100)<br />
	DECLARE @SQL varchar(4000)<br />
	DECLARE @NSQL nvarchar(4000)<br />
	<br />
	SET @CursorName = 'spGetParents_cursor_' + CAST(@ObjectId as varchar(20)) + '_' + REPLACE(CAST(@ObjectClassifier AS varchar(70)), '.', '_')<br />
	<br />
	PRINT @CursorName<br />
						<br />
	INSERT INTO [dbo].[TMP_HierarchyMap] <br />
		(<br />
		ParentId,<br />
		ChildId,<br />
		ParentType,<br />
		ChildType,<br />
		ParentDAOType,<br />
		ChildDAOType,<br />
		[Description],<br />
		CreateDate,<br />
		CreatedBy,<br />
		TmpUID,<br />
		ReferencedId<br />
		)<br />
		SELECT <br />
            ParentId,<br />
            ChildId,<br />
            ParentType,<br />
            ChildType,<br />
            ParentDAOType,<br />
            ChildDAOType,<br />
            [Description],<br />
            CreateDate,<br />
            CreatedBy,<br />
            @TempId,<br />
            Id<br />
            FROM HIERARCHYMAP M1<br />
            WHERE<br />
            ChildId = @ObjectId<br />
            AND<br />
            ChildType = @ObjectClassifier<br />
				AND<br />
				NOT EXISTS(SELECT 1 FROM TMP_HIERARCHYMAP M2 WHERE M1.ParentId=M2.ParentId AND M1.ParentType=M2.ParentType AND ChildId=@ObjectId AND ChildType=@ObjectClassifier AND TmpUID=@TempId)<br />
	<br />
	SET @SQL = <br />
		   'DECLARE @ThisParentId bigint<br />
			DECLARE @ThisParentType varchar(255)<br />
			DECLARE @CountParent int<br />
			<br />
		    DECLARE $CURSOR_NAME$ CURSOR FOR <br />
			SELECT ParentId,ParentType <br />
			FROM HIERARCHYMAP <br />
			WHERE  <br />
				ChildId = $OBJECTID$<br />
				AND ChildType = ''$OBJECTCLASSIFIER$''<br />
				<br />
				OPEN $CURSOR_NAME$<br />
				FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType<br />
					<br />
				WHILE @@FETCH_STATUS = 0<br />
				BEGIN		<br />
					<br />
					SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType<br />
					IF (@CountParent>0)<br />
						BEGIN<br />
						EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$''<br />
						END<br />
					FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType<br />
   				END<br />
					<br />
				CLOSE $CURSOR_NAME$<br />
				DEALLOCATE $CURSOR_NAME$<br />
				'<br />
		SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)<br />
		SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )<br />
		SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )<br />
		SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )<br />
		<br />
		SET @NSQL = CAST(@SQL AS nvarchar(4000))<br />
		<br />
		PRINT @NSQL<br />
		<br />
		<br />
		EXEC sp_executesql @NSQL<br />
END<br />

dev

pzn3xq13-Apr-09 10:07

pzn3xq

13-Apr-09 10:07

I've noticed that some of the aspnet_ tables for security (e.g. aspnet_Roles) uses a primary key of a uniqueidentifer data type which is a very long number.

When should someone use a primary key, where the datatype is a uniqueidentifier as opposed to int and set IsIdentity=Yes?

Thanks

Re: Difference between datatype uniqueidentifier and int for a primary key?

Colin Angus Mackay13-Apr-09 12:16

Colin Angus Mackay

13-Apr-09 12:16

The main reason is because a unique identifier is unique for any database.

One advantage is that if you have to merge databases then you won't class with ID values. With an int then the possibility is quite high unless you carry out a lot of work to prevent it.

Another is security. Because unique identifiers are randomly generated that means a person who has one identifer cannot easily guess the next on in the sequence or find adjacent rows in the database. They could randomly guess, but with the speed of current technology they'll be there a while.

*Developer Day Scotland - Free community conference Delegate Registration Open

converting from varbinary(max) to xml

amer shammout12-Apr-09 22:48

amer shammout

12-Apr-09 22:48

I am working on sql server 2008 I have a table with a varbinary(max) filed
I want to store a row of this table in ather table in xml filde so when I write a query and set in the end of query (for xml auto) an error was occered what can i do

Eddy Vluggen13-Apr-09 0:02

Eddy Vluggen

13-Apr-09 0:02

What did the error message say?

I are troll Smile | :)

amer shammout13-Apr-09 0:17

amer shammout

13-Apr-09 0:17

FOR XML AUTO could not find the table owning the following column 'Logo' to create a URL address for it. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.

Eddy Vluggen13-Apr-09 1:38

Eddy Vluggen

13-Apr-09 1:38

According to the syntax mentioned in this[^] blogpost, I'd assume that your statement would work if you add the BINARY BASE64 option like this;

FOR XML AUTO, BINARY BASE64

I are troll Smile | :)