Doubt in a piece of code [Transact-SQL database audit]. - Database Discussion Boards

Kevin Marois15-May-12 7:31

15-May-12 7:31

Ya, most xBase languages are pretty similar in their syntax. If it compiles in one, it should in another with minimal work.

If it's not broken, fix it until it is

Re: xBase question

David Crow15-May-12 7:37

David Crow

15-May-12 7:37

Kevin Marois wrote:
If it compiles in one, it should in another with minimal work.

The problem is I'm converting a portion of it to C.

"One man's wage rise is another man's price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"Show me a community that obeys the Ten Commandments and I'll show you a less crowded prison system." - Anonymous

Re: xBase question

Kevin Marois15-May-12 7:44

Kevin Marois

15-May-12 7:44

Lucky you

The thing to remember is that you really don't know what's in a variable until runtime. In FoxPro you can do

Customer = "Smith"
Customer = CreateObject("Excel.Application") 
Customer = .T.

Now a later version of FoxPro added the 'AS' keyword:

LOCAL CustomerName AS String

but this is for intellisense only and doesn't constrain the variable to a type, so you can still do

LOCAL CustomerName AS String
CustomerName = 10+5

It's not uncommon in older apps to see a variable declared as Public in the startup program, then see its value changed all over the place. This is hell on earth.

Here's a FoxPro forum [^]you can use. I'v been a member there for many years, and most people there are very knnowledegable.

If it's not broken, fix it until it is

Oracle: Replace single occurrence of a character

Lash2014-May-12 8:21

Lash20

14-May-12 8:21

Hi all,

How do you replace a single occurrence of a character in Oracle?

For example, how do I replace a single occurrence of the percent sign with a plus sign:
'my string a%a here' should be 'my string a+a here',
while 'my string a%%a here' should still remain as 'my string a%%a here'.

I know it can be done using REGEXP_REPLACE, but I'm not sure of which regular expression to use to make it replace only a single occurrence.

Any help will be appreciated.

Thanks

Re: Oracle: Replace single occurrence of a character

rana ray15-May-12 0:56

rana ray

15-May-12 0:56

why don't you rely on occurence of special character.if it more than one,don't replace. simply if-else case..

cheers!!!
Vatsa
www.objectiveprogramming.com

Linq to Sql

sindhuan14-May-12 1:10

sindhuan

14-May-12 1:10

Hi I wanna Insert a record into the database by fetching the current user and current date.I have to do this by Linq to sql..As I'm new to this can some one tell me how to do this??

user us= new user();

us.Date = DateTime.Now;

us.name ="";

dc.users.InsertOnSubmit("us");

Is this the right way to do?? us.name should be the login name..how to fetch the user name from db??

Re: Linq to Sql

Kevin Marois14-May-12 11:45

Kevin Marois

14-May-12 11:45

sindhuan wrote:
Is this the right way to do??

No, because

dc.users.InsertOnSubmit("us");

won't even compile, because you're trying to insert a string "ur". Your code is expecting an entity of type user. So you want

dc.users.InsertOnSubmit(us);

So, you first want to query the user to get the name, then insert the new row.

using (MyDataContext dc = new MyDataContext())
{
    var userName = (from u in dc.tblUsers
                    where u.Id = someId
                    select u.UserName).FirstOrDefault();


    user us = new user
    {
        Date = DateTime.Now,
        name = userName
    };

    try
    {
        dc.users.InsertOnSubmit(us);
    }
    catch(Exception e)
    {
        // Handle exception here
    }
}

You will have to adjust the data context and table names and column names, but this should get you started.

If it's not broken, fix it until it is

modified 14-May-12 18:00pm.

Doubt in a piece of code [Transact-SQL database audit].

vValkir13-May-12 21:54

vValkir

13-May-12 21:54

Hi all,

Trying to discover an appropiate way to perform the audit of a database, I have discovered a piece of code that seems to be quite appropiate. This is there:

http://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/[^]

What matters right now is the following lines of code, which are difficult for me to be understood properly:

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

This piece of code is placed inside a trigger writen on Transact-SQL (SQLServer) code. I understand more or less what is the programmer trying to get by using these lines, but what I don't understant (probably because I'm quite new on SQLServer) is what are the 'i.' and 'd.' references. By accessing the link you can discover the full code, but I had not been able to discover anythink on the code preceding it.

Another part of the code above that is a bit confusing is the following:

@PKCols = COALESCE(@PKCols + ' and', ' on')

COALESCE returns the first non-null expression, but it's difficult for me to understand the meaning of the sentence inside the complete expression shown above.

Thanks in advance, any kind of help (links, direct answer...) will be wellcome.

modified 14-May-12 4:25am.

Re: Doubt in a piece of code [Transact-SQL database audit].

Mycroft Holmes13-May-12 22:36

Mycroft Holmes

13-May-12 22:36

You need to take the i and d in context of the rest of the query, they probably represent (alias) the inserted and deleted or possibly data records based on the query.

The coalesce in this case services the composite (2+ fields) primary key requirement.

Never underestimate the power of human stupidity
RAH

Re: Doubt in a piece of code [Transact-SQL database audit].

vValkir13-May-12 22:54

vValkir

13-May-12 22:54

The code preceding the lines I pasted above are the following:

CREATE TRIGGER tr_trigtest ON trigtest FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit INT ,
       @field INT ,
       @maxfield INT ,
       @char INT ,
       @fieldname VARCHAR(128) ,
       @TableName VARCHAR(128) ,
       @PKCols VARCHAR(1000) ,
       @sql VARCHAR(2000), 
       @UpdateDate VARCHAR(21) ,
       @UserName VARCHAR(128) ,
       @Type CHAR(1) ,
       @PKSelect VARCHAR(1000)
       

--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'trigtest'

-- date and user
SELECT         @UserName = SYSTEM_USER ,
       @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) 
               + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

-- Action
IF EXISTS (SELECT * FROM inserted)
       IF EXISTS (SELECT * FROM deleted)
               SELECT @Type = 'U'
       ELSE
               SELECT @Type = 'I'
ELSE
       SELECT @Type = 'D'

-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') 
               + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
       FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

              INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       WHERE   pk.TABLE_NAME = @TableName
       AND     CONSTRAINT_TYPE = 'PRIMARY KEY'
       AND     c.TABLE_NAME = pk.TABLE_NAME
       AND     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

So, there are no mentions about 'd.' or 'i.'. Are "d" and "i" alias for these tables by default?

Re: Doubt in a piece of code [Transact-SQL database audit].

Corporal Agarn14-May-12 4:08

Corporal Agarn

14-May-12 4:08

One thing to remember about T-SQL is that by default if you have a null in an expression the expression is null. Thus

SQL

COALESCE(@PKCols + ' and', ' on')

would return ' on' if @PKCols is null as NULL + ' and' returns NULL.

Also NULL cannot be compared to another NULL as in WHERE NULL = NULL because this returns NULL not true or false. This gets me on occasion. Besides the COALESCE function there is ISNULL, used like

SQL

WHERE ISNULL(@PKCols, '') <> ''

Hope this is more helpful than confusing.

Oracle Question: Split String into two

Lash2011-May-12 5:57

Lash20

11-May-12 5:57

Hi all,

How do you use the REGEXP_SUBSTR function to split a string into two?

Specifically, I want to split a string into two where " and " is the delimiter (note the whitespace).

So for example, if the string is "xande and zem and dem and doe", I want string 1 to be "xande" and string 2 to be "zem and dem and doe".

Any help would be greatly appreciated.

Thanks

Re: Oracle Question: Split String into two

Jörgen Andersson11-May-12 9:08

Jörgen Andersson

11-May-12 9:08

SQL

case 
    when instr(mystring, ' and ') > 0 then substr(mystring, 0, instr(mystring, ' and ')-1)
    else mystring 
end as string1
case
    when instr(mystring, ' and ') > 0 then substr(mystring, instr(mystring, ' and ') + length(' and '))
    else null 
end as string2

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: Oracle Question: Split String into two

Lash2011-May-12 9:33

Lash20

11-May-12 9:33

Worked perfectly. Thanks Jorgen!

Re: Oracle Question: Split String into two

Jörgen Andersson11-May-12 9:47

Jörgen Andersson

11-May-12 9:47

You're welcome

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

timestamp

vijaylumar11-May-12 1:39

vijaylumar

11-May-12 1:39

hi
i could not understand the "timestamp"

what is timestamp and how can i get month from timestamp

what is difference between timestamp and datatime in sql

thanks in advance
vijay kumar

Re: timestamp

Eddy Vluggen11-May-12 1:42

Eddy Vluggen

11-May-12 1:42

MSDN[^] states:
Is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

You're welcome Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

What is database SQL query to display the name of the table only in to datagrid?

LAPEC10-May-12 1:04

LAPEC

10-May-12 1:04

Hello Everyone

I have created a WPF project (C# language) and I also have a MS Access 2007 database.

I'm trying to display the database table name in to datagrid, but i can't figur out the SQL query how to do it. The code below that I wrote it's not correct I think maybe someone can help me please and solve it...

private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            string CmdString = string.Empty;

            using (OleDbConnection myConnection = new OleDbConnection(ConString))
            {
                CmdString = "SELECT FoodMenu FROM MSysObjects WHERE (FoodMenu Not Like 'MSys*') AND (Type In (1,4,6)) ORDER BY FoodMenu";

                OleDbCommand comm = new OleDbCommand(CmdString, myConnection);
                OleDbDataAdapter sda = new OleDbDataAdapter(comm);
                myConnection.Open();
                DataTable dt = myConnection.GetSchema("Tables");   //Get list of user tables  
                foreach (DataRow dataRow in dt.Rows)
                {
                    DataGridMenuTables.Items.Add(dataRow["FoodMenu"].ToString().Trim());
                    //DataGridMenuTables.ItemsSource = dt.DefaultView;
                }
                //myConnection.Close();
            }
        }

Kind regards

Roni

Re: What is database SQL query to display the name of the table only in to datagrid?

PIEBALDconsult10-May-12 3:16

PIEBALDconsult

10-May-12 3:16

LAPEC wrote:
DataTable dt = myConnection.GetSchema("Tables"); //Get list of user tables

That should work.

LAPEC wrote:
//DataGridMenuTables.ItemsSource = dt.DefaultView;

Do you mean DataSource?

LAPEC wrote:
dataRow["FoodMenu"].ToString()

It's already a string, don't use ToString, just cast it ((string) dataRow["FoodMenu"]).Trim()

Re: What is database SQL query to display the name of the table only in to datagrid?

vvashishta10-May-12 4:17

vvashishta

10-May-12 4:17

The simplest way is to create a Stored Procedure and put below mentioned SQL Query to fetch the list of tables in database.. then Bind those records to a DataGrid in C#.

SQL Query is: SELECT * FROM information_schema.tables

- Happy Coding -
Vishal Vashishta

Re: What is database SQL query to display the name of the table only in to datagrid?

Eddy Vluggen10-May-12 7:05

Eddy Vluggen

10-May-12 7:05

I think he's using Microsoft Access, not Sql Server Roll eyes | :rolleyes: