Tag Archives: The database

Solution to the problem that SQL database query result field contains new line character, which leads to copy to excel dislocation

Problem description:
in the process of work, sometimes encountered such a problem, write a SQL query to query data in the database, see the number of rows (such as 1000 rows), but make a copy of the query results to Excel, but happened mismatch, and lead to Excel in the number of lines is more than 1000 lines, caused the number of rows of data inconsistency.

field value contains char(10) newline character, copy the field value to Excel.
Source:
some corresponding cell contains a newline, lead to dislocation happened in copy to Excel.

Option 1 (Recommended) : Enclose the field values in question in double quotation marks so that the newline characters in the field values are restricted to the correct Excel cell.
scenario 2: use the script to remove the corresponding field value newline character.
create table #t
(
Name nvarchar(50),
Remark nvarchar(50)
)

insert into #t
lues (‘ A1 ‘+ char(10) +’ B1 ‘, ‘row 1’), (‘ A2B2 ‘, ‘row 2’)

select Name, Remark
om #t
— Solution 1: You can put double quotation marks directly in the field, and copy it to Excel without misplacing or showing redundant double quotation marks.
select ‘”‘ + Name + ‘”‘ as Name, Remark
om #t

select replace(replace(Name, char(13), ‘ ‘), char(10), ‘ ‘) as Name, Remark
om #t
drop table #t

Enum type and set type of MySQL

Enumeration type and collection type of MySQL
CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

create table consumer(
    id int,
    name char(16),
    sex enum('male','female','other'),
    level enum('vip1','vip2','vip3'),
    hobbies set('play','music','read','run')
)

Enumeration here means that you can select only one from here, and set means that you can select more than one from set.
Input insert statement

insert into consumer values(1,'egon','male','vip2','music,read')

And you might be wondering, what happens if I put multiple entries in the corresponding enum?

insert into consumer values(2,'eg','male','vip1,vip2','music,read')


ah

Insert into / delete / update / select of SQL Server database table

Score
Id = int, primary key, StuName Subject Result

2>r> 3
5
.> … … …
Add a score: Name StuName is Lei Wang, Subject is Java programming, Result is 92
Insert into score(StuName,Subject,Result) Values(‘ StuName ‘, ‘Java programming’,92);
2. Delete the score information of Chinese subject
Delete from score where subject = ‘Chinese’;
Change student’s StuName to 76
Update Score Set Result= 76 Where Stuname = ‘Stuname’
Select * from user where Score> 80
Select * From Score Where Result > = 80;

@Autowired injection * * required a single bean, but 2 were found**

why
The StringRedistemplate object is injected into the project as follows:

@Autowired
private StringRedisTemplate redisTemplate;

Then it says an error:

Field redisTemplate in com.xxx.api.controller.XxxController required a single bean, but 2 were found:
  - getRedisTemplate: defined by method 'getRedisTemplate' in class path resource [com/xxx/config/RedisConfig.class]
  - stringRedisTemplate: defined by method 'stringRedisTemplate' in class path resource [org/springframework/boot/autoconfigure/data/redis/RedisAutoConfiguration$RedisConfiguration.class]

@Autowired is either injected by type or declared @Qualifier(” beanID “), which has nothing to do with the field name I defined as redisTemplate. Other
but the project is so written, nor the @ the Qualifier is not an error (and I didn’t complain before use), the only difference is the field name is different, other places are stringRedisTemplate, I changed, really good. This is clearly a metaphysical operation, if you follow the previous interpretation of @Autowired.
why

@Autowired annotation injection rule:

after some code tests, the Autowired default by Type, if the same Type to find multiple beans, then, and according to the Name way comparison, if there are more than one, then an exception is reported.

According to the error prompt: StringRedisTemplate this type of bean in container pool has two redisTemplate, StringRedisTemplate (our own projects, configured with a spring boot automatically configure also give I added a)
I defined the field name is redisTemplate: no, just the StringRedisTemplate is some, so there is no problem!
To solve
There are many solutions:

    field is modified for StringRedistemPlate to specify the injected bean using @Qualifier. In some cases, add @primary [not recommended]
the conflicting bean
END
I had memorized the infusion process deeply until I came across it. Some items in the notes didn’t impress me as being important, and I kept a summary in mind, yet I couldn’t discover its significance until I encountered the problem.

DB2 table annotation pull

Application scenarios:
company Intranet data tables built after migrating to customers online, usually cannot be attached to export comments, need further pull
table field notes:

SELECT
	*
FROM
	(
		SELECT
			'comment on column ' || trim(t.TABSCHEMA) || '.' || t.TABNAME || '.' || t.COLNAME || ' is ' || '''' || t.REMARKS || '''' || ' GO' AS COMMENT
		FROM
			syscat. COLUMNS t
	)
WHERE
	COMMENT IS NOT NULL

Table annotation:

SELECT
	*
FROM
	(
		SELECT
			'comment on table ' || trim(t.tabschema) || '.' || t.tabname || ' is ' || '''' || t.REMARKS || '''' || ' GO' AS COMMENT
		FROM
			syscat. TABLES t
	)
WHERE
	COMMENT IS NOT NULL

Tar: due to the previous error, it will exit with the last error state

Official website to download the mysql connector – odbc – 8.0.21 – Linux – glibc2.12 – x86-64 – bit. Tar. Gz encountered error: tar ZXVF decompression –
Tar: Exits the previous error state because of the previous error
Solution: the source package placed in /home directory can be decompressed successfully
The reason has not been analyzed yet. If you want to see it, you can comment and reply to discuss and communicate with us

The solution to the error of [Oracle] ora-00054

Error message:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
Key words: ORA-00054 lock wait
 
The table cannot be truncated by another process if the table is not committed after the INSERT message is sent.
For example:
Process A creates A new table, inserts A row of data, but does not commit.

ORA-00054: ORA-00054 ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054: ORA-00054

The truncate of process B can only be executed after process A commits or after the resource is released.


Note that this situation is different from two processes operating on a resource at the same time (e.g., UPDATE on the same row). When two processes are operating on a resource at the same time, the second operation will get stuck (waiting).
You can find the process that holds the resource, commit the resource and release it. If you cannot commit, you can confirm and kill it.

SQL> select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;


Note that not all the information that appears after using the above statement is the problem process, because normal operation on the table as long as there is a hold resource in the query will be selected, so you can use this command several times to check whether the resource has been held, and you can look at the table name below to help confirm.
Query for sid and serial#.

SQL> select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

KILL the session process.

How to save big data in Oracle to CLOB

If the data is too large to be assigned directly to a CLOB variable, then we can read and write to the CLOB variable in stages using the DBMS_LOB.read and DBMS_LOB.write methods. But at this point we should cache CLOB variables, as shown in the first sentence below. Here is an example program that can not only write big data but also read it by switching the order DBMS_LOB.read and DBMS_LOB.write.

dbms_lob.createtemporary(lob_loc => x_clob,
                             cache   => TRUE);
PROCEDURE load_clob(p_clob_in IN CLOB,
                      x_clob    IN OUT NOCOPY CLOB) IS
    
    l_clob_len NUMBER := dbms_lob.getlength(p_clob_in);
    l_data VARCHAR2(32756);
    l_buf_len_std NUMBER := 4000;
    l_buf_len_cur NUMBER;
    l_seg_count   NUMBER;
    l_write_offset NUMBER;
  BEGIN
    IF p_clob_in IS NOT NULL THEN
      l_seg_count := floor(l_clob_len/l_buf_len_std);
      FOR i IN 0 .. l_seg_count
      LOOP
        
        IF i = l_seg_count THEN
          l_buf_len_cur := l_clob_len - i * l_buf_len_std;
        ELSE
          l_buf_len_cur := l_buf_len_std;
        END IF;
        
        IF l_buf_len_cur > 0 THEN
          dbms_lob.read(lob_loc => p_clob_in,
                        amount  => l_buf_len_cur,
                        offset  => i * l_buf_len_std + 1,
                        buffer  => l_data);
          l_write_offset := nvl(dbms_lob.getlength(lob_loc => x_clob),
                                0) + 1;
          dbms_lob.write(lob_loc => x_clob,
                         amount  => l_buf_len_cur,
                         offset  => l_write_offset,
                         buffer  => l_data);
        END IF;
      END LOOP;
    END IF;
  END load_clob;

Reproduced in: https://blog.51cto.com/snans/1353672

Import / export. SQL file / gzip file for MySQL under Linux

    ready. SQL file and upload to server login server MySQL import. SQL file
If you need to create the database for the entire database file you don’t need to create the database for the table file
Source *.sql //.sql file path
 
3. Export. SQL file
Export table structure
It’s going to be in the data directory
cd /mysql/data/
. /bin/mysqldump -uroot -p -d newdb > Newdb.sql // newdb database name
// Enter the password
 
Export data and table structures
It’s going to be in the data directory
cd /mysql/bin/
. /bin/mysqldump -uroot -p newdb > Newdb.sql // newdb database name
// Enter the password
 

    exports the gzip file
It’s going to be in the data directory
. /bin/mysqldump -uroot -p –default-character-set=utf8 xw_taes | gzip > /home/xw_taes_1.sql.gz
// Enter the password
 

    import gzip
In any directory
gzip -d < /home/xw_taes_1.sql.gz | mysql -uroot -p xw_taes_1
// Enter the password
Xw_taes_1 is the name of the database to import
There is no meeting

 
Note: Adding plaintext password directly will report an error

Oracle 12C installation process related errors and Solutions

For the latest version of Oracle 12c database, we will encounter many problems in the process of installing and building the database. Here I will summarize the problems I encountered, and give the corresponding solutions.

1.ORA-12500: TNS: Listener could not start the dedicated server process
As the name implies, this type of problem relates to the associated listening service

sqlplus/noolog
nn /as sysdba
s>up
conn /as sysdba
startup
conn /as sysdba
startup ORCL database associated with the service, if not opened in the CMD check can be through the task manager in the service column to find the relevant service to open the restart, ORACLESSWrite – ORACLESService – Listener – ORACLEScheduler -, generally is one of the service and Listener did not start, the service can be resolved after the restart. To reconfigure listeners, open the Net Configuration Assistant and reconfigure existing listeners. Open the netmanager and check whether the user’s localhost port 1521 is not abnormal.

ORA-12560:TNS: Protocol oracer error
Error problems can be caused by three factors:
The listening service is not up.
Windows platform a operation as follows: Start – Program – Management Tools – Services, open the Services Panel, start OracleHome92TNSListener service. The database instance is not up.
Windows platform: Start – Programs – Management Tools – Services, open the Services panel, launch Oracle Server XXXX,XXXX is your Database SID. Registry problems.
regedit, then go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 and set the environment variable ORACLE_SID to XXXX. XXXX is your database SID. Or just to the right of My Computer, Properties — Advanced — Environment Variable — System Variable — New, Variable Name = ORACLE_SID, Variable Value =XXXX,XXXX is your Database SID. ORACLE_SID =XXXX. ORACLE_SID =XXXX. ORACLE_SID =XXXX.

3. Port occupancy problem
CMD to perform netstat ano | findstr searches in 1521 CMD perform netstat – ano find localaddress port
Query to the PID of the executing process
Taskkill /pid ** /f Open the services panel, find the running pid process and terminate it
End the process, and the occupied port will be released

4.ORA-12505 Listener refused the connection

    sid, user name, etc. Make sure it matches the database you created — restart your computer, release the used port 1521 — restart Oracle Develpoer — then run CMD, open a command prompt, and type LSNRCTL to run stop first. Then start — Open Resource Manager
    and run C:\ Oraclexe \app\ Oracle \product\10.2.0\ Server \BIN\tnslsnr.exe
    ey_local_machine \ System \CurrentControlSet\Services\ OraclexetNSListener
    I>Path key value: C: \ oraclexe \ app \ oracle \ product \ 10.2.0 \ server \ BIN \ TNSLSNR exe

    Temarily sorted out the above four problems, in general, to ensure that the associated monitoring service is normally opened, the server SID and other configurations are correct, can test the successful connection.