Tag: Stored

stored procedures – Process of updating a record

Help determining the process of updating a record.

Coming from an ORM background with Entity Framework this was the process:

  1. Retrieve field from the database and populate entity.
  2. Access the entity object and change the fields you want to update
  3. Save the entity to the database context, and in the case of Entity Framework, it will go through all your entities, and compare them with their original values, and if any has changed, it will execute an update statement.

What is the approach like when not using an ORM like EF?
I have classes that are a 1:1 mapping to my db schema, so should I follow this same approach when trying to update a record via stored procedure?

I have crafted functionality of accepting an xml parameter in a stored procedure, where I can optionally pass in any piece of data I want for the update, so in reality, I don’t have to get data to conform to one of the aformentioned classes for the update…

For example, if I had a users table with firstname and lastname and I only had the need to update firstname. I could just pass firstname to the stored procedure to update.

networking – Where is network data stored while awaiting to be written to disk?

I think download rates may depend on these 3 factors: the Network Infrastructure (router, VPN, network card, towers), the RAM or cache, the Final Storage (HDD or SDD or whatnot).

So we take this as an example: I have a very slow HDD Drive and very slow RAM but a very fast network adapter & router.

When downloading, for example, a Linux Live ISO, the adapter will download it fast but yet all content that is downloaded is not written to disk immediately.

So my plausible solutions would be:

  1. The Internet Stack (TCP/IP or whatever) allow clients to defer receiving network packets. So that a client could say to aserver directly: Yo, I’m still struggling with the data I received, would you mind sending me the next data after (when I tell you to do so).
  2. A software library pauses receiving data when struggling.
  3. Data is stored somewhere else (other than cache or RAM) (maybe on the router?)

Is there a one real answer or it depends?

How can I query on-premise SQL Server database from Azure SQL database using stored procedure?

I have a Azure SQL database and it has a stored procedure from where I am trying to join a table which resides in on-premise SQL server database.
Essentially, I am trying to query a table which sits in on-premise SQL server’s database.

Are there any options to make cross-server queries from Azure SQL database?

Can a stored procedure increase performance on single-statement queries?

Let’s assume a trivial update query

UPDATE  t1
LEFT JOIN
        t2
ON      t2.id = t1.id
SET     t1.col1 = 'foo', t2.col1 = 'bar'
WHERE   t2.id IS NULL

Would wrapping the statement in a stored procedure significantly reduce execution time, considering the query would be invoked at a speed of 10.000 calls per minute?

mysql – Stored procedure retrieve

Is their any possibilities to retrieve stored procedure condition which is applied for insertion after insert..? Mysql php.

You have to drop it and create it a new

Also php and mysqli don’t add DELIMITER to the CREATe PROCEDURE, so you have to do it yourself.

So the code must loook like

DROP PROCEDURE IF EXISTS Insertion;
DELIMITER $$
CREATE PROCEDURE Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN department varchar(40),IN doj date,IN basicpay int(11))
BEGIN
  
  DECLARE HRA decimal(20,2);
  DECLARE DA decimal(20,2);
  DECLARE PF decimal(20,2);
  DECLARE NET_SALARY decimal(20,2);
  
  IF department = 'HUMAN RESOURCE' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;
  
  ELSEIF department = 'MARKETING' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;
  
  ELSEIF department = 'PRODUCTION' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;
  
  ELSEIF department = 'FINANCE AND ACCOUNTING' THEN
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;
  
  
  ELSE 
  SET HRA = (5/100)*basicpay;
  SET DA = (7/100)*basicpay;
  SET PF = (10/100)*basicpay;
  
  
  END IF; 
SET NET_SALARY = basicpay+HRA + DA + PF;
    
  insert into employees(FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT,DATE_OF_JOINING,BASIC_PAY,HRA,DA,PF,NET_SALARY)
  values(firstname,lastname,email,department,doj,basicpay,HRA,DA,PF,NET_SALARY);  
END$$
DELIMITER ;

How to get applied stored procedure for inserted row in mysql php query

$query = “CREATE PROCEDURE IF NOT EXISTS Insertion(IN firstname varchar(40),IN lastname varchar(40),IN email varchar(40),IN department varchar(40),IN doj date,IN basicpay int(11))
BEGIN

DECLARE HRA decimal(20,2);
DECLARE DA decimal(20,2);
DECLARE PF decimal(20,2);
DECLARE NET_SALARY decimal(20,2);

IF department = ‘HUMAN RESOURCE’ THEN
SET HRA = (5/100)*basicpay;
SET DA = (7/100)*basicpay;
SET PF = (10/100)*basicpay;

ELSEIF department = ‘MARKETING’ THEN
SET HRA = (5/100)*basicpay;
SET DA = (7/100)*basicpay;
SET PF = (10/100)*basicpay;

ELSEIF department = ‘PRODUCTION’ THEN
SET HRA = (5/100)*basicpay;
SET DA = (7/100)*basicpay;
SET PF = (10/100)*basicpay;

ELSEIF department = ‘FINANCE AND ACCOUNTING’ THEN
SET HRA = (5/100)*basicpay;
SET DA = (7/100)*basicpay;
SET PF = (10/100)*basicpay;

ELSE
SET HRA = (5/100)*basicpay;
SET DA = (7/100)*basicpay;
SET PF = (10/100)*basicpay;

END IF;
SET NET_SALARY = basicpay+HRA + DA + PF;

insert into employees(FIRST_NAME,LAST_NAME,EMAIL,DEPARTMENT,DATE_OF_JOINING,BASIC_PAY,HRA,DA,PF,NET_SALARY)
values(firstname,lastname,email,department,doj,basicpay,HRA,DA,PF,NET_SALARY);

END”;

sync – Best way to share Shortcut to File stored in SharePoint – Can’t access URL in File Explorer without Opening IE First

I’m trying to find ways to share a shortcut to a file (Excel Add-In) I have stored in MS Teams/Sharepoint.

So far I’ve gotten it to work via “Sync/Shortcut” and by opening the files in explorer and creating a shortcut then copying it.

I looked into opening files via “File Explorer” without using a shortcut/sync feature by using “Classic Mode” in IE to open files, but I noticed that FE wasn’t able to get back to the FE URL unless the user has first opened it via IE and even then, it must be done each restart of the computer! So, that method seems out unless somebody knows a fix for that.

I tried using:

start "" "%systemroot%explorer.exe" "\domain.sharepoint.com@SSLDavWWWRootsites..."

and

"%systemroot%explorer.exe" "\domain.sharepoint.com@SSLDavWWWRootsites..."

Today I resigned to at least get it working, and setup instructions on how to use “Sync” which is the reccomended method by microsoft, but then I found when I tried to hyperlink the “deploy” script, that MS Word doesn’t allow Environment Variables (%USERPROFILE%Sharepoint...) inside Hyperlinks in a word document!

Now I’m wondering if there is a way I can prevent users from accessing/downloading files unless access via “Sync”, but I don’t like how “Sync” doesn’t make a shortcut under File Explorers “One Drive”.

I see now that if I add a shortcut AFTER sync, it won’t place a shortcut under “One Drive” in FE, but I’m wondering if I add a shortcut, then sync, do I get the benefits of Sync and it’s under File Explorer? Note: I just tried to sync after shortcut and it says “You’re already syncing a shortcut tot a folder from this shared library”.

https://support.microsoft.com/en-us/office/add-shortcuts-to-shared-folders-in-onedrive-for-work-or-school-d66b1347-99b7-4470-9360-ffc048d35a33#:~:text=If%20you%20add%20a%20shortcut,general%20availability%20in%20September%202020.

As it stands now, I think I’ll direct users to hit “Add a Shortcut”, then navigate via “File Explorer” as I can’t use Hyperlinks still to reference “One Drive” in the MS Word document that details this process and then create the shortcut/run the deploy script.

Does anybody have suggestions on a best approach for this?

See https://superuser.com/questions/1434775/hyperlink-to-a-file-using-the-current-user

file format – How are 360° photos stored and processed?

There are no finer details.

Let us start from the beginning.

A file

You have a camera with a lens and the file stores what was projected on the sensor.

You can change the lens, for example to a wide angle lens and the file just do the same. No black magic there.

You can then store an image taken with a fisheye lens. The file itself has no idea and stores the image exactly the same way.

A file format has no way to differentiate what is stored in it.

360 views

Ok. Now let’s take a look at some types of “360” images.

A 360 image for start is one that contains information on what is around you. Behind you, in front, to the left, to the right.

The most primitive way to capture this information is by taking a photo of a metallic reflective sphere. This contains information about (almost) all your surroundings.

360 images from a spherical reflective ball

This is a “Probe Map” projection. But there are several others. Cubic projection, Spherical projection, Cylindrical projection.

What now has popularized these images is the ability to compute these transformations and unwrap them again.

So this simple Jpg with a distorted projection is projected into a 3D “solid” from the inside and then you see a section of it, and you can move around.

This solid can be a sphere or a cube, depending on the application. (Cube is the most common)

Cubic projection

Here is a link to a simple paper I wrote some years ago about the basics of different projections for environment maps.

The viewing

The trick is that we have now software that can interact with this projection, either on a 3D rendering software for a video or animation, or in real-time like a video game, a VR application, or a simple panorama on a website.

encryption – How are passwords stored in the database of a decentralized peer to peer system?

I want to use username and password instead of public key cryptography for a decentralized peer-to-peer application, but since the passwords are stored on users’ computers, there is a possibility of being stolen. How do I store the passwords securely?

And how do I give permission safely? How can I prevent someone else from gaining permission unfairly?

stored procedure – MYSQL – Convertir filas a columnas

Debo hacer una consulta de 2 tablas donde el contenido de una de ellas -sus filas- debe estar mostrada en columnas.

El resultado que se desea obtener es:
introducir la descripción de la imagen aquí

He intentado realizandolo con un contador pero no logro que el contador se reinicie cuando cambie el nro de pedido, tambien con una subconsulta y tampoco.

PD: Solo debe mostrar solo las primeras 5 incidencias, así sean 8 o 9 incidencias.

Aquí les puedo dar un Script con los datos necesarios para pruebas:

> CREATE TABLE `tmp_guia` (   `sguia_numero_pedido` varchar(15) NOT NULL
> DEFAULT '',   `sguia_hoja_ruta` varchar(10) NOT NULL DEFAULT '',  
> `satencion_persona` varchar(20) NOT NULL DEFAULT '1' ) ENGINE=InnoDB
> DEFAULT CHARSET=utf8;
> 
> insert tmp_guia values ("N001", "HB001", "JOSE PEREZ"); insert
> tmp_guia values ("N003", "HB003", "JAVIER SOLIS"); insert tmp_guia
> values ("N002", "HB002", "MARIA ROSARIO");
> 
> CREATE TABLE `tmp_guia_incidencia` (   `sguia_numero_pedido`
> varchar(15) NOT NULL DEFAULT '',   `sguia_item` char(02) NOT NULL
> DEFAULT ''   `sguia_incidencia` varchar(20) NOT NULL DEFAULT '' )
> ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> insert tmp_guia_documentos_cliente values ("N001", "01", "FUERA DE
> LUGAR"); insert tmp_guia_documentos_cliente values ("N003", "03",
> "CANCELADO"); insert tmp_guia_documentos_cliente values ("N003", "02",
> "NO HUBO COORDINACION"); insert tmp_guia_documentos_cliente values
> ("N002", "01", "INICIADO"); insert tmp_guia_documentos_cliente values
> ("N003", "01", "INICIADO"); insert tmp_guia_documentos_cliente values
> ("N002", "02", "FINALIZADO");