MySQL Forums - SQLite

Sync Android SQLite with MySQL and Microsoft SQL Server (no replies)

Alice Qiao — Thu, 21 Feb 2013 05:01:04 +0000

Pervasync can now sync Android SQLite with MySQL and Microsoft SQL Server. Check it out at http://www.pervasync.com

Minecraft Server | Logblock | need help (no replies)

mike Watson — Wed, 25 Jul 2012 06:16:44 +0000

Hi, i'm new to mySQL and i need some help connecting mySQL to my logblock plugin for my server, i'm a little confused about it, and i'm using 000.webhost.com for my database, so i'm not entirely sure what to do from there, someone please help

BLOB type for Sqlite to MySQL export (1 reply)

DUS Jean-Claude — Tue, 28 Aug 2012 12:50:29 +0000

Hi,

I'm converting tables from Sqlite to MySQL. All is fine except for png pictures with BLOB type in Sqlite.

How can I convert those data keeping the same BLOB type in MySQL ? Can you suggest me a tool to do this ? is ANSI or utf-8 encoding important for this export ?

For your information, the Sqlite database comes from uWamp and the Sqlite Manager interface in uWamp includes a MySQL export option. But the BLOB type data (png pictures) do not appear anymore in the file exported for MySQL.

Thanks in advance.

sqlite to mysql (5 replies)

dallas smith — Mon, 16 May 2011 11:34:09 +0000

I'm trying to migrate from sqlite to mysql and am having trouble with this table creation. I am wondering how other people have got around the problem of creating unique or primary keys on tables.

CREATE TABLE wiki (
name text,
version integer,
time integer,
author text,
ipnr text,
text text,
comment text,
readonly integer,
UNIQUE (name,version)
);

IllegalStateException : inappropriate state (no replies)

Tej Kiran Sharma — Wed, 07 Apr 2010 07:36:56 +0000

Hi,
i am getting following problem while closing ResultSet.

-------------------------------------------
Code is
-------------------------------------------
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
rs = ResultSet
ps = PreparedStatement
these are in try catch block.
-------------------------------------------
error message : call() called in inappropriate state
stacktrace : java.lang.IllegalStateException: call() called in inappropriate state at org.ibex.nestedvm.Runtime.call(Runtime.java:655) at org.ibex.nestedvm.Runtime.call(Runtime.java:647) at
org.sqlite.NestedDB.call(NestedDB.java:406) at org.sqlite.NestedDB.call(NestedDB.java:389) at org.sqlite.NestedDB.reset(NestedDB.java:135) at org.sqlite.RS.close(RS.java:98)

can any body help me, why i am getting such exception and what is the solution for it. i am using sqlitejdbc-v056 version

Thanks in Advance
Tej Kiran

SQLite - IIS, PHP and java (no replies)

alexis katsavras — Wed, 10 Feb 2010 12:21:33 +0000

Hi there,

I am at the research stage of a project i have been asked to undertake.

At the moment the O/S will be windows server 2003 and the web server IIS. These are set and i cannot change them.

I will be using PHP to deliver the web content to users and also a Java application will be used to integrate to third party product.

Both PHP and Java will be accessing a SQLite db concurrently. SQLite, Java App and php will all be on one box. So my questing is should this setup work OK? Is there anything i should be aware off.

Thank you for any help, pointers

Alexis

mysql to sqlite (no replies)

Andy Edwards — Sun, 31 Jan 2010 18:15:54 +0000

Hi Guys,

I've been playing with Roundcube on my PC and I'm very impressed... but now I'd like to put it on a rather bare bones server and I was thinking that as I don't need it for anything else (being a mailserver) I could get away without using mysql - seeing as it's only for family use.

Is there any way I can move from mysql to sqlite without having to set up Roundcube again?

Thanks,

problem with inserting values into sqlite table (no replies)

kugb kjlhb — Thu, 29 Oct 2009 15:42:00 +0000

hello everyone!
I`m trying to use sqlite database with c++, but something isnt working right...

The problem I`m dealing here is that i cant insert other datas into the table i`ve created except NULL, because i get following error message:

Error in select statement : INSERT INTO a (a,b,c,d) VALUES (NULL, NULL, something_else, NULL) [no such column: something_else].

when i write :

insert(4, aa, "NULL", "NULL", "NULL");

then NULL "values" are inserted

I guess i`ve made some mistake with using pointers, but i dont see it.. I`m not very experienced programmer..

here is the main function:

int main ()
{

char data_base_name [20];
char *db_name;
db_name = data_base_name;

char tab_name [20];
table_name=tab_name;

open_db ("db_name");

cout<<"Insert table name:"< cin>>tab_name;

char a[10]="a";
char aa[100]="NULL";

select_stmt ("DROP TABLE a");

create_table (4, a, "b", "c", "d");

insert(4, aa, "NULL", "something_else", "NULL");

sqlite3_close(db);

getchar ();

return 0;
}

create function, which i think works ok, but who knows...

int create_table (int no_col, char *fmt, ...)
{
int i;
char f[500] = "CREATE TABLE ";

va_list ptr;
va_start (ptr, fmt);
for (i=0; i<(no_col-1); i++)
{
strcat (fmt, ","); // fmt = a ,b ,c ,d
strcat(fmt, (va_arg (ptr, char*)));
}
va_end (ptr);

fmt1 = fmt; //fmt1- global pointer

strcat (f, table_name); // "CREATE TABLE x
strcat (f, " ("); // "CREATE TABLE x "(
strcat (f, fmt); // "CREATE TABLE x ( a,b,c,d
strcat (f,")"); // "CREATE TABLE x (a,b,c,d)"

char * stmt = f ;
printf ("\nstmt = %s\n", stmt);
select_stmt (stmt);

return 0;
}

insert function :

int insert (int no_col, char *fmt2, ... )
{
int i;
va_list ap;
va_start (ap, fmt2);

for (i=0; i<(no_col-1); i++)
{
strcat (fmt2, ",");
strcat (fmt2, (va_arg(ap, char*)));
}
va_end (ap);

char k[500]= "INSERT INTO ";
strcat (k, table_name );
strcat (k, " ( ");
strcat (k, fmt1);
strcat (k, ") ");
strcat (k, "VALUES (");
strcat (k, fmt2);
strcat (k, ")");
printf ("\nk = %s\n\n", k);

char * stmt = k;
select_stmt (stmt);

return 0;
}

any help and suggestions very appreciated..

T

overcoming sqlite "database locked' error (no replies)

Allasso Travesser — Wed, 12 Aug 2009 18:10:15 +0000

Hello,

I would like to pass cookies back and forth between a search script I have written that runs on the local system, and javascript pages displaying the forms and results in Firefox. I'll forego all the gory details here, it is working pretty well, but being able to pass cookies back and forth would make allow me to make it much more robust. FF (3+) uses an sqlite db to store it's cookies. I have tried to edit cookies using sqlite3, but I keep getting a 'database locked' error, incessantly. (Does FF simply put a lock on this file at the beginning of a session and not release it til it is done?)

I have read things that talk about using fcntl() to get around this, but it is quite over my head, though I am willing to invest, as I really want to accomplish this.

If I could get some suggestions on how to handle this situation to my ends, I would much appreciate it. I could just simply dump the db, alter the cookie values, create a new db with the modified dump file, and then copy it over the existing cookies db. This works. Perhaps a more kosher way would be to use fcntl() to get an exclusive lock beforehand. This is an area I am quite unfamiliar with, but willing to invest in learning if it seems feasible. I really would like to be able to edit the cookies outside of FF and I believe there is a way without just forcing it. Reading them is no problem, but I want to be able to write flags back to the javascript while it is running.

Perhaps copying over the db file is not such a big deal, after all, this would (most likely) only be occurring while the user is using the search feature anyway. Any thoughts on this? But if I can do this in a more kosher manner, it would be better.

Any input on this?

Thanks much,

Allasso

Subqueries (1 reply)

shan hyland — Wed, 22 Jul 2009 08:53:17 +0000

Hi,

I am doing an assignment using SQLite and was wondering if someone could tell me why this doesn't work and maybe offer some help please?

select *, count(distinct au.acNum) as auNum, count(int.acNum) as intNum
from academic ac
LEFT OUTER JOIN author au on ac.AcNum = au.AcNum
LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum
group by ac.AcNum
having count(distinct au.acNum) =

(select Max(int.acNumCount)
from (select count(int.acNum) as int.AcNumCount
from academic ac
LEFT OUTER JOIN author au on ac.AcNum = au.AcNum
LEFT OUTER JOIN interest int on int.AcNum = ac.AcNum
group by ac.AcNum
having auNum = 0))

Working with blob fields (1 reply)

Daniel Andrascik — Mon, 18 May 2009 07:27:16 +0000

Working with blob fields

In SQLite can be use syntax like this:

CREATE TABLE aTbl (a integer, b BLOB);
INSERT INTO aTbl (a, b) VALUES (1, X'A9AE80');

Second line insert into Blob field "b" hexadecimal value 'A9AE80'. Exist analogous technique in MySQL? Or something else?

Thanks

Attach SQLite in MySQL (1 reply)

Srinivasan T — Tue, 02 Jun 2009 12:43:00 +0000

Hi,

I'm writing a simple CPP application to migrate data from SQLite database to MySQL database. I am not sure if it is possible to attach a SQLite database in MySQL connection (as we attach another SQLite database in SQLite database ie., using 'attach database ' syntax.)

FYI, I will not be able to dump the SQLite database and import the sql file to MySQL since I have BLOB data in my SQLite database.

Any help would be much appreciated.

Regards,
Srini.

this query in sqlite (no replies)

rachmat febfauza — Sat, 20 Dec 2008 01:34:50 +0000

I have problem with executing this query in sqlite. to reconstruct problem please follow the following steps.

1. create table awal1, akhir1 and hasil1 first.

CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product varchar(60), Location varchar(50), "Begin" datetime);

INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2', '2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', '2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2', '2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', '2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2', '2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1', '2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', '2007-05-07 17:28:25');

CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product varchar(60), Location varchar(50),"End" datetime);

INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2', '2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', '2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2', '2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', '2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', '2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2', '2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1', '2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', '2007-05-07 17:28:27');

CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50), Product varchar(60), Location varchar(50), "Begin" datetime, "End" datetime, Difference integer, PRIMARY KEY (Code,Level,Category,Product,Location,"Begin","End"));

2. then execute this query

insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, awal1.Product, awal1.Location, awal1."Begin",akhir1."End", strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", awal1.Code, awal1.Category, awal1.Product, awal1.Location;

3. check the result

the result that i hope is like this

A1236 MEDIUM FOOD SNACK HOMS 1 2007-5-6 10:48:57 2007-5-6 11:19:25 1828
A1236 MEDIUM FOOD SNACK HOMS 2 2007-5-6 10:48:57 2007-5-6 11:19:21 1824
A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:20:34 2007-5-6 11:31:57 683
A1222 SMALL FOOD WAFER HOMS 2 2007-5-6 11:20:34 2007-5-6 11:31:57 683
A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:31:57 2007-5-6 11:31:57 0
A1221 SMALL FOOD CAKE HOMS 2 2007-5-6 11:31:57 2007-5-6 11:31:57 0
A1220 SMALL FOOD MARGARINE HOMS 1 2007-5-6 11:42:46 2007-5-6 11:42:46 0
A1221 SMALL FOOD CAKE HOMS 1 2007-5-6 11:42:46 2007-5-6 11:42:46 0
A1222 SMALL FOOD WAFER HOMS 1 2007-5-6 11:42:46 2007-5-6 11:42:46 0
A1269 SMALL CLOTHES BELT HOMS 3 2007-5-7 17:28:25 2007-5-7 17:28:27 2

how to make result like i hope?

thanks for advanced

note : the result that i hope is mysql result's, so i confuse how to do this in sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows in mysql server, my computer starts not responding temporary, it tooks 100% cpu usage but less PF or memory usage.

re : install mysql (no replies)

pavan kumar — Wed, 15 Oct 2008 08:53:20 +0000

Hi,

We are developing desktop application at .net, we want to use mysql for backend,
now i need some help, we want to install mysql through programatically, can any body help me, how to install mysql through programatically,

Steps for setting up *.db in SQLite same as MySQL? (no replies)

kris hanigan — Sun, 08 Jun 2008 03:34:46 +0000

I'm new to MySQL within '08 and will be giving SQLite a try this week, but my question is - the steps to make a *.db the same as they are in MySQL? I read on a blog that there's a whole different procedure which frightens me because I'm far from a pro and it took me long enough to figure out the original.

Creating a MySQL standalone database just like SQLite/MsAccess (3 replies)

Kaiyang Tay — Tue, 27 May 2008 16:36:09 +0000

Hi

Will like to know is it possible to make a database file for MySQL just like those such as MS Access and SQLite. So that it is possible to pack the database with application?

So far, what I have always thought, that MySQL is a database server which must be installed in order to let any applications dependent on it to be workable. Until recently I saw Caeser IV is packed with MySQL.

How can I make a *.db or something similar?

is there any light edition of mySQL (9 replies)

netman mo — Mon, 25 Aug 2008 02:07:13 +0000

hi

well I am working on a project that we need a very small database that can be installed on client system but our database needs to have some intelligent triggers and some small database like access can not support this intelligency so I want to know if there is any light edition of mysql that we can use it an edition that has the functionality of triggers.

thanks for your attention

Best Regards.

Using MySQL for desktop applications (1 reply)

Rohit Khare — Mon, 21 Aug 2006 16:43:21 +0000

Is it appropriate to use MySQL for stand-alone desktop applications? I mean instead of MS Access.

MySQL without server (1 reply)

Paul Gessos — Sun, 26 Mar 2006 18:12:07 +0000

When I create simple standalone programs in Java, C++, etc, I don't want to use MySQL server.

For end user:
It is hard to setup.
It is not safe for a dummie to setup MySQL server.
It is really big (if application is only 800 KBs).

I want a client library which can use MySQL databases (files in one specific from application directory) without any type of server.

I mean something like MS Access, but the library only. Not an extra program.

This database will be accessed by only one person/app, so there is no problem if the library does not support multiaccess.

Is there any similar thing out there?

Thanks.

performance: Mysqld / Mysql Embedded / SQL lite (1 reply)

Sylvain Becker — Fri, 03 Mar 2006 19:10:43 +0000

Hi,

Does anyone know about database performance, overheads between those three solutions:
Mysqld, Mysqld Embedded, SQL.
I am looking for information and comparaison, especially in terms of:
- speed
- for high concurrency and lots of access

Thank you and best regards,

Sylvain

Need to Convert SQLite 3 to csv (1 reply)

Patrick Kelly — Sun, 28 May 2006 07:31:25 +0000

This may be the wrong forum but I thought I would see if anyone knew how to convert SQLite 3 files to csv. I have two viewers and I can view the part of the SQLite 3 file allocated to fields. However, there is a paragraph of text associated with each record that makes up 98% of the record that does not show up in the viewers. Does anyone know how I can do a straight conversion to csv or xls from SQLite 3? Thanks in advance for any replies.

SQLite Migration (6 replies)

Laurent Goussard — Wed, 03 Jun 2009 13:58:55 +0000

Hello !

Is an SQLite support planned for a future release of the MySQL Migration toolkit ?
I miss it a lot ;)

SQLITE module not getting loaded (1 reply)

Adnan Siddqi — Mon, 06 Feb 2006 14:17:56 +0000

I am using php 5.12(zip version) and i am getting sqlite module not found error ,despite of php_sqlite.dll is present in folder.Any idea?

thanks

-adnan

INSERTS get progressively slower (2 replies)

Dean Sadites — Sun, 28 May 2006 07:48:04 +0000

I just installed MySQL 4.1.13 and the MySQL Connector Net 1.04. I've written a C# script to create a table and insert approximately 2.4 million records from a table in another database (Oracle). The problem is that the more records that are inserted, the slower it gets. This behavior persists whether I do all inserts as a single transaction, or whether I do a commit after each group of 10,000 or 100,000 inserts. Doesn't seem to matter -- just keeps getting slower and slower. I can do the exact same operation on a SQLite database under the exact same conditions (i.e. same script, new table, no indexes) and insert the entire 2.4 million records in about 16 minutes. I would have thought that MySQL would at least be able to match or exceed what SQLite can do.

Below is the log from my script. In this case, I'm doing a begin transaction and commit for each block of 10,000 rows. It starts off taking about 8 seconds for the first 10,000, but it's already up to almost 2.5 minutes for the 10,000 rows between 440,000 and 450,000. SQLite would have finished inserting the full 2.4 million records about 15 minutes earlier. (I realize that MySQL provides a bulk insert from file capability, but I'm really not interested in using this. If SQLite can do what I want, I think MySQL should be able to also.) Can anyone explain what's going on here and how performance might be improved (or at least be consistent for each group of inserts)?

2005-07-22 19:20:05 10,000 records written...
2005-07-22 19:20:13 20,000 records written...
2005-07-22 19:20:23 30,000 records written...
2005-07-22 19:20:31 40,000 records written...
2005-07-22 19:20:40 50,000 records written...
2005-07-22 19:20:51 60,000 records written...
2005-07-22 19:21:00 70,000 records written...
2005-07-22 19:21:09 80,000 records written...
2005-07-22 19:21:22 90,000 records written...
2005-07-22 19:21:34 100,000 records written...
2005-07-22 19:21:48 110,000 records written...
2005-07-22 19:22:01 120,000 records written...
2005-07-22 19:22:14 130,000 records written...
2005-07-22 19:22:30 140,000 records written...
2005-07-22 19:22:45 150,000 records written...
2005-07-22 19:23:04 160,000 records written...
2005-07-22 19:23:21 170,000 records written...
2005-07-22 19:23:39 180,000 records written...
2005-07-22 19:23:57 190,000 records written...
2005-07-22 19:24:16 200,000 records written...
2005-07-22 19:24:35 210,000 records written...
2005-07-22 19:24:55 220,000 records written...
2005-07-22 19:25:17 230,000 records written...
2005-07-22 19:25:46 240,000 records written...
2005-07-22 19:26:15 250,000 records written...
2005-07-22 19:26:46 260,000 records written...
2005-07-22 19:27:23 270,000 records written...
2005-07-22 19:28:06 280,000 records written...
2005-07-22 19:28:55 290,000 records written...
2005-07-22 19:29:47 300,000 records written...
2005-07-22 19:30:47 310,000 records written...
2005-07-22 19:31:55 320,000 records written...
2005-07-22 19:33:14 330,000 records written...
2005-07-22 19:34:46 340,000 records written...
2005-07-22 19:36:24 350,000 records written...
2005-07-22 19:38:05 360,000 records written...
2005-07-22 19:39:58 370,000 records written...
2005-07-22 19:41:58 380,000 records written...
2005-07-22 19:43:54 390,000 records written...
2005-07-22 19:46:01 400,000 records written...
2005-07-22 19:48:12 410,000 records written...
2005-07-22 19:50:29 420,000 records written...
2005-07-22 19:52:45 430,000 records written...
2005-07-22 19:55:53 440,000 records written...
2005-07-22 19:58:17 450,000 records written...