Questions tagged [sqlite]

23177 questions
0

votes
0

answer
20

Views

SQLite3 - Select returns bad data

I have a problem without my simply database. I have defined (fields - text, decimal, data) the sales table in the database. I load data into it (code). The problem is that: Select sum(salesvalue) from sales it shows bad data. The sum is too small in relation to reality. 'Salesvalue' -> DECIMAL Inter...
Qlikeers
1

votes
3

answer
52

Views

Can't insert into a database using sqlite3_bind_text

Hello I have an issue with this function recup(). The purpose of recup() is to insert all of the words found in the string chaine, word by word, into a database. The words come from chaine which is set by the user using a fgets. The problem I am facing is that it is only inserting words that have th...
Quentin Lam
1

votes
2

answer
60

Views

AVG function with multiple conditions and columns in SQL Lite

I figured out how to do avg function with condition/s but I couldn't make it work for my specific need. Let's say I'm working with this table called sales_performance product_ID sales_period sales_qty sales_index product_sub goal_met C12 0001 15 20 D71...
memokerobi
1

votes
4

answer
49

Views

SQL: conditional count statement

I am new to SQL and I have a SQL database with two columns, which in combination are unique (but their individual values are not): songID and artistID. Each song is recorded by different artists and each artist recorded different songs (see code below). I now want to extract information on who coope...
1

votes
2

answer
77

Views

Entity Framework Core SQLite Connection String Keyword not supported: version

I created a ASP.NET MVC website using .NET Core 2.2 using a SQLite database. So far it's working well. Trouble begins when I want to add SQLite-specific keywords to the connection string, such as Data Source=~\\App_Data\\MyDb.db; Version=3; DateTimeFormat=UnixEpoch; DateTimeKind=Utc Now I get Keywor...
Etienne Charland
1

votes
2

answer
70

Views

Perl - Get the structure of a sqlite database using DBI

I need to test the structure of my SQLite database which is composed by a unique table with let's say 2 columns (id, name). I can't figure out the SQL query to get the table schema of my database. I am able to get all the content of the database using the DBI method selectall_arrayref(). However it...
AntMau
1

votes
1

answer
31

Views

Difference between two dates in Sqlite3

I work in Sqlite3 and tried DATEDIFF(days, listing_date, sale_date) But it didn't work. What is the correct way to take a difference between two columns which are timestamps in Sqlite3? id listing_date sale_date 1 2012/02/27 2020/02/27 2 2011/06/27 2020/02/17 3 2008/08/03 2...
Ewa Szyszka
1

votes
3

answer
55

Views

what is the most efficient way to iterate over a very large table and update the rows?

Problem: When using the built-in SQLite3 database in Python (3.6), what are the considerations for iterating over a very large database table and updating it row by row? Requirements: I'll need to iterate each row and use information from a column to perform an action and then update a second column...
GiraffeMan91
1

votes
1

answer
31

Views

SQLite Database for a RecipeApp (one-to-many relationships)

In my app I created a Recipe class, an Ingredient class, and a ListIngredient class; then in my DBHelper I created a Recipe table, an Ingredient table, and a ListIngredient table, to link one Recipe to more ingredients, in this way: String RecipeTable = 'CREATE TABLE ' + TBL_RECIPE + ' ( ' + RECIPE_...
Edoardo Tavilla
1

votes
2

answer
39

Views

SQLite query WHERE with OUTER JOIN

I am a bit rusty with my SQL and am running into a little issue with a query. In our application we have two relative tables to this problem. There are entries, and for each entry there are N steps. We are trying to optimize our querying, so instead of asking for all entries all the time, we just a...
zaknotzach
1

votes
2

answer
33

Views

Android: Save FCM Notification to Room DB

I am using firebase FCM to get the notification. I want if the notification received, that should store in my RoomDatabase. Later on, when the user opens the app, then in notification section all notification will display. I am using MVVM, I was trying to save through ViewModel but it not work. Here...
Akash Chaubey
1

votes
2

answer
27

Views

SQL: simple summation query with join to 2 other tables

I have 3 tables in sqlite as follows: Table 1 ID | x 1, 2.0 2, 3.0 3, 4.0 4, 3.0 Table 2 join_ID | x 1, 5.0 1, 6.0 2, 5.0 2, 2.0 3, 2.0 4, 2.0 Table 3 join_ID | x 4, 5.0 1, 6.0 3, 5.0 2, 2.0 2, 2.0 1, 2.0 I would like to obtain a table as follows: ID | x | x_agg 1, 2, 21 2, 3, 14 3, 4, 11 4, 3, 10 W...
user32882
1

votes
2

answer
57

Views

Is there a way to automatically create CRUD for EF Model (DB First currently)

I am creating a WPF app and I have an existing DB that I would like to use and NOT recreate. I will if I have to, but I would rather not. The DB Is sqlite and when I add it to my data later and create a DataModel based on the DB, I get the model and the DB Context, however there are no methods creat...
Charlie
0

votes
0

answer
6

Views

sqlite shift rowid in multiple records

Hello i have an sqlite db with many records like 10540 record they are ordered by creation time , i want to shift like a record in the middle and like to do it automatically for example : select * from table1 where id >= 8521; UPDATE Table1 SET id = id +1 ; does not work i get Error: Result: UNIQ...
coolirc
1

votes
0

answer
16

Views

I have a problem saving the date I picked using date picker fragment to my database

I have this problem saving the date I picked in a string variable in my database I have already tried saving it formatted with a forward slash and not formatted as a whole number but none of these worked out for me I have it in a RecyclerView like a list of things that when you click on one of the...
Hitham
0

votes
1

answer
20

Views

SQLite if there is existing data skip or overwrite

I have some x,y coordinates in my sqlite but when I add a new data I want to check the data and if there is same data overwrite or skip it. Coordinates come automatically from another method. public void DBCreate(){ SQLITEDATABASE = openOrCreateDatabase('LatLongDatabase', Context.MODE_PRIVATE, null)...
H.P.
1

votes
1

answer
120

Views

SQLite: reuse quoted alias

I'd like to reuse an alias that contains a non-alphabetic character in a query, something like: SELECT 42 AS 'the#answer', 'the#answer'+8 AS 'fifty'; Output I want: 42|50; output I get: 42|8. I've tried almost every possible combination of quote types, and looked for documentation, but I can't seem...
Federico Poloni
0

votes
0

answer
4

Views

Make SQLite errors specific and not just SQLITE_ERROR in express/node.js

So basically I have a similar block written in Express: var sqlite3 = require('sqlite3'); const data = {}; const db = new sqlite3.Database('sqlite/test.db'); db.run('INSERT INTO [Employees]([Name]) VALUES(?)', [name], function (err) { if (err) { //this is the error block data.err...
Abana Clara
1

votes
1

answer
963

Views

how to search for a string in all table Columns in android?

how can i search for a string in all the tables columns ? any help please ? this is my database creation method thanks @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL('CREATE TABLE ' + DATABASE_TABLE + ' (' + KEY_TITLE + ' TEXT NOT NULL , ' + KEY_CO...
chazrmani
1

votes
2

answer
822

Views

Auto_increment ID in Java-Sqlite code

I'm trying to use SQLite with java, I wrote a piece of code that given three String(s) name sname1 sname2 has to populate the table |---------------------| |ID|NAME|SNAME1|SNAME2| |1 |Ale |test |test2 | |---------------------| How could I auto_increment the ID? I tried to use auto_increment in the...
Mitro
1

votes
1

answer
803

Views

How to access database in an HTML5/JS app online and offline with one code base?

I'm working on an HTML/JavaScript GUI application that will run on mobile devices (using Cordova) and also as a hosted web page that's accessible from a desktop web browser. I want to give the option to read and store data in a local offline database (SQL database most likely), as well as the optio...
Mary
1

votes
1

answer
520

Views

Two Columns joining the same table with SqlAlchemy

I'm building a sport related application in Flask using SqlAlchemy. I've got a Game table that has a home_team and an away_team which both join to the Team table. I want to be able to declare the relationship between the Team table and the Game table but I'm getting an error saying Could not determ...
hammygoonan
0

votes
1

answer
8

Views

Using querys to get information from different tables

I am learning SQLite and I'm using this database to learn how to correctly use querys but I'm struggling specially when I have to use data from multiple tables to get some information. For example, with the given database, is there a way to get the first name, last name and the name of songs that ev...
Juan
0

votes
0

answer
6

Views

How fix “Database is locked” when UPDATE

i want to update datas from variable, but I don't know why, when I execute the command this error message is displayed : line 18, in save curseur.execute('UPDATE SAV SET Commentaires_SAV=(?)', (T_Comment.get('1.0', END),) ) sqlite3.OperationalError: database is locked I'm runing on Atom for the scri...
Florian.G
1

votes
1

answer
1.8k

Views

Laravel - PHP Artisan Tinker: How to view all tables in sqlite database

I have a Laravel project and I have a sqlite database. I want to view all the tables in the database in php artisan tinker. I have tried this $tables = DB::select('SHOW TABLES'); but it throws this error Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1 near 'SHOW':...
Clinton Green
1

votes
2

answer
49

Views

How to improve sqlite query speed

I am working in the spatialite-gui with a sqlite db. I have a large table (80,000,000 records) and I want to make a table that includes a code from another table, which has about 48,000 records. I wrote the following code and tested it on the first 80k records of my big table; it took about 1:25 to...
EnKay
1

votes
2

answer
64

Views

Passing an object to a ViewController from AppDelegate

In my application, a Connection object is initialized in my AppDelegate.swift, applicationDidBecomeActive(). How do I pass that Connection object to my ViewController subclass?
coiax
1

votes
3

answer
35

Views

Error With Cursor When Loading Details From Database

I'm currently working through a school project, trying to create a Scout Group manager. This is my first time using Java, XML or Android Studio, so progress has been a little slow, but I'm getting there. The issue I'm having at the moment is with extracting Scout details from a database, in order...
Sam Og
1

votes
1

answer
43

Views

Laravel. Migrations. Adding a field [duplicate]

This question already has an answer here: How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations? 8 answers I insert the field like this: $table->timestamp('added_on')->default('CURRENT_TIMESTAMP'), but in SQLite the default value is 'CURRENT_TIMESTAM...
MyNick
1

votes
4

answer
46

Views

How to query in sql from three table in one statement?

I have three tables as shown below. How to get (in one query statement) the 'product _name' that has been ordered by the user_id =1 ? (product_name for 1 ,2,3 that user_1 ordered) ?
Mark.222
1

votes
2

answer
37

Views

How to write out of memory csv using python?

I need to output the results of a sql query to csv. The query results will be outside of my memory resources. Also csv operations have typically been much slower in pandas then using csv library for me, so I'd prefer not to use pandas. I have tried to create the code below with the intent to batch a...
David 54321
1

votes
1

answer
16

Views

How long is temporary table available in Sqlite?

In my android app I create a temporary table to make some joins on it. db.execSQL('CREATE TEMPORARY TABLE pair (a INTEGER, b INTEGER);') Could you tell me for how long time it's available? It seems that if I restart app it's gone but without restart it seems to be in database but for how long time?...
Vitalii
0

votes
1

answer
23

Views

Maintaining database at client side

Client side: I have a desktop application. The application also has a Sqlite file. I installed it using WIX Toolset on a client machine. When I enter some data in application it gets saved in the database file. Server side: I made some changes to my application like adding an extra column to a table...
AP7
1

votes
2

answer
843

Views

Is it possible to use 'index' as a column name in sqlite?

Is it possible to use index as a column name in sqlite? When i try to execute this query, it is generate with hibernate/jpa: select metadatait0_.id as id25_, metadatait0_.guid as guid25_, metadatait0_.index as index25_, metadatait0_.library_section_id as library7_25_, metadatait0_.metadata_type as m...
cremersstijn
1

votes
1

answer
1.4k

Views

I have a sqlite syntax error. What is incorrect?

I have a sqlite syntax error. What is incorrect? my code: + (void)insertUpdateCatalogTime:(NSString *)time { NSLog(@'%@', time); sqlite3 *database; if(sqlite3_open([DatabaseManager databasePath], &database) == SQLITE_OK) { const char *sqlStatement = [[NSString stringWithFormat:@'UPDATE t_catalog S...
Voloda2
1

votes
2

answer
1.2k

Views

Java Current Date Time not recognized by Sqlite DateTime

I am using the following code in Android to get current date time Date currentDateTime = new Date(); And storing it in the sqlite table as currentDateTime.ToString() SO in the table the values are stored like so 'Tue Sep 11 20:32:39 IST 2012' 'Thu Sep 13 17:23:38 IST 2012' 'Thu Sep 13 17:34:38 IST...
Vrashabh Irde
2

votes
1

answer
18

Views

How can I view these values in the database after I insert them so that I can access them later?

I'm currently setting up a dungeons & Dragons mobile application in android studio and I want to be able to view the values for the character that I am currently saving into a Database 'CharacterDB'. How can I implement that in such a way that will let me find the values to pull them for later use?...
Joe Harkins
1

votes
0

answer
97

Views

Store image in sqlite from variable

I created this database public void onCreate(SQLiteDatabase db) { String query='CREATE TABLE '+tableName+' ( ID INTEGER PRIMARY KEY AUTOINCREMENT, ' + col1+' INTEGER, '+col2+' String, '+col3+' String, '+col4+' String );'; db.execSQL(query); } to sotre an image with some informations in a list I get...
Hossame Makhlof
1

votes
1

answer
71

Views

Retrieve multiple rowids in SQLite insert or update, using JavaScript

I know SQLite have last_insert_rowid() function. I'd like to create a similar function but I want to retrieve an array of indexes, each one coming from an insert or update into my db. To be clearer, I have a populated table like this (name is UNIQUE): rowid name age 1 'John' 18 2 'Anne'...
Zappescu
1

votes
1

answer
97

Views

Password recovery using email

I have a table called users with 3 columns (username, password, email). When a user forgets his password, he needs to insert his email to receive his password by email (password recovery). I have two questions: Why do I always get the message User does not exist? In the second observe, is there a mo...
Ester Silva

View additional questions