#SQLUpdates

[memeticsolutions01]

SQL Server 2022 CU15: What’s New and Why It Matters | Memetic Solutions

SQL Server is a crucial tool for managing and analyzing large amounts of data. To ensure smooth performance, security, and stability, Microsoft frequently updates it. The Cumulative Update 15 (CU15) for SQL Server 2022 is the latest release, packed with improvements and fixes. In this blog, we’ll break down the key features and benefits of CU15 to keep you informed about the latest changes.

Key Features of the CU15 Update

Improved Performance

CU15 focuses on improving memory management, query performance, and database engine stability.

It fixes problems like unexpected crashes during certain database operations and query delays under heavy workloads.

This update helps administrators ensure more stable and efficient database performance.

2. Enhanced Security

Security is a top priority. CU15 addresses security vulnerabilities found in previous versions.

It provides safeguards against data corruption and unauthorized access, keeping your data safe.

By applying CU15, businesses can stay compliant with security regulations and protect their systems from threats.

3. Better Integration with Cloud Services

CU15 enhances support for hybrid environments, making it easier to integrate with cloud services.

It improves interoperability with other Microsoft tools, allowing businesses to fully leverage the potential of the SQL Server ecosystem.

How Memetic Solutions Can Help?

Memetic Solutions specializes in providing tailored technology solutions to drive business growth and productivity. For companies using SQL Server, Memetic Solutions offers:

Expertise in cloud solutions and seamless integration services.

Help with enforcing compliance, improving database performance, and strengthening security measures.

Support with applying the latest updates like SQL Server CU15 to minimize downtime and maximize productivity.

By partnering with Memetic Solutions, businesses can ensure a high-performing and secure environment, while taking full advantage of the latest technological advancements.

[sonerorhan]

Sql ile Sağdan Karakter Silme

LEFT(kolonadi,CHARINDEX('/',kolonadi)-1)

İSTENİLEN: / Karakteri sonrası tüm karakterleri silme...

=======================================

Önce sorgu kontrol.....

SELECT CODE=LEFT(CODE, CHARINDEX('/',CODE)-1) FROM LG_001_ITEMS WHERE CODE LIKE 'TM%' =>> tm ile başlayan tüm kodlar

Sonra Güncelleme.........

UPDATE LG_001_ITEMS SET CODE=LEFT(CODE, CHARINDEX('/',CODE)-1) WHERE CODE LIKE 'TM%'

TEHLİKELİ DURUM : KOD FAZLALIĞI SİLİNDİKTEN SONRA ÇAKIŞMA OLURSA ?

Invalid length parameter passed to the LEFT or SUBSTRING function.

The statement has been terminated.

[coffeewheat]

SQL Update script

/* sets NotionalCurrency to Deliverablecurrency */ SET XACT_ABORT ON SET NOCOUNT ON PRINT N'Preparing data.' declare @transactionsToUpdate table ( DeliverableCurrency1ExtRefInID int, DeliverableCurrency2ExtRefInID int, NotionalCurrency1ExtRefInID int, NotionalCurrency2ExtRefInID int) BEGIN PRINT 'Starting..' DECLARE @cnt INT SELECT @cnt = COUNT(*) FROM @transactionsToUpdate BEGIN TRANSACTION /* UPDATE CODE HERE. Don't forget to fill AuditLog */ IF(XACT_STATE() = -1) BEGIN ROLLBACK TRAN PRINT N'ROLLED BACK' END ELSE BEGIN IF(@@TRANCOUNT = 0 ) BEGIN PRINT 'ERROR OCCURED' END ELSE BEGIN ROLLBACK TRAN PRINT N'HA HA HA! transaction was ROLLBACKED because COMMIT is commented!' --PRINT N'COMMITED' --COMMIT TRAN END END END

[tencentdeduction]

Using JDBI with Spring Boot

JDBI is a nice little ORM for Java applications. It offers a nice level of abstraction to make it simple to map SQL to Java objects without a ton of confusing overhead. Spring Boot is a nice framework to make Spring applications without a lot of configuration. That said, I found it quite confusing the first time I tried to wire up JDBI classes to Spring Boot, and only figured it out eventually by reading the JDBI source, and scouring github for projects that use JDBI. Dropwizard, on the other hand, makes it easy to integrate JDBI, but if you like Spring Boot, you can still use JDBI.

Here's a quick guide to getting started with JDBI in Spring Boot. I created a GitHub repository to go along with this code that you can follow along with if you like.

Set Up Your Spring Boot Project

There's plenty of good documentation to explain how to set up a Spring Boot project, so I'll skip explaining that step here. We'll start with a project that followed the quick start guide. Open up your Application.java file, then we'll add the necessary parts in turn to wire things up.

First, we add a datasource to our project in Application.java:

@Bean @ConfigurationProperties(prefix = "spring.datasource") public DataSource dataSource() { return DataSourceBuilder.create().build(); }

This is a pretty standard spring datasource. You'll also want to add a configuration for this datasource to your application.properties file:

spring.datasource.url = jdbc:h2:mem:test spring.datasource.driver-class-name = org.h2.Driver spring.datasource.initialize = true

And create an h2 schema.sql in your resources that will be initialized when we start our app:

CREATE TABLE example ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(255) );

This is the same as any other ORM so far.

Wiring Things Up

Now for the tricky bits in your Application.java:

@Autowired @Bean public DBI dbi(DataSource dataSource) { synchronized (DBI.class) { return new DBI(dataSource); } }

This bean creates a new DBI instance that without spring you would be creating with new. Next, we wire a JDBI DAO object that uses the JDBI Object API:

@Bean @Autowired public ExampleDao exampleDao(DBI dbi) { return dbi.onDemand(ExampleDao.class); }

(The source to this DAO object is available on github)

That's it. You can now use your ExampleDao anywhere in your springified objects. There's a use of the DAO in the controller in the example code. However, it kind of feels like a lot of work to manually create a bean for every DAO.

Wiring Things Up Without Manually Creating Beans

One solution to simplify things is to wrap each DAO object in another higher level object, with the inner class only containing abstract JDBI methods, and if you need extra Java code to fill in JDBI's gaps (such as constricting WHERE clauses) you can put this in the outer Java class. Here's an example:

package hellojdbi; import org.skife.jdbi.v2.DBI; import org.skife.jdbi.v2.sqlobject.Bind; import org.skife.jdbi.v2.sqlobject.BindBean; import org.skife.jdbi.v2.sqlobject.GetGeneratedKeys; import org.skife.jdbi.v2.sqlobject.SqlQuery; import org.skife.jdbi.v2.sqlobject.SqlUpdate; import org.skife.jdbi.v2.sqlobject.customizers.Define; import org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper; import org.skife.jdbi.v2.sqlobject.stringtemplate.UseStringTemplate3StatementLocator; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import java.util.List; @Repository public class ExampleDao { 
 private ExampleDb db; 
 @Autowired 
 public void setDbi(DBI dbi) { 
 this.db = dbi.onDemand(ExampleDb.class); 
 } 
 public long create(Example example) { 
 return db.create(example); 
 } 
 public Example get(long id) { 
 return db.get(id); 
 } 
 public List<example> getWhere(String name, long minimumId) { 
 String where = String.format("name = '%s' AND id > %s", name, minimumId); 
 return db.getWhere(where); 
 } 
 @UseStringTemplate3StatementLocator 
 @RegisterMapper(ExampleMapper.class) 
 public static abstract class ExampleDb { 
 @SqlUpdate("INSERT INTO example (name) values (:name)") 
 @GetGeneratedKeys 
 abstract long create(@BindBean Example example); 
 @SqlQuery("SELECT * FROM example WHERE id = :id") 
 abstract Example get(@Bind("id") long id); 
 @SqlQuery("SELECT * FROM example WHERE <where>") 
 abstract List<example> getWhere(@Define("where") String where); 
 } 
}

Now you can remove the bean you added earlier in your Application.java, and things will still work, no need to manually wire up the DAO. This has a nice side effect of separating the abstract class that JDBI consumes from DAO code that is written in Java. You can see the code wired up like this in the github repository.

Now you can easily plug your DAO code into your controllers or business logic classes, without needing to manually wire them up.

Using your DAO

Here is an example controller that uses our ExampleDAO:

package hellojdbi; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.stream.Collectors; @RestController public class HelloController { private ExampleDao exampleDao; @Autowired public void setExampleDao(ExampleDao exampleDao) { this.exampleDao = exampleDao; } @RequestMapping("/") public String index() { Example example = new Example(); example.setName("My Example"); long exampleId = exampleDao.create(example); Example createdExample = exampleDao.get(exampleId); List<example> wheres = exampleDao.getWhere("My Example", 0); String wheresJoined = wheres.stream() .map(Example::getId) .map(i -> i.toString()) .collect(Collectors.joining(", ")); return String.format("Created example '%s' with id '%d'\nFound %s", createdExample.getName(), createdExample.getId(), wheresJoined); } }

That's it! Pretty straightforward once you understand what needs to be done, but not so simple to figure out without some documentation.

Feel free to get at me on twitter if something is wrong or confusing.