Can we declare a variable after creating table
I'm creating a new stored procedure that can auto generate table and it will check the other table and insert the data to the new table. The problem is I get an error .
I already try to add semicolon after creating table and try also remove it, it not working:
BEGIN CREATE TABLE TEST ( ID INT AUTO_INCREMENT NOT NULL, TEST NAME VARCHAR(255) DEFAULT NULL, UNIQUE(ID), PRIMARY KEY (ID) ); <I HAVE ERROR HERE> DECLARE vID,vNAME,vADD VARCHAR(255); DECLARE no_more_rows BOOLEAN DEFAULT FALSE; DECLARE cur1 CURSOR FOR SELECT NAME,ADD FROM OLD_TABLE ....... END$$
I expect that the run will compile successfully but it tell me have 1 error
See also questions close to this topic
How to connect mysql with Jenkins
I want to write the jenkins pipeline that should checkout the code and connect with mysql database and do some operations in the mysql database. I did not found any document for connecting mysql with jenkins. Instead I found connecting mysql with groovy and do some operations. If I use groovy then I have to download download the MySQL jdbc jar file and connect with that. Is there is any way to connect with mysql with jenkins.
indicates that either the object has no columns or the current user does not have permissions on that object
Every night I transfer the table from the MysQL server to to the MSSQL server . Sometimes, once a week or less, an error occurs. "The OLE DB provider "MSDASQL" for linked server "X" indicates that either the object has no columns or the current user does not have permissions on that object."
In the morning I run the same request, everything works. What could be the problem?
truncate table t
insert into t(id, contractId, accountId, productSpecId, productId, activationTime, timeFrom, timeTo, prolongationTime, flags, version)
select id, contractId, accountId, productSpecId, productId, activationTime, timeFrom, timeTo, prolongationTime, flags, version from openquery(X, 'select id, contractId, accountId, productSpecId, productId, activationTime, -- timeFrom, cast(if(timeFrom<="1900-01-01 00:00:00", "1900-01-01 00:00:00", timeFrom) as datetime) as timeFrom, timeTo, prolongationTime, flags, version from t')
Get Mysql query file execution result saved in a file using windows CMD
I need to get the result of mysql query execution results saved into a txt file. The file contains over 600000 lines of insert statements. The command that I used is:
mysql -h10.100.109.123 -f -uUserName -pPassword --database=databaseName < AFC.sql > abc.txt
Abc.txt gets created but there is no data in it. From console, I can see there are many errors. -f is used so that it wont stop on error.
Have gone through and tried out many suggestions of similar question. But none of them worked. MySql version 5.7
PL SQL Procedure from Trigger
I created procedure GETDEL that select name from students , but when i try to call this procedure in trigger it says i gave wrong types of argument to call
I have been trying to pass (records) or (records OUT SYS_REFCURSOR) as a argument but it dont works
CREATE OR REPLACE PROCEDURE GETDEL(records OUT SYS_REFCURSOR) AS BEGIN OPEN records FOR SELECT name FROM students; END GETDEL;
CREATE OR REPLACE TRIGGER After_delete_student AFTER DELETE ON TABLE2 FOR EACH ROW DECLARE rec sys_refcursor; BEGIN GETDELCZL(); END;
Stored procedure DateTime 'Parameter not found in the collection' error
I have a c# .Net program that utilizes the MySQL.Data library to connect to my MySQL database hosted in AWS. I have a stored procedure called "get_dates" in my database that is defined as follows:
CREATE DEFINER=`user_name`@`%` PROCEDURE `get_dates`(IN _identifier VARCHAR(20), OUT activation_date DATETIME, OUT deactivation_date DATETIME) BEGIN SELECT ifnull(ACTIVATION_DATE, "2000-01-01 12:00:00") INTO activation_date FROM table_name WHERE ID = _identifier; SELECT ifnull(DEACTIVATION_DATE, "2000-01-01 12:00:00") INTO deactivation_date FROM table_name WHERE ID = _identifier; END
From my program I call the stored procedure in this manner:
MySql.Data.MySqlClient.MySqlCommand getDatesCommand = new MySql.Data.MySqlClient.MySqlCommand(); getDatesCommand.Connection = connection.connection.Connection; getDatesCommand.CommandType = CommandType.StoredProcedure; getDatesCommand.CommandText = "get_dates"; getDatesCommand.Parameters.Add("@_identifier", MySql.Data.MySqlClient.MySqlDbType.VarChar); getDatesCommand.Parameters["@_identifier"].Value = identifierVar; getDatesCommand.Parameters.Add("@activation_date", MySql.Data.MySqlClient.MySqlDbType.DateTime); getDatesCommand.Parameters["@activation_date"].Direction = ParameterDirection.Output; getDatesCommand.Parameters.Add("@deactivation_date", MySql.Data.MySqlClient.MySqlDbType.DateTime); getDatesCommand.Parameters["@deactivation_date"].Direction = ParameterDirection.Output; getDatesCommand.ExecuteNonQuery(); DateTime activation_date = (DateTime)getDatesCommand.Parameters[@"activation_date"].Value; DateTime deactivation_date = (DateTime)getDatesCommand.Parameters[@"deactivation_date"].Value; getDatesCommand.Dispose();
I am receiving a System.ArgumentException: 'Parameter 'activation_date' not found in the collection.' exception at the line
DateTime activation_date = (DateTime)getDatesCommand.Parameters[@"activation_date"].Value;
I honestly have no idea. any help would be appreciated. I would also like to continue to use DateTime variables and not like to reformat my tables to use Varchars instead.
It is possible to generate SQL script to create only stored procedures for EF mapping?
I have a SQL Server database project with definition of tables, relations, keys, indexes, security, publish profiles, etc. and I also have an ASP.NET Core MVC project.
The issue is that I would like to use Entity Framework for DML operations but the catch is that I am allowed to use only stored procedures (probably from the security perspective, where application account will have only execution permission on those stored procedures and read only on specific tables).
I found that EF can map
.SaveChanges()method to stored procedures via model builder like this example
It can also generate them with
migrationcommand. I am not experienced with migrations, but I would like to only generate the SQL for the stored procedures creation and add this script (which was not run yet) into the SQL Server database project (the workaround is to run migration into separate database and generate scripts from the database into the database project, but it seems not nice).
Is it possible to do it somehow?