Re: [sqlite] Help with syntax
Hey Barefoot, Finally got it to work! I get how the syntax works now, you declare the columns and set values for them :) That's great, I can start creating my script now, 99,997 more entries to go hehe! Cheers! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, May 20, 2008 9:04:42 AM Subject: Re: [sqlite] Help with syntax Hi Carlo, > Well those original instructions are correct, as long as you unsmarten > the the quotes, so it reads: > > sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " I should clarify that I meant the syntax of your original instructions is correct, whereas what you are attempting is incorrect syntax. You seem to be just guessing and hoping it will work. Have you read through the syntax information on the SQLite site, such as the page I gave you for the insert statement syntax? > Got the following message: > > # sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " > SQL error: table list has no column named owner OK, lets look at the schema: > create table list (id integer primary key,owner_id integer not null > default 0,behavior integer not null default 1,entry text not > null,regex boolean not null default 0,timestamp_last timestamp not > null default 0,source integer not null default 0); As the error said, table list has no column named owner. There is however an owner_id column, so this should work: sqlite /var/local/database/dblist "insert into list (owner_id,behavior,entry) values(0,0,'newblacklistentry.com') " Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo, > Well those original instructions are correct, as long as you unsmarten > the the quotes, so it reads: > > sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " I should clarify that I meant the syntax of your original instructions is correct, whereas what you are attempting is incorrect syntax. You seem to be just guessing and hoping it will work. Have you read through the syntax information on the SQLite site, such as the page I gave you for the insert statement syntax? > Got the following message: > > # sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " > SQL error: table list has no column named owner OK, lets look at the schema: > create table list (id integer primary key,owner_id integer not null > default 0,behavior integer not null default 1,entry text not > null,regex boolean not null default 0,timestamp_last timestamp not > null default 0,source integer not null default 0); As the error said, table list has no column named owner. There is however an owner_id column, so this should work: sqlite /var/local/database/dblist "insert into list (owner_id,behavior,entry) values(0,0,'newblacklistentry.com') " Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Tuesday, May 20, 2008 8:37:37 AM Subject: Re: [sqlite] Help with syntax Well those original instructions are correct, as long as you unsmarten the the quotes, so it reads: sqlite /var/local/database/dblist "insert into list (owner,behavior,entry) values(0,0,'newblacklistentry.com') " Barefoot, Got the following message: # sqlite /var/local/database/dblist "insert into list (owner,behavior,entry) values(0,0,'newblacklistentry.com') " SQL error: table list has no column named owner I put values inside the brackets based on the instructions given me: The value for behavior is 0 for blacklist and 1 for whitelist. Owner is always 0 (for system) in your tests. I typed in: sqlite /var/local/database/dblist "insert into list (0,0,newblacklistentry.com) values(0,0,'newblacklistentry.com') " And I get: SQL error: near "0": syntax error Carlo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo,
> Here are the instructions given me
> You can add entries with inserts from the command line. You should
> be able to do it like this:
>
> sqlite /var/local/database/dblist “insert into list
> (owner,behavior,entry) values(0,0,’newblacklistentry.com’) ”
Well those original instructions are correct, as long as you unsmarten
the the quotes, so it reads:
sqlite /var/local/database/dblist "insert into list
(owner,behavior,entry) values(0,0,'newblacklistentry.com') "
But what you were trying was incorrect:
>> sqlite /var/local/database/dblist "insert into list
>> ('0,0,newblacklistentry1,com') values
>> ('0,0,newblacklistentry1.com')"
>>
Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
@Igor: That's a good idea, taking the list out of a CVS formatted file and run
that command
@Barefoot: Here are the instructions given me, which includes the schema you
requested:
You get the schema of entry with:
.schema
sqlite> .schema
create table list (id integer primary key,owner_id integer not null default
0,behavior integer not null default 1,entry text not null,regex boolean not
null default 0,timestamp_last timestamp not null default 0,source integer not
null default 0);
create table owner (id integer primary key,owner text);
create index list_entry_idx on list(entry);
create index list_owner_idx on list(owner_id);
create index list_regex_idx on list(regex);
create index owner_owner_idx on owner(owner);
sqlite>
You can do queries with:
select * from list;
1|0|1|white.com|0|1210870781|0
2|0|0|black.com|0|0|0
You can add entries with inserts from the command line. You should be able to
do it like this:
sqlite /var/local/database/dblist “insert into list (owner,behavior,entry)
values(0,0,’newblacklistentry.com’) ”
The value for behavior is 0 for blacklist and 1 for whitelist. Owner is always
0 (for system) in your tests.
* Note the syntax he provided does not work straightaway. You can see how I
tried with my SQL knowledge (not that great obviously) to make something out of
the statement provided.
Thanks for the quick replies :) Appreciate it!
Carlo
- Original Message
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, May 20, 2008 7:27:08 AM
Subject: Re: [sqlite] Help with syntax
Carlo S. Marcelo <[EMAIL PROTECTED]>
wrote:
> Below is the syntax and error I received.
>
> [EMAIL PROTECTED] root]# sqlite
> /var/local/database/dblist "insert into
> list ('0,0,newblacklistentry1,com') values
> ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named
> 0,0,newblacklistentry1,com
This statement makes no sense. In the first pair of parens, you are
supposed to provide a list of column names. In the second, a list of
values. A new row is inserted, in which specified columns are set to
specified values (and columns that were not mentioned, if any, take on
their default values). Something like this:
insert into list(column1, column2, column3) values (0, 0,
'newblacklistentry1.com');
> What I am trying to do here is create a script that
> will populate the database with a hundred thousand
> entries (no duplicates).
Perhaps you can use .import directive supported by sqlite command line
shell. You need a file in CSV format, one row per record. Then just do
.import filename tablename
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Carlo S. Marcelo <[EMAIL PROTECTED]>
wrote:
> Below is the syntax and error I received.
>
> [EMAIL PROTECTED] root]# sqlite
> /var/local/database/dblist "insert into
> list ('0,0,newblacklistentry1,com') values
> ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named
> 0,0,newblacklistentry1,com
This statement makes no sense. In the first pair of parens, you are
supposed to provide a list of column names. In the second, a list of
values. A new row is inserted, in which specified columns are set to
specified values (and columns that were not mentioned, if any, take on
their default values). Something like this:
insert into list(column1, column2, column3) values (0, 0,
'newblacklistentry1.com');
> What I am trying to do here is create a script that
> will populate the database with a hundred thousand
> entries (no duplicates).
Perhaps you can use .import directive supported by sqlite command line
shell. You need a file in CSV format, one row per record. Then just do
.import filename tablename
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo,
> insert into list ('0,0,newblacklistentry1,com')
> values ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named 0,0,newblacklistentry1,com
Well, the error tells you the problem. You are asking SQLite to insert
a text value '0,0,newblacklistentry1.com' into a column called
'0,0,newblacklistentry1,com' in a table called list.
Does your table have a column called '0,0,newblacklistentry1,com' ?
I'm guessing not, so there's your problem.
If you're unfamiliar with the syntax required for an insert statement,
look here:
http://www.sqlite.org/lang_insert.html
If you still need help, please post the schema (ie the create table
statements) of your database and explain what you want inserted where.
Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] help with syntax
That did the trick. Thanks very much. jim -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 4:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] help with syntax Anderson, James H (IT) wrote: > I have the following sybase query (a left join) that I'm having trouble > translating into the "LEFT JOIN" syntax. Any help appreciated. > > Thanks, > jim. > > > create table tmpRR_ML as > select > b.ratingCodeas ratingCode, > a.CDSpreadCurve as CDSpreadCurve > from tmpRR a, >MasterList b, >crRefRating c > where b.dunsNumber = b.ultimateDuns; >and a.Credit_Ultimate_Party_Id *= b.dunsNumber >and b.ratingCode *= c.rating; > > > NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. > > James, I suspect this should be the following for SQLite, but I'm just guessing at the *= syntax from sybase. create table tmpRR_ML as select b.ratingCodeas ratingCode, a.CDSpreadCurve as CDSpreadCurve from tmpRR a left join MasterList b on a.Credit_Ultimate_Party_Id = b.dunsNumber left join crRefRating c on b.ratingCode = c.rating where b.dunsNumber = b.ultimateDuns; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] help with syntax
Thanks, Dennis, I'll try that. Yes, "*=" means "LEFT JOIN" in sybase syntax. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 4:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] help with syntax Anderson, James H (IT) wrote: > I have the following sybase query (a left join) that I'm having trouble > translating into the "LEFT JOIN" syntax. Any help appreciated. > > Thanks, > jim. > > > create table tmpRR_ML as > select > b.ratingCodeas ratingCode, > a.CDSpreadCurve as CDSpreadCurve > from tmpRR a, >MasterList b, >crRefRating c > where b.dunsNumber = b.ultimateDuns; >and a.Credit_Ultimate_Party_Id *= b.dunsNumber >and b.ratingCode *= c.rating; > > > NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. > > James, I suspect this should be the following for SQLite, but I'm just guessing at the *= syntax from sybase. create table tmpRR_ML as select b.ratingCodeas ratingCode, a.CDSpreadCurve as CDSpreadCurve from tmpRR a left join MasterList b on a.Credit_Ultimate_Party_Id = b.dunsNumber left join crRefRating c on b.ratingCode = c.rating where b.dunsNumber = b.ultimateDuns; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] help with syntax
Anderson, James H (IT) wrote: I have the following sybase query (a left join) that I'm having trouble translating into the "LEFT JOIN" syntax. Any help appreciated. Thanks, jim. create table tmpRR_ML as select b.ratingCodeas ratingCode, a.CDSpreadCurve as CDSpreadCurve from tmpRR a, MasterList b, crRefRating c where b.dunsNumber = b.ultimateDuns; and a.Credit_Ultimate_Party_Id *= b.dunsNumber and b.ratingCode *= c.rating; NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. James, I suspect this should be the following for SQLite, but I'm just guessing at the *= syntax from sybase. create table tmpRR_ML as select b.ratingCodeas ratingCode, a.CDSpreadCurve as CDSpreadCurve from tmpRR a left join MasterList b on a.Credit_Ultimate_Party_Id = b.dunsNumber left join crRefRating c on b.ratingCode = c.rating where b.dunsNumber = b.ultimateDuns; HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
