Posts tagged sql
I had three data with where gender=female. I was trying to call those data with limit and offset (Since in future there might be more than 1000 data). SELECT * FROM `users` WHERE `gender`='Female'...
Is it possible to add listeners via VBScript/ JS directly into a SRSS report? Goal: Track changes of input fields and key press inside the wrapper React based on the events Directly in the...
I have to migrate a bunch of text from an old application into a new one. Some of these texts contain HTML tags and entities (HTML editor was used) and now they do not want to support this in the n...
I have the following scenario (in MySQL 8): CREATE TABLE `steps` ( `id` int NOT NULL AUTO_INCREMENT, `number` varchar(30) DEFAULT NULL, `parent_number` varchar(30) DEFAULT NULL, `timestamp` ti...
Let's say I have a table with 10,000 rows and instead of selecting all of the rows at once I select 1,000 at a time like LIMIT 0,1000 LIMIT 1000,1000 etc. Without an ORDER BY statement the or...
One of the legacy applications my team has to maintain has almost always this pattern for dealing with data modification: try { // get the connection // begin transaction // optional...
A person I used to work with several years ago was hired to rewrite a product using a .NET-based modern tech stack. One of the aspects that stroke me was that he believes that the product should mo...
Our team is currently transferring all functionality (+ some changes) from small and very old project A (almost code freeze) to project B (actively developed). As part of the data migration, there ...
I am building an MVC cart system. I have the MERCHANTS table and the PRODUCTS table. A merchant (store) can have one or more products, everything works fine but what I want is to display products b...
The context A reports related process is directly reading the production operational database once about two hours. This involves reading all the data from some 70 tables which takes a couple minu...
Despite the fact that I have programmed against SQL Server for quite a while I did not pay much attention to the tempdb database. This is especially true if application logic is mostly written usin...
query = "CREATE TABLE "+TABLE+" ("+COLUMN1+" INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT, "+COLUMN2+" VARCHAR(255), " + COLUMN3+" VARCHAR(255), "+COLUMN4+" TEXT, "+ COLUMN5+" VARCHAR(255)...
What's the better way to store base64 in SQLite Database? I was thinking to put base64 as TEXT since base64 has lots of chars. CREATE TABLE name (id INTEGER PRIMARY KEY AUTO_INCREMENT, image TEXT)...
I have a column that is a concatenation of 5 other columns plus a join to a different table. UPDATE db.a JOIN db.b ON fk_b = b.pk SET concat_field = CONCAT(field1,field2,field3,b.field,field4,fi...
I had installed mysql using the following command sudo pacman -Sy mysql Earlier, I was using Lampp(Xampp for Linux) where I could start using the command /opt/lampp/start start I am not sur...
I have recently an issue at work after upgrading an ASP.NET Core 3.1 application to .NET 5. It worked correctly on all environments (e.g. PreProd, Prod) when targeting 3.1, but failed on Prod only ...
In some situations, could it be problematic to migrate a website's database (MySQL) from one website hosting provider to another without the information schema or with the information schema? In m...
I had a curiosity about how much the experienced users wait for their questions to be answered on Stack Overflow and had written a query for it: SELECT YEAR(q.CreationDate) * 100 + MONTH(q.Creatio...
I have a parent child relation in my table, with possibly circular cases. Is it possible to break the infinite recursivity in CTE checking values of all previous rows? I would need something like t...
On 25th October 2020 in Europe/Berlin clocks where set back from 03:00 AM to 02:00 AM to change from summer time (CEDT) to winter time (CET). Which means there is a 1 hour separation between 02:30...
For my purposes, it is secure enough to use passwords to execute MySQL commands on the command line, what I would like to do is disable the warning that says, mysql: [Warning] Using a password on ...
I have extensive working experience with Entity Framework ORM and have noticed two major ways of writing LINQ (LINQ2SQL): lot of eager loading: the most prevalent, uses (lots) of Includes to eag...
Let's say we have two tables A and B and a join table C that has foreign keys to both A and B and the combination of those foreign keys is unique. One could either do a unique constraint or a comp...
Its possible to execute a MySQL file from the command line like so, mysql -u USER -pPASSWORD < FILENAME which triggers a warning, mysql: [Warning] Using a password on the command line int...
In a stored procedure let's say I have a VARCHAR variable, DECLARE example_variable VARCHAR(100); how much memory is allocated to it and is that independent of the length of the data in the var...
Lets say I have two tables, A and B and I need to join a subset of them. Is there best practices of sticking the conditions in the WHERE clause like this, SELECT * FROM A JOIN B on a.fk_b = b....
Due to a series of unfortunate events I have some tables where the auto_increment value got behind what it should be. If the auto_increment value is 9 and there are 20 rows in the table the next 1...
I have a PHP program that does a SELECT and then updates some of the values based on an algorithm. Rather than updating one row at a time UPDATE example_table SET COLUMN_A = 1 WHERE primary_k...
I have a very large table that I need to iterate through with a PHP script and I can't do all of the results at once so I do it in sections with a LIMIT like for ($x = 0; $x < 10000000; $x += 1...
So I had a table with a primary key and a bunch of different columns. Columns A, B, and C were all unsigned ints (like the primary key column) and each column had a unique constraint I dropped the...
I have tables A and B and then I have a many to many join table with foreign keys to both called a_b. Neither foreign key can be null and the combinations for the foreign keys to A and B are unique...
I need to drop all of the stored procedures from my MySQL database before I recreate them. How can I do so?
I have a query that returns the owner of jobs on an SQL instance, select s.name as JobName , s.owner_sid , ISNULL(L.name,'AccessViaGroup') as LoginName --Trying to figure out how to turn that ...
My project has the following set up for the production and preproduction ("clone") environment. Production is not accessible at all for the development team, only the preproduction database. Pr...
Currently, we have a CRUD plus reporting application that talks to one MySQL database. Intermittently users will report locks when searching, currently, I can only get the approximate time of when ...
Almost every table in my DB has triggers that fire on INSERT, UPDATE, DELETE and write the changes to a separate read only DB. This makes it possible to track changes and undo things well after the...
I found a confusing construction in several stored procs in an MS SQL 2008 R2 database: DATEADD(dd, 0, DATEDIFF(dd, 0, some_date)) As I understand it, these are the relevant function signatures: D...
I am considering either removing some columns or changing the datatypes if I could significantly reduce the amount of storage that is currently used by those columns. Some of the columns are ints a...
I have a database with quite a few VARCHAR fields. When the database was first built the lengths of the columns were set a bit larger than absolutely necessary. Now after, having used the DB for a ...
I just completed my first install of SQL as a server-based, Enterprise Edition. I wanted to check it out using SSMS on PC using my Admin Access. But could not connect. For security reasons my adm...
Sometimes, either to reduce the amount of typing big table names or when joining a table to itself one will need to alias a table. Personally I find SQL statements with aliases harder to read and a...
I have some SQL scripts that contain DDL to create tables and schemas for a database. I'd like to be able to comment this SQL and then use those comments to generate output documentation (in HTML)...
I have two tables with millions of records. Every so often I need to join them and exclude just a handful of records where a bit(1) column is set to 1 instead of 0. I can do it with either, WHERE ...
I have noticed some time ago that Entity Framework assumes a CASCADE behaviour (implicit value, if not specified) for referential constraints (FKs) when deleting items. This means that by default, ...
In programming instead of arrowcode where one has many layers of indented if statements, you can return a result as soon as possible. So instead of, if if end if end if It looks like if ret...
I have a number of MySQL stored procedures that use a cursor to go through a select and then pass the results to other stored procedures one row at a time. This can take a while to run, what is hap...
From time to time I will have large structural changes to make on my MySQL DB, things like adding columns, changing datatypes, adding indexes etc. These types of changes result in downtime and what...
I have a bunch of stored procedures that look like something this CREATE PROCEDURE example() BEGIN UPDATE STATEMENT A; UPDATE STATEMENT B; UPDATE STATEMENT C; END// When I run them thr...
If one finds where the past number of months equals the past number of days like this, select distinct DATE_SUB(now(), INTERVAL 92 DAY),DATE_SUB(now(), INTERVAL 3 MONTH) The numbers that are curre...
I need to add one column with the same name to all of the tables in my database, how can I find which tables don't currently have a column with that name?
