Tag Archives: database

What is the schema in the database?

reference resources: http://database.guide/what-is-a-database-schema/

In database, schema (pronounced “skee MUH” or “skee MAH”, called schema in Chinese) is the organization and structure of database. Both schema and schemata can be used as plural forms. Schema objects include table, column, data type, view, stored procedures, relationships, primary key, foreign key, etc. Database schema can be represented by a visual graph, which shows database objects and their relationships

 

The above is a simple example of a schema diagram, showing three tables and their data types, the relationship between tables, and primary and foreign keys. The following is a more complex example of a database schema.

 

In this case, the pattern diagram is divided into four parts

(1) Customer data: data related to customers, such as name, address, etc

(2) Business: data needed by the business, such as employees, store location, payment details, etc

(3) Inventory: details of all products. Here, the product is a movie, so it contains movie title, category, actor and other data.

(4) Views: special views on the data used for evaluation, so we can further create a database through these schema diagrams. In fact, MySQL workbench allows us to generate a create database directly from the diagram Table script, and then we can directly use this script to create a database, and also directly convert a database into a relational chart.

Are schema and database the same?

When it comes to database schema, there are many doubts. The problem often arises whether there is a difference between schema and database, and if so, where is the difference.

It depends on the database vendor

Part of the confusion about schema is that database systems tend to handle schema in their own way

(1) MySQL documents point out that in physics, schema and database are synonymous, so schema and database are the same thing.

(2) However, Oracle documents point out that some objects can be stored in the database, but not in the schema. Therefore, schema and database are not the same thing.

(3) According to this SQL Server technical article, schema is an independent entity within SQL server. So, they are not the same thing.

Therefore, depending on the RDBMS you are using, the schema and database may not be the same.

How to define schema in SQL standard?

In ISO / IEC 9075-1 SQL standard, schema is defined as a persistent, named collection of descriptors. If you are confused about the definition of schema before, I hope it will be better, at least not worse after reading my article.

In a broad sense

Another reason for the confusion may be that the term schema has such a wide range of meanings, because it has different meanings in different environments. The word schema comes from the Greek SKH ē Ma, which means form, figure, shape or plan. In psychology, schema is used to describe the organized thinking or behavior patterns of organizational information categories and their relationships. Before we design a database, we also need to look at the types of information in the data and the relationship between them. Before we start to use the physical schema in DBMS, we need to create a conceptual schema. When discussing patterns in software development, we can discuss conceptual patterns, physical patterns, internal patterns, external patterns, logical patterns, etc., each of which has its own specific meaning.

Schema definition of DBMS

Here are the schema definitions of three leading relational database systems:

MySQL

Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on. These objects are connected through SQL syntax, because the columns make up the tables, the foreign keys refer to tables and columns, and so on. Ideally, they are also connected logically, working together as part of a unified application or flexible framework. For example, theINFORMATION_ SCHEMA and performance_ schema databases use “schema” in their names to emphasize the close relationships between the tables and columns they contain.

In MySQL, physically, aschema is synonymous with adatabase. You can substitute the keywordSCHEMA instead ofDATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

Some other database products draw a distinction. For example, in the Oracle Database product, aschema represents only a part of a database: the tables and other objects owned by a single user.

MySQL official documents point out that conceptually, a schema is a set of interrelated database objects, such as tables, table columns, column data types, indexes, foreign keys, and so on. But from the physical level, schema and database are synonymous. You can use the keyword schema instead of database in the SQL syntax of MySQL, for example, create schema instead of create database .

Reference: MySQL glossary, MySQL 5.7 reference manual. Mysql, retrieved 6 June 2016.

SQL Server

The names of tables, fields, data types, and primary and foreign keys of a database.

SQL Server official documents point out that the schema contains the database table, field, data type and the name of primary key and foreign key. Reference: SQL Server glossary. SQL Server 2016 technical documentation. Microsoft developer network. Retrieved 6 June 2016

Oracle Database

The schema system in Oracle is very different from other database systems. The schema of Oracle is closely related to database users.

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.

Oracle official documents point out that schema is a collection of logical structures of data or schema objects, which is owned by database users and has the same name as the user, that is to say, each user has an independent schema.

Reference: Oracle database objects. Oracle database online documentation 12C release 1 (12.1). Oracle help center. Retrieved 6 June 2016

If you want to know more about schema, you can refer to this article schema definitions by DBMS

Create schema

Although the above three DBMS are different in defining schema, they all support create schema statements.

MySQL

In mysql, create schema creates a database because create schema is synonymous with create database . In other words, you can use create schema or create database to create a database.

Oracle Database

In Oracle, create schema statement does not actually create a schema, because a schema has been created for database users when creating users, that is to say, create user creates a schema in Oracle, create user creates a schema Schema statements allow you to associate schema with tables and views, and authorize them, so that you don’t have to issue multiple SQL statements in multiple transactions.

SQL Server

In SQL server, create schema creates a schema by name. Unlike mysql, the create schema statement creates a schema that is defined separately to the database. Different from Oracle, the create schema statement actually creates a schema (as mentioned earlier, this statement does not create a schema in Oracle). In SQL server, once the schema is created, users and objects can be added to the schema.

summary

The word schema can be used in many different environments. When creating a schema in a specific DBMS, you need to use the DBMS specific definition mode. When you switch to a new DBMS, you must check how the system defines the schema.

MySQL error code ‘1064’ troubleshooting process

Afternoon self-test code, stuck on this update for an hour and a half, greatly reduced the development productivity, send out the screening process, good soldiers will not fall into the same trap. First, type out the exception stack.

2016-03-28 17:23:38.420 main DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory:463] - Finished creating instance of bean 'Sybase'
2016-03-28 17:23:38.420 main  INFO [org.springframework.jdbc.support.SQLErrorCodesFactory:126] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2016-03-28 17:23:38.420 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:199] - Looking up default SQLErrorCodes for DataSource [[email protected]]
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:217] - Database product name cached for DataSource [[email protected]]: name is 'MySQL'
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:175] - SQL error codes for 'MySQL' found
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator:399] - Translating SQLException with SQL state '42000', error code '1064', message [  
--- The error occurred while applying a parameter map.  
--- Check the mall.org.updateById-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 12' at line 1]; SQL was [] for task [SqlMapClient operation]
2016-03-28 17:23:38.424 main ERROR [com.jd.dd.mall.service.waiter.impl.OrgService‫Impl:97] - IM data access exception
com.jd.im.data.exception.IMDataAccessException: IM data access exception
	at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.handleUserException(IMSqlMapClientTemplateForMultDs.java:280)
	at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update(IMSqlMapClientTemplateForMultDs.java:160)
	at com.jd.dd.mall.service.waiter.impl.OrgService‫Impl.updateById(OrgService‫Impl.java:106)
	at com.jd.dd.mall.service.waiter.impl.OrgService‫Impl.add(OrgService‫Impl.java:92)
	at com.jd.dd.mall.web.controller.waiter.OrgManageController.addOrg(OrgManageController.java:85)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:746)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:687)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:822)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:796)
	at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:66)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:168)
	at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:136)
	at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:134)
	at com.jd.service.waiter.OrgControllerTest.add(OrgControllerTest.java:53)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the mall.org.updateById-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 12' at line 1
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:206)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:381)
	at com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update(IMSqlMapClientTemplateForMultDs.java:134)
	... 54 more
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred while applying a parameter map.  
--- Check the mall.org.updateById-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 12' at line 1
	at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
	at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:383)
	at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:381)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
	... 56 more

and my Ibatis SQL

<update id="updateById"  parameterClass="Org">
		UPDATE org
		SET	name = #name:VARCHAR#,
			<isNotNull property="tree_level">tree_level = #tree_level#, </isNotNull>
			<isNotNull property="yn">yn = #yn#, </isNotNull>
			<isNotNull property="route">route = #route:VARCHAR#, </isNotNull>
			`version` = `version` + 1,
			<isNotNull property="editor">editor = #editor:VARCHAR#  </isNotNull>
			
		WHERE id = #id#
	</update>

like this stack does not clearly indicate the cause of SQL problems, troubleshooting problems must be according to the error code to check. Baidu checked the 1064 error code. The column name of the table overlaps with the Mysql keyword. OK, I took this SQL close to the mysql client query interface and found that the name field changed to the color of the keyword. I thought I found the problem, so I put back quotes around the name. Run the unit test again and report an error of 1064.

There is no way out, only ibATIS executed SQL typing out, put into mysql client to see. OK, log4J plus log, the execution of IBATIS SQL typing out. Run the unit test again and the SQL doesn’t come out.
There is a feeling of frustration that should not be called indolent every day!
You have no choice but to look at SQL with your eyes. If the Editor field is null, then there are extra commas before the WHERE. Adjust the SQL.

	<update id="updateById"  parameterClass="Org">
		UPDATE org
		SET id = #id# ,
			<isNotNull property="name">`name` = #name:VARCHAR#, </isNotNull>
			<isNotNull property="tree_level">tree_level = #tree_level#, </isNotNull>
			<isNotNull property="yn">yn = #yn#, </isNotNull>
			<isNotNull property="route">route = #route:VARCHAR#, </isNotNull>
			<isNotNull property="editor">editor = #editor:VARCHAR# , </isNotNull>
			`version` = `version` + 1
		WHERE id = #id#
	</update>

Then run the unit test and the SQL executes successfully. Just log can not type out SQL, this time to type out. Where have you been when I need you?Mysql’s error code is not reliable at all! The SQL syntax is obviously wrong, but the code of 1064 is quoted.

Remove the back quotes from the Name and Version fields and the SQL will still execute normally. Fun!

How to make the import complete smoothly!

When you import an EXP, you may encounter constraints that prevent you from importing.
You can disable constraints to make the import complete!
When importing, I encountered the following problems:
Column 30 MOS
IMP-00019: row rejected due to ORACLE error 2291
IMP-00003: ORACLE error 2291 encountered
ORA-02291: integrity constraint (CMDB.CI_ELEMENT_LOCATION) violated – parent key not found
The processing steps are as follows:
SQL> create table cmdb.configuration_item_bak as select * from cmdb.configuration_item;
Table created.
SQL> alter table cmdb.configuration_item disable primary key;
alter table cmdb.configuration_item disable primary key
*
ERROR at line 1:
ORA-02297: cannot disable constraint (CMDB.PK_CONFIGURATION_ITEM) –
dependencies exist

SQL> Alter TABLE cmDB. configuration_item disable constraint PK_CONFIGURATION_ITEM;
alter table cmdb.configuration_item disable PK_CONFIGURATION_ITEM
*
ERROR at line 1:
ora-02297: cannot disable constraint (CMDB.PK_CONFIGURATION_ITEM) –
dependencies exist

SQL> Alter TABLE CMDB. Configuration_item disable constraint PK_CONFIGURATION_ITEM cascade;
Table altered.
SQL> alter table cmdb.configuration_item disable primary key;
Table altered.
SQL> Alter TABLE cmDB. configuration_item disable constraint PK_CONFIGURATION_ITEM;
Table altered.
 
I thought the import should be ok 🙂
imp/file=cmdb.dmp fromuser=cmdb touser=cmdb ignore=y
But the question remains?
Why is that?The original constraint error here was a violation of the constraint to import data in the file, regardless of whether the constraint on the table was disabled or not.
Just because the table already has the same data as the file, a constraint in the file was violated when importing it.
So you can complete the import by clearing the table data.
truncate table cmdb.configuration_item ;

then import:
-bask-3.00 $imp/file= CMdb.dmp Fromuser = CMDB Touser = CMDB ignore=y
Import: Release 11.2.0.2.0 – Production on Wed Aug 17 03:27:17 2011
Copyright (C) 1982, 2009, Oracle and/or its Affiliates. All rights Reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production
With the Partitioning option
Export file created by Export :V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server USES AL32UTF8 character set (possible charset)
export client USES US7ASCII character set (possible charset conversion)
. It is important to import CMDB’s objects into CMDB
imported
 

A series of errors encountered in connecting to the database using Oracle SQL developer

Today, I encountered a series of errors in connecting Oracle database with SQL Developer, which took a lot of time and was finally solved. Now I am writing it down, hoping to give some help to those students who encountered the same mistakes
Make sure that both services are started, or if not, start both services first.

The first is this exception Io exception: The Network Adapter could not establish The Connection, this exception is probably The IP error of The host name of The connection, The host name does not have to be localhost or 127.0.0.1, but should be consistent with The IP name in The Oracle installation directory (my name is The directory H:\app\LBJ\ Product \11.2.0\dbhome_1\ Network \ADMIN) listens. ora file. That is, the IP after HOST in the figure below is consistent

After the modification is completed, restart the Listener service (namely the two services in the figure above), and encounter the Listener union connection with the following error: Ora-12505, TNS: Listener does not currently know of SID Given in Connect Descriptor; after searching the data, it is found that there is a problem with SID. The name in the SID is also not optional. It should be the same as the SID_NAME in the file (listlisteners. Ora) (orcl in this case)

      



Restart the listening service after the modification to see if the problem has been resolved. Again, I encountered an error where I was unable to get more information from the socket. Ora file, as shown in the figure above. Simply comment out the #(PROGRAM = extproc) line and the problem is solved. Now restart the service again and find that you can finally connect, and you’re done.

About the cause of the long-standing ADO error: unspecified error “(error code = 0x80004005) (excerpt from the Internet)

ms-help://MS.MSDNQTR.2005APR.1033/enu_kboledb/oledb/251254.htm
SYMPTOMS
When trying to connect to an Access .mdb file, an ODBC error is returned stating “Disk or Network Error”. The Microsoft OLE DB Provider for the Microsoft Jet database engine may also return an “Unspecified error” (error code = 0x80004005) message.

The problem can occur only when the data source is opened under the Microsoft Internet Information Server (IIS) or a Microsoft Windows NT service but not under a logged-on user account.
CAUSE
Jet creates a temporary file when the engine is started. In doing so, it first checks the TMP environment variable and uses that path to define where the temporary file is created. If it doesn’t see a TMP environment variable, it looks for the TEMP environment variable. If TEMP is not defined, it then uses the Windows folder (/WINDOWS or /WINNT).

If TMP/TEMP is defined but points to a nonexistent folder, the error occurs.

Moreover, when opening the Access database through IIS or a Windows NT service, and when IIS or the service is running under the local system account, the TMP or TEMP system environment variable is used. When the Web services or Windows NT service are started using a user account, the TMP/TEMP user profile environment variables is used.
 
RESOLUTION
Define a TMP or TEMP environment variable and assign the variable to an existing folder. If you are accessing the Access database from IIS, be certain to check the system environment settings rather than environment variables of the logged-on user. Also ensure that the IUSER anonymous IIS account has access to the temporary folder.

You must restart the computer after changing any environment variables.
Environment variables are set under Windows NT 4.0 by going to the Control Panel and clicking System and then selecting the Environment tab.

C programming interface of SQLite database (6) result codes and error codes

SQlite database C programming interface (VI) Result Codes and Error Codes by QQ: 253786989 2012-02-07

Standard Codes
Here are the standard return values and error code definitions:

#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */
/* end-of-error-codes */

Some of these constants are returned only by a specific function, such as the SQLITE_RANGE, which is returned only by the SQlite3_bind_xxx function. Some constants, such as SQLITE_ERROR, only indicate that an error occurred during the execution of the function, but there is no way to know why the error occurred.
Sqlite_false stands for MISUSE of apis. For example, a bind function returns SQlite_ern when a statement is given another parameter after the sqlite3_step function has executed without being reset.
Extended Codes
Standard error codes provide less information about the cause of the error. So sometimes we use extended error codes. The extended error code is based on the standard error code, whose lower order byte is the original standard error code, and then attaches information to its higher order byte “or” to describe the details of the error.

int sqlite3_extended_result_codes(sqlite3*, int onoff);

The error codes for these extensions are not enabled by default due to compatibility issues with the client’s legacy programs. Programmers can enable or disable extended error codes by using the SQlite3_extended_result_CODES function.
Here are all the extended error codes (most of which describe SQLITE_IOERR) :

#define SQLITE_IOERR_READ              (SQLITE_IOERR | (1<<8))
#define SQLITE_IOERR_SHORT_READ        (SQLITE_IOERR | (2<<8))
#define SQLITE_IOERR_WRITE             (SQLITE_IOERR | (3<<8))
#define SQLITE_IOERR_FSYNC             (SQLITE_IOERR | (4<<8))
#define SQLITE_IOERR_DIR_FSYNC         (SQLITE_IOERR | (5<<8))
#define SQLITE_IOERR_TRUNCATE          (SQLITE_IOERR | (6<<8))
#define SQLITE_IOERR_FSTAT             (SQLITE_IOERR | (7<<8))
#define SQLITE_IOERR_UNLOCK            (SQLITE_IOERR | (8<<8))
#define SQLITE_IOERR_RDLOCK            (SQLITE_IOERR | (9<<8))
#define SQLITE_IOERR_DELETE            (SQLITE_IOERR | (10<<8))
#define SQLITE_IOERR_BLOCKED           (SQLITE_IOERR | (11<<8))
#define SQLITE_IOERR_NOMEM             (SQLITE_IOERR | (12<<8))
#define SQLITE_IOERR_ACCESS            (SQLITE_IOERR | (13<<8))
#define SQLITE_IOERR_CHECKRESERVEDLOCK (SQLITE_IOERR | (14<<8))
#define SQLITE_IOERR_LOCK              (SQLITE_IOERR | (15<<8))
#define SQLITE_IOERR_CLOSE             (SQLITE_IOERR | (16<<8))
#define SQLITE_IOERR_DIR_CLOSE         (SQLITE_IOERR | (17<<8))
#define SQLITE_IOERR_SHMOPEN           (SQLITE_IOERR | (18<<8))
#define SQLITE_IOERR_SHMSIZE           (SQLITE_IOERR | (19<<8))
#define SQLITE_IOERR_SHMLOCK           (SQLITE_IOERR | (20<<8))
#define SQLITE_IOERR_SHMMAP            (SQLITE_IOERR | (21<<8))
#define SQLITE_IOERR_SEEK              (SQLITE_IOERR | (22<<8))
#define SQLITE_LOCKED_SHAREDCACHE      (SQLITE_LOCKED |  (1<<8))
#define SQLITE_BUSY_RECOVERY           (SQLITE_BUSY   |  (1<<8))
#define SQLITE_CANTOPEN_NOTEMPDIR      (SQLITE_CANTOPEN | (1<<8))
#define SQLITE_CORRUPT_VTAB            (SQLITE_CORRUPT | (1<<8))
#define SQLITE_READONLY_RECOVERY       (SQLITE_READONLY | (1<<8))
#define SQLITE_READONLY_CANTLOCK       (SQLITE_READONLY | (2<<8))

Error Functions

int sqlite3_extended_result_codes(sqlite3*, int onoff);

Enable or disable the use of extended error codes for a database connection. Enable extended error codes by passing a non-zero value to the second parameter of the SQlite3_extended_result_CODES function. This function always returns SQLITE_OK, and there is no way to get an extension error code that is currently enabled or off.

int sqlite3_errcode(sqlite3 *db);

If a database function operation does not return SQLITE_OK, you can then call the function to get the error code. By default it returns the standard error code, and it may also return an extended error code if the current database connection is enabled.

int sqlite3_extended_errcode(sqlite3 *db);

Similar to the SQlite3_errcode function, except that it only returns the extended error code.

const char *sqlite3_errmsg(sqlite3*);
const void *sqlite3_errmsg16(sqlite3*);

Returns an error code string, encoded in UTF-8 or UTF-16. Programmers should either call these functions, get the error code information, or make a copy. The next database operation may invalidate the returned string pointer.
SQlite error handling cannot handle multiple errors simultaneously. For example, if an API function call goes awrong and the programmer fails to check for that error, the next API function call may well return SQlite_ern, indicating that the program is trying to use an invalid data structure. So programmers should check and handle any errors that might occur after each API function call.
In addition, if multiple threads share the same database connection, it is best to encapsulate the core API calls and error-handling code in the Critical section. Programmers can use the SQlite3_db_mutex function to get the mutex pointer to the database connection (a pointer to the SQlite3_mutex object).
Prepare for V2 Version
The following table compares the prepare function of the original version with that of v2 version:

The V2 version of prepare is more concise for error handling and has the schema advantages listed in the table above, so it is recommended to use the V2 version of prepare.
Transactions and Errors
Typically, SQlite operations are in auto-commit mode. SQlite automatically encapsulates each SQL command into a transaction. Error recovery is easy if each statement is encapsulated in its own transaction. Any time SQLite finds itself in the wrong state, it simply rolls back the current transaction. This effectively cancels the current SQL command and returns the database to the state it was in before the error occurred.
However, once the BEGIN TRANSACTION command is executed, SQlite is no longer in autocommit mode. A TRANSACTION is opened and will remain open until either the END TRANSACTION or the COMMIT TRANSACTION command is executed. This allows multiple SQL commands to be encapsulated into a single transaction, allowing a discrete set of commands to execute either all or none (atomic operations), but it also limits SQLite’s error recovery.
When a displayed (explicit) transaction encounters an error during execution, SQLite attempts to cancel the statement just executed. Unfortunately, this is not always possible. If things go badly, SOMETIMES SQlite can just roll back the entire current transaction, with no other option.
The most likely errors to cause a rollback are SQLITE_FULL (database or disk space is full), SQLITE_IOERR (disk IO error or file is locked), SQLITE_BUSY (database lock), SQLITE_NOMEM (out of memory), SQLITE_INTERRUPT (interrupt). If the program is executing a display transaction and receives one of these errors, be prepared to handle the possibility of a transaction being rolled back.

int sqlite3_get_autocommit(sqlite3*);

With this function, you can get the current commit status. If a non-zero value is returned, the database is in auto-commit (atutoconmit) mode. If 0 is returned, the database is currently inside an explicit transaction.
If the SQlite database is forced to do a full transaction rollback, the database will once again go into transaction autocommit mode. If the database is not in auto-commit mode, it must be in a transaction, indicating that no rollback is required.

SQlite database C programming interface (VI) Result Codes and Error Codes by QQ: 253786989 2012-02-07

Txt import MySQL: error 1062 (23000): duplicate entry ‘0’ for key ‘primary’

Existing problems:
When importing TXT file into Mysql database, ERROR 1062(23000) always appears: Duplicate entry ‘0’ for key ‘PRIMARY’ ERROR, but TXT file does not have a data with ID (PRIMARY key) of 0.


Reason for error:
This is still not clear, because I have no record of ID=0, so I don’t know why the duplicate ID is 0. At first, I thought it was the data encoding that caused the data chaos, but later I found that even the same encoding could not solve the problem.

Solutions:
Setting the primary key (ID) to AI (Auto Incremental) is sufficient. Setup has been completed and import has been successful

Note:
I thought that if I set the primary key to AI, the primary key column would be 1, 2, 3… , because the primary key in TXT file is discontinuous, but I found that this understanding is wrong, after insertion, the primary key still maintains its discontinuity.

Version problem of SQL Server


Recently, SQL Server 2005 has been installed on the computer. It works normally on this computer. Everything is OK.

When connected to the server operation Database, however, you Unspecified error when creating the table:

The details of the error are as follows:

===================================


Unspecified error
 (MS Visual Database Tools)


------------------------------
Program Location:


   at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.NewTable(Object dsRef, Object pServiceProvider)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.TableDesignerNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.CreateDesigner(DocumentType editorType, DocumentOptions aeOptions, Urn parentUrn, IManagedConnection mc)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

=========================================

checked on the Internet, the original problem was between SQL Server versions: SQL Server 2005 Management Studio could not operate SQL Server 2008, so this error occurred.

The solution is to replace 2005 with 2008.

Solution of Oracle error 6550

When exporting data using the EXP command, the following appears:
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully
Cause
Use of Higher Version Export utility (10.2.0.1) on Lower Version database(9.2.0.6).
The main reason is that the server and client versions are not correct
You can modify oracle’s in the environment variable (Path)
D:/oracle/product/10.2.0/db_1/bin; D:/oracle/product/10.2.0/client_1/bin; The location of the