Tag Archives: SQL

Cause: java.sql.SQLException: invalid column type: 1111 Error [How to Fix]

Warming: Servlet.service() for servlet [spring] in context with path [/*****] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='ENTERPRISE_NAME', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #13 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column type: 1111] with root cause
java.sql.SQLException: Invalid column type: 1111
	at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3978)

Screen:

When inserting the name,ENTERPRISE_NAME passes null and mybatis returns an error.
The solution is:

A convenient method to count the total number of query results in MySQL

MYSQL keywords:
SQL_CALC_FOUND_ROWS
to check the manual and found this keyword is used in the query result of statistical filtering conditions when the total number of (not restricted by Limit)
For example:

SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid=14 LIMIT 1,10;

Let’s say there are 1,000 that satisfy our condition, and we return 10.

for immediate use

SELECT found_rows() AS rowcount;

Return rowcount = 1000;
this saved a SELECT count (*) AS the rowcount repeat query, can save considerable time.

From: http://ginew.blog.163.com/

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;

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

about rs.last () error reporting

When writing to the statements rs.last() and rs.beforeFirst() that are used when querying data from a database, these methods return an error because Microsoft SQL does not have this method and requires additional statements to be added.

String SQL =”select * from stu_info”;// query key code
PreparedStatement PSTMT = dbConn. PrepareStatement (SQL,
the ResultSet. TYPE_SCROLL_INSENSITIVE, ResultSet. CONCUR_READ_ONLY); .
ResultSet rs = PSTMT executeQuery ();
 
Add the key code for the scarlet letter, and rs.last() will work.
However, the ResultSet.CONCUR_UPDATETABLE in the reference article will return an error. I don’t know why…
Refer to the article links: https://blog.csdn.net/pzasdq/article/details/52602730
 

SQL Union, union all usage and common errors and Solutions

The SQL UNION operator
The UNION operator is used to combine the result sets of two or more SELECT statements.
Note that the SELECT statement within the UNION must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.
The UNION SQL grammar

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: By default, the UNION operator picks a different value. If duplicate values are allowed, use UNION ALL.
SQL UNION ALL syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Also, the column name in the UNION result set is always equal to the column name in the first SELECT statement in the UNION.
Error reporting and resolution
When using the UNION operator, the following error can easily be reported:
Parse error: org. Apache. Hadoop. Hive. Ql. Parse. ParseException: line 5-0 always recognize input near “and” (” and “the UNION, ‘ ‘SELECT’ set in the operator
Simplify the code used as follows:

SELECT
    device_id
FROM
    tableA
UNION
(SELECT
    device_id
FROM
    tableB as a1
INNER JOIN tableC as a2
on a1.device_id = a2.device_id
)

This is mainly because the UNION operator can only join fields to fields, but not fields to tables or tables, even if the number of fields and their names and formats are the same.
Therefore, you can’t have parentheses around the UNION operator, because it’s easy for SQL to determine that this is a subquery/table and therefore to report an error when joining
The solution

    > SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D
    Matters needing attention

      > Multiple UNION operators can be used in SQL statements. SELECT statements that use the UNION operator cannot contain their own ORDER BY or COMPUTE clauses. You can only use an ORDER BY or COMPUTE clause on the final combined result set (that is, after the last SELECT statement). The GROUP BY and HAVING clauses can be used in SELECT statements that use the UNION operator. By default, SQL Server 2005 evaluates statements containing the UNION operator from left to right. You can specify the order of evaluation using parentheses (I haven’t tested this thoroughly on any other platform, so avoid parentheses altogether, so if you need to specify the order of evaluation, you can simply write that part of the evaluation at the beginning of your SQL statement).

How to solve MySQL error 1049 (42000): unknown database ‘database’

In fact, I made a very serious mistake, and this mistake can only be reported for two reasons.
Reason 1: There is an extra space after your account password, which means you have the wrong database.
Fix: If it is a login password, report this error, as shown in the figure below:

It would be possible to have an extra space between p and 123, and then you would remove the space in between. You put p and 123 together and you type in p123
Reason 2: Your syntax is wrong, because use can only be followed by the database name, never the table name. An incorrect or incorrect table name will report the following error.

Solution: Double check your database to see if it has TB_EMP6.

Check your table again to see if it is your table name.

The name of the table is confused with the database name.
Summary is to confirm the database name is not exist, there are no spelling mistakes, if not again to see your grammar is not wrong.
This is better to locate your problem, in fact, to put it bluntly this is I made a stupid mistake, but also eat a catty gain a wisdom.
Hope to help you, welcome to leave a message to exchange ~

Usage of NVL in SQL

, for example, in the figure above: NVL (p.mark, 0)
p.mark is a value of type number. If the mark score is null, the value is 0; if not null, the value of p.mark is **
An NVL function is a null value conversion function
NVL (expression 1, expression 2)
If expression 1 is null, NVL returns the value of expression 2; otherwise, it returns the value of expression 1. The purpose of this function is to convert a null value (NULL) into an actual value. The value of its expression can be numeric, character, or date. But expressions 1 and 2 must have the same data type.
Logarithmic font: NVL (COMM,0);
To character NVL(TO_CHAR(COMM), ‘No Commission’)
NVL for date type (HireDate,’ 31-DEC-99′)

Solution to MySQL Error 1130 problem

Local Error 1130 problem solution
When connecting to mysql database, ERROR 1130 (HY000) occurs: the Host ‘XXXX’ is not allowed to connect to this mysql Server. Local localhost will not work either. If you reinstall mysql database, you will be afraid that the original database cannot be restored.
Appear this kind of problem may be caused by illegal power off and remove the root user, mysql database user without root, in the table if localhost is also unable to log in, you need to. In my ini finally add skip – grant – tables, so you don’t need a password login, after landing in the following SQL statement to create the root user (I am using version 5.1, different versions can be different SQL statements)

INSERT INTO `user` VALUES ('localhost', 'root', 'your password', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');

After success, delete ski-grant-tables in my.ini, and you can now log in using the root password locally.
Remember in the database installed mysql, do not illegally shut down, forced power off, otherwise this problem will occur.
If Error 1130 appears locally, then the remote connection must also appear. If the local connection problem is solved, the remote connection problem may not be solved.
Error 1130 appears in the remote connection
There are two ways to solve this problem
1. Table modification
Maybe your account doesn’t allow you to log in remotely, only at localhost. At this time, as long as the computer at localhost, after logging into mysql, change the “host” item in the “User” table in the “mysql” database, and change the name from “localhost” to “%”.

mysql -u root -pvmware
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>flush privileges;
mysql>select host, user from user

2. Authorization law
For example, if you want myUser to use MyPassword to connect to a mysql server from any host.

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

If you want to allow user MyUser to connect to the mysql server from a host with IP of 192.168.1.3, use myPassword as the password

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

Supplement: Reinstalling mysql database is not a successful solution
Sometimes you have to reinstall the mysql database, but the installation is always unsuccessful due to the impact of the previous installation of mysql files. You can completely remove the mysql legacy files in the following two steps
1. Full text search MySQL and delete by looking up registry Regedit.
2. Delete MySQL data, generally in the MySQL directory under C: \ProgramData (it is recommended to backup this folder before deleting, so as to restore the data in the future).

Oracle database file is damaged, Error:ORA-01033 :ORACLE initialization or shutdown in progress

Due to create your own table space, but want to rename, will directly to smash this table space, after the myeclipse deployment project under the service is just an error, pl/SQL or navicat is to open other existing connection, is all the database file is unusable, comprehensive look at a few other people’s blog last problem solved:
Attached to the blog: http://blog.csdn.net/liverliu/article/details/6410287
I’ll just stick to his summary:
First of all, the cause of the problem is that I created a bookspace table space in f:/ LLH/directory before, but I didn’t want the table space later, so I just deleted it. This error is caused by deleting several files such as F :/ LLH /bookspace.dbf by mistake.
Ora-01033: ora-01033: Oracle initialization or shutdown in progress
1. First, run CMD under Windows and enter the DOS environment. 2. Login as DBA user
(1) sqlplus /NOLOG
 
(2) Connect sys/change_on_install as sysdba
Tip: Successful
 
(3) Shutdown Normal
Tip: the database has been closed
has been uninstalled the database
the ORACLE routine has been closed
(4) Startup Mount
Tip: ORACLE routine has been started
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 82886080 bytes
Database Buffers 33554432 Bytes
Redo Buffers 532480 bytes
database finished loading
(5) the alter database open;
 

error on line 1 :
ora-01157: unable to identify/lock data file 19 – refer to DBWR trace file
ora-01110: data file 19: “” F:/LLH/ bookspace.dbf “; The
tip file section is slightly different for each individual.
Continue to input
(6) Alter Database Datafile 19 offline Drop;

hint: the database has changed.
 
Use the last two steps in the “database has changed” loop until alter Database Open. No more error, “Database has changed”.
 
Up to this point, I will follow the original blogger’s operation. Now I need to close the command line and click computer — > Management – & gt; After the service, find the Oracle-related service and stop it, and then restart it. Oracle services are closed and started in a certain order, specific query can be online.
You can also refer to the following website content for setting, I did not try, roughly the same.
http://www.linuxidc.com/Linux/2016-04/130111.htm