Tag Info

Hot answers tagged

17

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...


12

To check for non-default collations on columns, you can use the following query: select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null order by table_schema, table_name, ordinal_position; Edit: to find the collation of the database, you need to ...


11

REPLACE does not play with wildcards that way. I think you meant: UPDATE [table] SET [column] = REPLACE([column],'TLD.com','TLD.org') WHERE [column] LIKE '%TLD.com%'; You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead: ...


9

Like all coding standards, every project is going to have preferences. I am not saying you will like my preferences but I will offer them. Keep in mind that LedgerSMB is heading towards a model where half of the code will be SQL so we have had to put some thought into these. The first thing we did was decide that DDL should be documented for ...


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc You just don't change the database state when you do. It boils down to whether you read "manipulate" to include "change state"


6

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional ...


5

Make all identifiers snake_case Don't use keywords for identifiers. Name function params with a leading underscore to avoid ambiguities with column names. Use := for assignment in PL/pgSQL instead of = (both are allowed but := is official) Maximum identifier length is 63 bytes. PostgreSQL will silently truncate if too long, so don't make them any longer ...


5

At one time or another I've worked with all of the databases you mention. Unfortunately I have found that it doesn't take very long at all for the syntax to deviate in to their own flavours, for anything other than the simplest SELECT, INSERT, UPDATE and DELETE. When it gets to some of the categories you suggest it will quickly get vendor specific. I've ...


4

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know). Take the below for example: use TestDB; go create table dbo.TestTriggerTable ( id int identity(1, 1) not null, some_int int not null default 1 ); go insert into dbo.TestTriggerTable default values; go 1000 create trigger ...


4

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types. Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? ...


4

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done. In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update). Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, ...


3

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...


3

To answer your question; WHY? You probably already know this by now since the post is 2 years old. But I'll respond just for the record. The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the ...


3

fn_dblog is the way to look backwards as the other commentators have said. Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This ...


2

I think I just figured it out... MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for ...


2

Imposing such rules on db level can be very expensive in terms of resources, so I don't think there is a standard way which will work everywhere. I cannot suggest anything better than raising an error inside the body of BEFORE DELETE trigger (or AFTER DELETE, but BEFORE trigger seems to be more appropriate in this case). Unfortunately, it's quite RDMS ...


2

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


2

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table: CREATE TABLE dbo.UpdateLog ( EventType SYSNAME, Parameters INT, EventInfo NVARCHAR(MAX), EventDate DATETIME NOT NULL DEFAULT GETDATE(), AppName SYSNAME NOT NULL DEFAULT APP_NAME(), ...


2

Normally in OLTP databases, transactions are so quick that we need not to place explicit transactions.Though internally MySQL take care of this and place implicit transaction. But if you think your scripts are long and it will take time to execute and if unrealistic/unwanted results can occur if failed in middle then you must apply explicit transactions.


2

Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) ...


2

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days. As suggested by Kin, have a look at Nacho's ...


1

SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below declare @seqid int select @seqid =next value for from your sequence then in place of sequence ,keep this variable


1

SQL> r 1 with t as ( 2 select 1.0 as x from dual 3 union all select -2.0 as x from dual 4 union all select 3.0 as x from dual 5 union all select -4.0 as x from dual 6 union all select 5.0 as x from dual 7 union all select -6.7 as x from dual 8 union all select 8.9 as x from dual) 9 select sum(case ...


1

I'm going to list a different option here, having come from a development background. At one point, we had developed a client/server application, where we wanted the DDL (for the most part) to be the same between the client and the server. The challenge was the client and the server used different database engines. So what we did was use XML. We defined all ...


1

The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later. Also note: The session sql_log_off ...


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


1

Every time you have perform something atomically you should use transactions. Please understand that (depending on the language/framework/ORM), MySQL may be in auto-commit mode, which means that, functionally speaking, if you are using InnoDB something like this: INSERT INTO innodb_table VALUES (1, 2, 3); gets converted internally into: START ...



Only top voted, non community-wiki answers of a minimum length are eligible