Help using 'attach database' or Why is this an error?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Help using 'attach database' or Why is this an error?

J Decker
When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map
(option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map
(option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help using 'attach database' or Why is this an error?

Peter da Silva
Try this:

attach database 'test.db' as con2;

On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" <[hidden email] on behalf of [hidden email]> wrote:

    When I attempt to use attach database I get an error about 'no such column'
   
   
    M:\sqlite3\sqlite3\build>sqlite3 test.db
   
    SQLite version 3.23.0 2018-04-02 11:04:16es
    Enter ".help" for usage hints.
    sqlite> attach database test.db as con2;
    Error: no such column: test.db
    sqlite>
   
   
    I was trying to debug this set of commands, which is actually run on two
    connections within the same process... (oh I had misspelled foreign as
    foriegn)
   
   
   
   
    PRAGMA foreign_keys=on;
   
    create table `option4_name` (`name_id` char(36) NOT NULL,`name`
    varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);
   
    create table `option4_map` (`option_id` char(36) NOT
    NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
    NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
    (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
    REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
    CASCADE ON DELETE CASCADE);
   
    create table `option4_values` (`option_id` char(36) default '0',`string`
    varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
    UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
    (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
    DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
    and
   
    BEGIN TRANSACTION;
   
    insert into option4_name (name_id,name) values(
    '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );
   
    insert into option4_map
    (option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
    );
   
    #SQLITE ERROR:Result of prepare failed? foreign key mismatch -
    "option4_map" referencing "option4_name" at char 185[] in [insert into
    option4_map
    (option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
    )]
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help using 'attach database' or Why is this an error?

J Decker
On Wed, Apr 11, 2018 at 7:54 AM, Peter Da Silva <
[hidden email]> wrote:

> Try this:
>
> attach database 'test.db' as con2;
>
ahh that makes sense.
I'll split this into a separte thread for the real issue...


>
> On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" <
> [hidden email] on behalf of [hidden email]>
> wrote:
>
>     When I attempt to use attach database I get an error about 'no such
> column'
>
>
>     M:\sqlite3\sqlite3\build>sqlite3 test.db
>
>     SQLite version 3.23.0 2018-04-02 11:04:16es
>     Enter ".help" for usage hints.
>     sqlite> attach database test.db as con2;
>     Error: no such column: test.db
>     sqlite>
>
>
>     I was trying to debug this set of commands, which is actually run on
> two
>     connections within the same process... (oh I had misspelled foreign as
>     foriegn)
>
>
>
>
>     PRAGMA foreign_keys=on;
>
>     create table `option4_name` (`name_id` char(36) NOT NULL,`name`
>     varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);
>
>     create table `option4_map` (`option_id` char(36) NOT
>     NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id`
> char(36)
>     NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
> UNIQUE
>     (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
>     REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
> CASCADE,
>     FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
>     CASCADE ON DELETE CASCADE);
>
>     create table `option4_values` (`option_id` char(36) default
> '0',`string`
>     varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
> `value_id`
>     UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
>     (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
>     DELETE CASCADE);select tbl_name,sql from sqlite_master where
> type='table'
>     and
>
>     BEGIN TRANSACTION;
>
>     insert into option4_name (name_id,name) values(
>     '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );
>
>     insert into option4_map
>     (option_id,parent_option_id,name_id)values('00000000-0000-
> 0000-0000-000000000000','00000000-0000-0000-0000-
> 000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
>     );
>
>     #SQLITE ERROR:Result of prepare failed? foreign key mismatch -
>     "option4_map" referencing "option4_name" at char 185[] in [insert into
>     option4_map
>     (option_id,parent_option_id,name_id)values('00000000-0000-
> 0000-0000-000000000000','00000000-0000-0000-0000-
> 000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
>     )]
>     _______________________________________________
>     sqlite-users mailing list
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Help using 'attach database' or Why is this an error?

Hick Gunter
In reply to this post by J Decker
Try

ATTACH DATABASE "test.db" as con2;

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von J Decker
Gesendet: Mittwoch, 11. April 2018 16:51
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Help using 'attach database' or Why is this an error?

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36) NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id` UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values( '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map
(option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch - "option4_map" referencing "option4_name" at char 185[] in [insert into option4_map (option_id,parent_option_id,name_id)values('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users