Tag Archives: sqlite

sqlite plus Error parsing time stamp [How to Solve]

Using datetime type will report error parsing time stamp

Caused by: java.sql.SQLException: Error parsing time stamp
Caused by: java.text.ParseException: Unparseable date: “2021-08-10 09:27:10” does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)

Preemptive solution
Modify the time format of the driver in the configuration file, URL passing reference (recommended)
jdbcUrl=jdbc:sqlite:test_name.db?date_string_format=yyyy-MM-dd HH:mm:ss

	 /**
	 * Sorting files by file date
	 * File[] dbFiles = SqlteUtils.orderByDate(dataFilePath);
	 * @param fliePath
	 * @return
	 */
	public static File[] orderByDate(String fliePath) {
		File file = new File(fliePath);
		if (!file.exists()) {
			file.mkdirs();
		}
		File[] fs = file.listFiles();
		Arrays.sort(fs, new Comparator<File>() {
			@Override
			public int compare(File f1, File f2) {
				long diff = f2.lastModified() - f1.lastModified();
				if (diff > 0) {
					return 1;
				} else if (diff == 0) {
					return 0;
				} else {
					return -1;
				}
			}
			@Override
			public boolean equals(Object obj) {
				return true;
			}

		});
		return fs;
	}

elicit questions

### The error occurred while handling results
### SQL: select * from TABLE_XXX where id = ?and is_deleted='0'
### Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'update_time' from result set.  Cause: java.sql.SQLException: Error parsing time stamp
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy300.getTableConfigById(Unknown Source)
	at com.istrong.form.main.helper.TableConfigHelper.getConfigDaoByTcId(TableConfigHelper.java:129)
	at com.istrong.form.main.helper.TableConfigHelper.getTableConfig(TableConfigHelper.java:91)
	at com.istrong.form.main.service.ConfigService.InitConfig(ConfigService.java:154)
	at com.istrong.form.main.controller.FormDataController.getModelData(FormDataController.java:124)
	at com.istrong.form.main.controller.FormDataController$$FastClassBySpringCGLIB$$65b7e256.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
	at com.istrong.form.main.controller.FormDataController$$EnhancerBySpringCGLIB$$dd272f57.getModelData(<generated>)
	at com.istrong.dc.controller.DcTableFieldController.getModelData(DcTableFieldController.java:298)
	at com.istrong.dc.controller.DcTableFieldController$$FastClassBySpringCGLIB$$923062e5.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
	at com.istrong.dc.controller.DcTableFieldController$$EnhancerBySpringCGLIB$$d591f0fe.getModelData(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:665)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
	at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
	at com.istrong.ec.web.filter.body.HttpServletRequestBodyFilter.doFilter(HttpServletRequestBodyFilter.java:53)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at com.istrong.ec.web.filter.file.MaliciousFileFilter.doFilter(MaliciousFileFilter.java:119)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at com.istrong.ec.web.filter.ip.IpFilter.doFilter(IpFilter.java:60)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at com.istrong.ec.web.filter.exception.ExceptionFilter.doFilter(ExceptionFilter.java:36)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at com.istrong.ec.web.filter.request.MethodOverrideFilter.doFilter(MethodOverrideFilter.java:31)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:118)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:158)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:92)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:92)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:77)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:109)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
	at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
	at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
	at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
	at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
	at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
	at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
	at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)
	at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
	at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
	at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
	at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
	at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
	at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)
	at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)
	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)
	at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)
	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
	at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)
	at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)
	at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)
	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:376)
	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'update_time' from result set.  Cause: java.sql.SQLException: Error parsing time stamp
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:87)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getPropertyMappingValue(DefaultResultSetHandler.java:511)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:480)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:404)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 144 common frames omitted
Caused by: java.sql.SQLException: Error parsing time stamp
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:543)
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:597)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1258)
	at com.alibaba.druid.filter.FilterAdapter.resultSet_getTimestamp(FilterAdapter.java:1829)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1254)
	at com.alibaba.druid.filter.FilterAdapter.resultSet_getTimestamp(FilterAdapter.java:1829)
	at com.alibaba.druid.filter.FilterChainImpl.resultSet_getTimestamp(FilterChainImpl.java:1254)
	at com.alibaba.druid.proxy.jdbc.ResultSetProxyImpl.getTimestamp(ResultSetProxyImpl.java:740)
	at com.alibaba.druid.pool.DruidPooledResultSet.getTimestamp(DruidPooledResultSet.java:365)
	at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69)
	at com.sun.proxy.$Proxy491.getTimestamp(Unknown Source)
	at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:39)
	at org.apache.ibatis.type.DateTypeHandler.getNullableResult(DateTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:85)
	... 159 common frames omitted
Caused by: java.text.ParseException: Unparseable date: "2021-08-10 09:27:10" does not match (\p{Nd}++)\Q-\E(\p{Nd}++)\Q-\E(\p{Nd}++)\Q \E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q:\E(\p{Nd}++)\Q.\E(\p{Nd}++)
	at org.sqlite.date.FastDateParser.parse(FastDateParser.java:299)
	at org.sqlite.date.FastDateFormat.parse(FastDateFormat.java:490)
	at org.sqlite.jdbc3.JDBC3ResultSet.getTimestamp(JDBC3ResultSet.java:540)
	... 175 common frames omitted

[Solved] Python sqlite3.OperationalError: near “?“: syntax error

code

con = sqlite3.connect('db.sqlite3')
cur = con.cursor()
sql = 'SELECT id FROM ?WHERE source_id = ?'
cur.execute(sql, ('article_table', 41945,))
one = cur.fetchone()
con.close()

report errors

sqlite3.OperationalError: near "?": syntax error

reason

sqlite3 placeholder (?) Cannot be used for column or table names.
Placeholders are used to insert or retrieve values of data from the database in order to prevent SQL injection.

Solution:

con = sqlite3.connect('db.sqlite3')
cur = con.cursor()
sql = 'SELECT id FROM article_table WHERE source_id = ?'
cur.execute(sql, (41945,))
one = cur.fetchone()
con.close()

[Solved] DLL load failed while importing _sqlite3: the specified module could not be found.

In the anaconda environment, pysqlite3 is installed:

pip install pysqlite3

But an error is reported:

DLL load failed while importing _sqlite3: the specified module could not be found.

The DLL file is missing. Go to the official website to download the DLL compressed package of the corresponding system version

Copy sqlite3.dll to the DLLs directory under Anaconda and it’s solved.

[Solved] SQLite Error: SQLite error near “@table“: syntax error

In my previous post on c# operation SQLite, I found that sometimes the following errors occur when running:

SQLite error near “@table”: syntax error

The error code is as follows:

public static Tuple<bool, DataSet, string> GetBarcode(string barcode)
{
    if (string.IsNullOrEmpty(barcode))
    {
        return new Tuple<bool, DataSet, string>(false, null, "The passed parameter cannot be empty");
    }

    string sql = "SELECT * FROM @table WHERE Barcode = @barcode";
    SQLiteParameter[] parameter = new SQLiteParameter[]
    {
        new SQLiteParameter("table", "Database Table Name"),
        new SQLiteParameter("barcode", barcode)
    };
    return SQLiteHelpers.ExecuteDataSet(sql, parameter);
}

 

Solution:

I found that the database table name cannot be added to the SQLiteParameter parameter list, the other column names are fine, and the SQL statement will not report an error if you change to the string connection method, as follows.

public static Tuple<bool, DataSet, string> GetBarcode(string barcode)
{
    if (string.IsNullOrEmpty(barcode))
    {
        return new Tuple<bool, DataSet, string>(false, null, "The passed parameter cannot be empty");
    }

    string sql = string.Format("SELECT * FROM {0} WHERE Barcode = @barcode", "Database Table Name");
    SQLiteParameter[] parameter = new SQLiteParameter[]
    {
        new SQLiteParameter("barcode", barcode)
    };
    return SQLiteHelpers.ExecuteDataSet(sql, parameter);
}

How to Solve svn error: WC DB

Go to sqlite official website (http://www.sqlite.org/download.html) to download sqlite3.exe

       Find Precompiled Binaries for Windows，Click sqlite-shell-win32-x86-3080500.zip to download，300KB

Show items hide folders Svn
Svn download sqlite.exe and place it in the .svn folder.
Configure the sqlite environment variable, copy the .svn root directory and add it to the global environment variable
execute sqlite3 .svn/wc.db “select * from work_queue” view the error message
delete the error file table sqlite3 .svn/wc.db “delete from work_queue”
If an error is reported that the database is locked – database is locked
execute sqlite3 .svn/wc.db “delete from wc_lock” delete locked information
return to the project file for clearup operation

SVN Update Error Please execute the ‘Cleanup‘ command [How to Solve]

Question

SVN Update Error: Please execute the ‘Cleanup’ command

Detailed description
when using SVN to update files, you will encounter failure (maybe because the last pull was forcibly interrupted). You will be prompted to need cleanup, but when you clean up, you will be prompted that the folder has been locked. At this time, you go to release lock, but still fail. You will be prompted to please execute the ‘cleanup’ command. (TMD is really difficult to use)

Solution:
obviously, this cannot be operated because the SVN file is locked and needs to be released, but manual release does not work, so you need to enter the SVN root directory where the locked folder is located, and then set the hidden files to be visible find the .SVN folder, and link wc.db with database management tools such as Navicat, DataGrid and other ides. This is a SQLite database. Find WC_Lock table

Delete locked records and submit updates to the database
it’s OK to update the code or file again

How to Solve Jetpack room use error

 

When building the room, there are several small problems. Record them. Basically, it is a configuration problem:

There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: simple_student)。
an error is reported during compilation and the table cannot be found
you need to set the corresponding indication in the declared entity.

//Wrong
@Entity
data class StudentEntity
//Right
@Entity(tableName = "student_table")
data class StudentEntity

Not sure how to convert a Cursor to this method’s return type (kotlinx.coroutines.flow.Flow<?extends java.util.List<com.zxf.jetpackrelated.room.liveDataOrFlow.StudentEntity>>).

When the Dao layer declares the return value as livedata, an error is reported. After a long investigation, it was found that the corresponding function was declared as a suspended function
correct use should remove the corresponding suspend modifier.

//Wrong
@Query("select * from $STUDENT_TABLE_NAME")
suspend fun obtainStudentAll(): LiveData<List<StudentEntity>>
//Right
@Query("select * from $STUDENT_TABLE_NAME")
fun obtainStudentAll(): LiveData<List<StudentEntity>>

Not sure how to convert a Cursor to this method’s return type (androidx.lifecycle.LiveData<java.util.List<com.zxf.jetpackrelated.room.liveDataOrFlow.StudentEntity>>)
Similarly, it cannot be converted to flow, and the corresponding suspend modifier is removed.

//Wrong
@Query("select * from $STUDENT_TABLE_NAME")
suspend fun obtainStudentAll(): Flow<List<StudentEntity>>
//Right
@Query("select * from $STUDENT_TABLE_NAME")
fun obtainStudentAll(): Flow<List<StudentEntity>>

If you do not need to declare the return type as livedata or flow, you can directly declare suspend.

Unity Android solves the problem of information flow advertisement closing and error reporting

This error often occurs when unity connects to some native andorid SDKs:

W/system. Err: Android. View. Viewrootimpl $calledfromwrongthreadexception: only the original thread that created a view hierarchy can touch its views.
the solution is to use runonuithread() in the java file written by the Android layer

The reason is that the UI update of Android needs to be in the main UI thread, and some callbacks of the advertising SDK are not in this thread

reference material:

https://www.jianshu.com/p/866743c0356b

https://blog.csdn.net/cf8833/article/details/90297775

””Device not found” error causes and Solutions

This is a common error you may encounter when using SQLite in Android development.
See this in conjunction with my other article, “ADB is not an internal or external command, nor is it an executable application.”
Executing ADB shell under CMD. It is likely that your ADB version is low. (You can view your version of ADB with the adb version command.)
Here’s how to solve this problem.
Open the Android SDK Manager in Eclipse, upgrade the SDK Platform, and download the Google USB Driver. As shown in the figure below:


At this point, the problem is solved by executing adb.exe again.

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