Posted on

Caching in Hibernate With Redis

Hibernate is an open-source, object/relational mapping framework for the Java programming language. The goal of Hibernate is to help developers spare themselves from many tedious, manual data processing tasks. Hibernate is able to create mappings between Java classes and database tables, as well as data types in Java and SQL.

Any non-trivial application working with large quantities of data must rely on caching and other techniques to become more efficient. Caching is a strategy to improve the performance of an application by using a buffer to store frequently accessed data. By reducing the number of database requests and storing the data closer to the CPU, caching can significantly increase an application's speed.

Posted on

How to Restore SQL Server Master Database With or Without Backup

Despite being a popular relational database management system, SQL Server often encounters corruption and other issues. For this reason, SQL users often complain about being in trouble and unable to perform some simple tasks. However, it is not always the fault of SQL Server that the users cannot restore the SQL Server master database seamlessly. Sometimes, users do not have sufficient knowledge about the process either, and that is what stops them from successful database restoration. To reduce failed attempts from users, we will talk about restoring the SQL master database in this post. Continue reading to learn about the processes in detail.

Why Do You Need to Restore SQL Master Database?

Master database file (A.K.A. MDF file) is the main user database file that contains all the major data in it. If the database gets infected by a virus or encounters some other severe issue, it is the database file that gets affected in the beginning. In case of database corruption or some other errors, master database restoration is the only solution users are left with.

Posted on

MySQL Tutorial: Understanding The Seconds Behind Master Value

In a MySQL hosting replication setup, the parameter Seconds_Behind_Master (SBM), as displayed by the SHOW SLAVE STATUS command, is commonly used as an indication of the current replication lag of the slave. In this post, we examine how to understand and interpret this value in various situations.

Possible Values of Seconds Behind Master

The value of SBM, as explained in the MySQL documentation, depends on the state of the MySQL slave in general, and the states of MySQL slave SQL_THREAD and IO_THREAD in particular. While IO_THREAD connects with the master and reads the updates, SQL_THREAD applies these updates on the slave. Let’s examine the possible values of SBM during different states of the MySQL Slave.

Posted on

Contributing to Postgres

About once a month, I get this question: “How do I contribute to Postgres?.” PostgreSQL is a great database with a solid code base, and for many of us, contributing back to open source is a worthwhile cause. The thing about contributing back to Postgres is you generally don’t just jump right in and commit code on day one. So figuring out where to start can be a bit overwhelming. If you’re considering getting more involved with Postgres, here are a few tips that you may find helpful.

Follow What’s Happening

The number one way to familiarize yourself with the Postgres development and the code community is to subscribe to the mailing lists. Even if you’re not considering contributing back, the mailing lists can be a great place to level up your knowledge and skills around Postgres. Fair warning: the mailing lists can be very active. But that’s ok, as you don’t necessarily need to read every email as it happens — daily digests work just fine. There is a long list of mailing lists you can subscribe to, but here are a few I think you should know about:

Posted on

The Risks of Using EXECUTE (‘SQL Script’)

Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient.

SQL Prompt’s code analysis rule, BP013, will alert you to use of Execute(<string>) to execute a batch in a string, often assembled dynamically from user input. Use of this command is particularly suspect because it doesn’t allow you to parameterize values.

Posted on

SQL Provision and Azure SQL Database: Creating Local Development and Test Databases

Your organization, like many others, is probably moving towards the use of cloud-hosted database platforms, such as the Azure SQL Database. However, even if some of your production databases are on the Azure SQL Database, you still need to provide development and test copies of that database, whether they are hosted on a local, physical, or virtual machine, on a virtual machine in the Azure cloud, or in the Azure SQL Database. The problem with hosting lots of copies of the database in the Azure SQL Database is that it gets cost prohibitive, it places more restrictions on developers than they often like, and sometimes, network connections and bandwidth cause problems. This means you’ll sometimes need to move a copy of the database from Azure SQL Database to the local system for development and test work.

This article will describe how you can create a local copy of the database from a BACPAC of your Azure SQL Database production database and then use SQL Provision to deploy as many development and test copies (clones) of that database as you need. If your database contains any non-public data, you can also use the Data Masker component of SQL provision to ensure this data is obfuscated before deploying the clones.

Posted on

10 Things Every Programmer and Software Engineer Should Know

If you have been programming for some time and looking to learn to program then you might be thinking about what makes a good programmer. What can a computer science graduate do to prepare for a career in software development and programming? The things expected of a junior developer are some of the common questions I receive from many students on Facebook and Emails who follows me. These are mostly college students who now have access to a wealth of information thanks to the internet and eager to learn things in advance to prepare for their programming job interviews.

In this article, I'll share 10 things which I believe every programmer should know. This includes a programming language like C++ or Java, essential computer science concepts like data structures, algorithms and computer networking basics, essential tools like Git, Microsoft Word and Excel, skills like SQL and UNIX, editors like Eclipse or Visual Studio, and text editors.

Posted on

JVM Advent Calendar: Apache Zeppelin: Stairway to *Notes* Heaven!

Introduction

Continuing from the previous post, Two Years in the Life of AI, ML, DL, and Java, where I expressed my motivation, I mentioned our discussions, one of the discussions was that you can write in languages like Python, R, and Julia in JuPyteR notebooks. Most were not aware that you can also write Java and Scala in addition to Python, SQL, etc. with the help of Apache Zeppelin notebooks. And so, I wanted to share something to broaden everyone’s awareness of Apache Zeppelin and its features. The project itself is written in Java and is an open architecture, which means that Zeppelin can support anything as long as an interpreter for that thing has been provided.

First Things First

In case I have lost some of you, here’s what I meant by JuPyteR notebooks and writing notebooks in different languages. Also, have a look at the list of kernels supported by JuPyteR notebook. In this post, however, we are covering Apache Zeppelin, how to get it to work, and how to use a couple of notes in the Zeppelin environment.

Posted on

Tick Tock…6 Months Until SQL Server 2008/2008 R2 Support Expires Unless You Take Action

If you are still running SQL Server 2008/2008 R2, you probably have heard by now that as of July 9, 2019, you will no longer be supported. However, realizing that there are still a significant number of customers running on this platform that will not be able to upgrade to a newer version of SQL before that deadline, Microsoft has offered two options to provide extended security updates for an additional three years.

The first option you have requires the annual purchase of “Extended Security Updates.” Extended Security Updates will cost 75% of the full license cost annually and also requires that the customer is on active software assurance, which is typically 25% of the license cost annually. So effectively, to receive Extended Security Updates you are paying for new SQL Server licenses annually for three years, or until you migrate off SQL Server 2008/2008 R2.

Posted on

Specify Named Parameters Using the NamedParameterJdbcTemplate

In this article, we will cover how to use NamedParameterJdbcTemplate in a Spring boot application connected to a Postgres Database at the backend. We will be inserting, updating, and deleting employees from Postgres DB using NamedParameterJdbcTemplate. Just to keep the design appropriate, I have separated the dao, service, and controller. Service is just a pass-through in this article.

Overview

NamedParameterJdbcTemplate is a template class that allows a basic set of JDBC operations. It has an underlying classic JdbcTemplate, which allows running native SQL queries with '?' placeholders in execution time for prepared statements. NamedParameterJdbcTemplate implements NamedParameterJdbcOperations interface and holds the reference of JdbcTemplate object in JdbcOperations interface.

Posted on

Introducing Hrorm: A Simple, Declarative, Type-Checked ORM

A Question

A lot of cyber ink has been spilled about the problem of connecting models in Java code with models in relational (SQL) databases. The topic of object-relational mappings (ORMs), is indeed rich, and the full scope of things you might want to do to transfer information between an application and a database is enormous. Many ORM tools attempt to cover as much of that space as they possibly can; their designs and implementations provide for a great deal of flexibility and they are packed with features.

Of course, to achieve that flexibility, they are burdened with complexity. This is not their fault: Java and SQL are quite different worlds and covering all (or even many) of the ways to pull them together is intrinsically complex. What about a different approach? What if instead of designing an ORM tool to handle a wide variety of object and schema models and all the accompanying use-cases, an ORM tool was designed with some assumptions in mind about how objects and schemas look and work and how they are used? How simple could an ORM tool be?

Posted on

CASE WHEN THEN error

Hello i am getting an error on a CASE WHEN THEN syntax on my mySql 

         SELECT  DISTINCT u.uid,c.c_id,u.name,u.profile_pic,u.username,u.email,c.time
                FROM conversation c, users u, conversation_reply r
                CASE
                WHEN c.user_one = '99'
                THEN c.user_two = u.uid
                WHEN c.user_two = '99'
                THEN c.user_one= u.uid
                END
                AND (
                c.user_one ='99'
                OR c.user_two ='99'
                ) AND u.status='1' AND c.c_id=r.c_id_fk AND u.uid<>'1'ORDER BY c.time DESC LIMIT 15

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE
            WHEN c.user_one = '99'
            THEN c.user_two = u.uid
            WHEN c.user_t' at line 3
Posted on

Filter datatable with Like

I am traying to filter Datagrid view based on user type in TbxGlCode (TextBox)

Initialy Datatable is populated from SQL table to TempTable (DataTable)

getting error at runtime at below line

Dim Rows As DataRow() = TempTable.[Select]("Code like '%" & VerCode & "%'")

Exception Unhandled
System.Data.EvaluateException: 'Cannot perform 'Like' operation on System.Int32 and System.String

How to filter datatable with Like option

My Code

Dim TempTable As DataTable

    Private Sub FrmGLCodeList_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' Dim TempTable As DataTable
        'Create New Table Instance
        TempTable = New DataTable

        TempTable.Columns.Add("Code", GetType(Integer))
        TempTable.Columns.Add("Name", GetType(String))

        'Adding Rows to TempTable from SQL table

        Call RSData("Select GlCode as Code , GlName as Name from TblGlCodeMaster")

        Dim I As Integer = 0

        If DBCnCommonDataTable.Rows.Count > 0 Then
            For I = 0 To DBCnCommonDataTable.Rows.Count - 1
                TempTable.Rows.Add(Trim(DBCnCommonDataTable.Rows(I)("Code")), Trim(DBCnCommonDataTable.Rows(I)("Name")))
            Next
        End If

        DgvGlCodeView.DataSource = TempTable

        DBCnClose()
        DgvGlCodeView.Columns(1).Width = 300
    End Sub
    'Filtered data table
    Private Sub FilteredDT()

        Dim VerCode As Integer = Trim(TbxGlCode.Text)
        Dim FilteredDT As DataTable = TempTable.Clone
        Dim Rows As DataRow() = TempTable.[Select]("Code like '%" & VerCode & "%'")
        For Each row As DataRow In Rows
            FilteredDT.ImportRow(row)
        Next

        DgvGlCodeView.DataSource = FilteredDT

    End Sub

    Private Sub TbxGlCode_TextChanged(sender As Object, e As EventArgs) Handles TbxGlCode.TextChanged

        Call FilteredDT()
    End Sub
Posted on

Why this code is not inserting the data on my SQL database

$query = "INSERT INTO 'user-profiles' ('name', 'avatar', 'description', 'votes', 'pay_methods', 'location', 'user_id')
VALUES ('sadj', 'qwesad', 'sadqwdqw', '0','sadsad', 'sadasdsad''4')";

$resultprof = $this -> conn -> query($query);

votes and user_id are INT columns, and user_id is a foreign key.

Making this insert on mySQL directly it works perfectly, but on my PHP the "success" is false....

Posted on

Subscribe to Comments Plugin: Delete Data from Database

During the latest site redesign, I removed the Subscribe to Comments plugin. Wisely, the plugin does not delete any subscriber information from the database. So as a part of the site's redesign slash clean-up, I wanted to export/save and then delete all subscriber information to decrease overall database size. After searching and not finding any specific solution or preferred technique for this process, I rolled my own. Actually it's just a simple SQL query to get it done! :)

Step 1: Backup Database

Before making any changes to your database, it always is a good idea to make a good working backup copy. This way you can restore your previous content should something unexpected happen.

Also, if you want to keep a copy of all your subscriber infos (i.e., names and emails), then take a moment to export those data as well. You never know when/where the information may be useful.

Step 2: Select Data

Using your favorite database UI (e.g., phpMyAdmin), enter the following query:

SELECT * FROM wp_postmeta WHERE meta_key = "_sg_subscribe-to-comments";

Remember to double-check the database prefix (e.g., wp_) and edit to match your own. This query will return all subscriber data added via the Subscribe to Comments plugin. Just so you are aware of what you will be deleting. Take a moment to observe the total number of entries and so forth. Also as another reminder, now would be a good time to grab a copy of the subscriber data you are about to delete.

Step 3: Delete Data

At this point, there is no going back. Enter the following SQL query to delete ALL data collected via the Subscribe to Comments plugin. Again, remember to check and change the database prefix if necessary.

DELETE FROM wp_postmeta WHERE meta_key = "_sg_subscribe-to-comments";

And DONE. All subscriber data should be removed from your database.

Step 4 (Optional): Replace "Subscribe" Link

After deleting all subscriber data and deleting the plugin itself, there may be one more detail to consider. Namely, a way for your readers to subscribe to comments on your awesome posts. There probably a bunch of different approaches or solutions for this.

For example, if your readers are savvy like ours, you can simply replace the "Subscribe to Comments" button with a link to the RSS feed for each post. Here is a screenshot showing how this was done here at DigWP.com:

WP Comments FeedSimple link for users to subscribe to RSS feed for post comments

And the underlying code to make it happen:

<div class="comment-feed"><a href="<?php echo get_post_comments_feed_link(); ?>" title="Post Comments RSS Feed">Comments feed for this post</a></div>

That is added directly in the theme's comments.php template. Of course, some CSS also is used to dial in everything visually. So while comments are open, each post will display an RSS icon and feed link, for example:

https://digwp.com/2018/08/secure-wp-rest-api/feed/

Using some other plugin?

If you are not using the same plugin, Subscribe to Comments, the previous SQL queries will return zero results. But the concept is the same regardless of plugin. The only thing you need to find out is the name of the database column (and/or table if necessary), and then modify the previous queries accordingly.

Why Remove Subscribe to Comments?

The main reason is that we don't get a lot of comments these days, so it's not really necessary. But more importantly, I removed the plugin to eliminate all the errors. Well, not errors they were just PHP Notices or warnings. But there were a LOT of them. Like many megabytes worth of log entries every month. So basically endless log spam. It was just time to move on.

The Subscribe to Comments plugin was an essential and awesome plugin that served us well for many years. So total respect and props for the amazing run :)