Phpworker posted

how to convert MySQL Query to SQLite Query

Can the same MySQL query in MySQL be used in SQLite ?
trying hand on the SQLite ,
i try to run MySQL query to SQLite
and i got error.
let me show what i got ::
trying to import Joomla one table to SQLite



CREATE TABLE IF NOT EXISTS `jos_banner` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL DEFAULT '0',
  `type` varchar(30) NOT NULL DEFAULT 'banner',
  `name` varchar(255) NOT NULL DEFAULT '',
  `alias` varchar(255) NOT NULL DEFAULT '',
  `imptotal` int(11) NOT NULL DEFAULT '0',
  `impmade` int(11) NOT NULL DEFAULT '0',
  `clicks` int(11) NOT NULL DEFAULT '0',
  `imageurl` varchar(100) NOT NULL DEFAULT '',
  `clickurl` varchar(200) NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL,
  `showBanner` tinyint(1) NOT NULL DEFAULT '0',
  `checked_out` tinyint(1) NOT NULL DEFAULT '0',
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `editor` varchar(50) DEFAULT NULL,
  `custombannercode` text,
  `catid` int(10) unsigned NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `sticky` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ordering` int(11) NOT NULL DEFAULT '0',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tags` text NOT NULL,
  `params` text NOT NULL,
  PRIMARY KEY (`bid`),
  KEY `viewbanner` (`showBanner`),
  KEY `idx_banner_catid` (`catid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

i got error "Syntax Error"

Edit Question

why coverting joomla banner table to SQLite ? - shyam

Jan 27 '17 00:44

as i said , trying to learn SQLite . i know mysql so i copy one create table query from joomla and post it in SQLite to check ,how it goes ? - Phpworker

Jan 27 '17 01:11

2

shyam
answered Nov 30 '-1 00:00

HOW differ SQLite from Mysql Create table Query schema ?

AUTOINCREMENT
Dont remove AUTOINCREMENT from query . but remove NOT NULL before AUTOINCREMENT . It should simple like this in SQLite

  
`bid`  INTEGER PRIMARY KEY AUTOINCREMENT

put the AUTOINCREMENT after the INTEGER PRIMARY KEY and it will work , You can put NOT NULL at last like this


  `bid`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

if you dont want to use AUTOINCREMENT than SQLite have other features ROWID

In SQLite, table rows normally have a 64-bit signed integer special column ROWID, _ROWID_, or OID which is unique among all rows in the same table

ROWID used for indexing and it will auto increment at each insert . so there is not need auto Incremented index like id or bid but if you set primary key autoincrement for other column than
if you ask for ROWID, _ROWID_, or OID , it will list primary key column as result


select ROWID from jos_banner

even if i asked for ROWID it will result table's primary key which `bid` column in our case

more detail about SQLite Autoincrement

Column Type
SQLite database Supporting following Column Type:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

So varchar is not in the list of supported column type

is that varchar(255) give me SQL error in SQLite ?
No , it wont type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity
so suppose you create table with following query


CREATE TABLE `jos_banner` (
  `bid`  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  ,
  `type` varchar(30) NOT NULL DEFAULT 'banner',
  `name` varchar(255) NOT NULL DEFAULT '')

type and name is varchar column type so lets check what it column type really now


select typeof(type) from jos_banner

it return typeof(type) = text

so varchar convert to text column type by default .

FYI :: DONT try to use typeof function at Mysql , Mysql dont have typeof function

Edit Answer

1

Rasi
answered Nov 30 '-1 00:00

remove this keyword AUTO_INCREMENT , unsigned , KEY ,ENGINEE =MyISAM DEFAULT CHARSET=utf8
and it good to go
following is working query for above query



CREATE TABLE IF NOT EXISTS `jos_banner` (
  `bid` int(11) NOT NULL ,
  `cid` int(11) NOT NULL DEFAULT '0',
  `type` varchar(30) NOT NULL DEFAULT 'banner',
  `name` varchar(255) NOT NULL DEFAULT '',
  `alias` varchar(255) NOT NULL DEFAULT '',
  `imptotal` int(11) NOT NULL DEFAULT '0',
  `impmade` int(11) NOT NULL DEFAULT '0',
  `clicks` int(11) NOT NULL DEFAULT '0',
  `imageurl` varchar(100) NOT NULL DEFAULT '',
  `clickurl` varchar(200) NOT NULL DEFAULT '',
  `date` datetime DEFAULT NULL,
  `showBanner` tinyint(1) NOT NULL DEFAULT '0',
  `checked_out` tinyint(1) NOT NULL DEFAULT '0',
  `checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `editor` varchar(50) DEFAULT NULL,
  `custombannercode` text,
  `catid` int(10)  NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `sticky` tinyint(1)  NOT NULL DEFAULT '0',
  `ordering` int(11) NOT NULL DEFAULT '0',
  `publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `tags` text NOT NULL,
  `params` text NOT NULL,
  PRIMARY KEY (`bid`)
)

Edit Answer

Phpworker posted

how to convert MySQL Query to SQLite Query

shyam answered Nov 30 '-1 00:00

Rasi answered Nov 30 '-1 00:00

Related Questions

Insert link

Insert image

shyam
answered Nov 30 '-1 00:00

Rasi
answered Nov 30 '-1 00:00