I'm not sure this is quite the right place, but there do seem to be allot of posts with folks using ruby to play nicely with databases so I thought I would give it a go.
I am starting a long process of developing a database application bases on SQLite and ruby. This will run on various linux flavors as well as windows/cygwin. I am using ruby because the interface will eventually be browser based and ruby on rails or Sinatra seems like a good way to get that working. I have looked at some tutorials, but they are either too simple or too complicated. My first step is to do a simple import of a tab delimited text file to create a SQLlite database. There would be one database and several tables, so I need to know how to map each column in the file to table/column. It could be just one table for now if that is simpler.
Can some point me in the right direction to get me going? I have worked through allot of this tutorial, SQLite Ruby tutorial
This code,
Code:
#!/usr/bin/ruby
require 'sqlite3'
begin
db = SQLite3::Database.open "test.db"
db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY,
Name TEXT, Price INT)"
db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
db.execute "INSERT INTO Cars VALUES(3,'Skoda',9000)"
db.execute "INSERT INTO Cars VALUES(4,'Volvo',29000)"
db.execute "INSERT INTO Cars VALUES(5,'Bentley',350000)"
db.execute "INSERT INTO Cars VALUES(6,'Citroen',21000)"
db.execute "INSERT INTO Cars VALUES(7,'Hummer',41400)"
db.execute "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
rescue SQLite3::Exception => e
puts "Exception occured"
puts e
ensure
db.close if db
end
inserts some data to a table, but a hard coded comma separated list is a long way from a delimited text file. I think it's fine for the mapping to be hard coded for now, but this is something that would eventually be entered from a browser interface. At the end of the day, one function would be to select a file from a web interface and load it into a SQLite database. There would be mapping for each column in the file to a table/column in the database, and also a hash to determine if the record already exists. Where a record already exists, data could be added, where it doesn't exist, it will be created.
This is relatively straightforward stuff, but it always seems to take a while to get going.
i'm on freebsd 5.2.1, and from a fresh installation i've used pkg_add for the latest ported version of apache, as well as installing php 5. supposedly php5 comes with native support for sqlite (in the binary package), and this is what i added.
i am trying to install a site engine (the 'gyrator'... (0 Replies)
Hi everybody,
We have a cgi application which accesses sqlite database. It works fine in Linux environment but the same code doesn't enter data into the database when done in HP-UX environment. Should the codes vary depending on whether it is Linux or HP-UX.
Regards
Ruma (1 Reply)
Hi everyone,
I have a requirement that requires me to fill an sqlite database with 100,000 entries (no duplicates).
I will start out by giving the command that will insert the values necessary to populate the database:
# sqlite /var/local/database/dblist "insert into list... (2 Replies)
Hi all
I have installed a demo version of SCO OpenServer 5.0.2, I finally found it is Desktop Interface, I would like to know how to change its interface to dos based interface?
If you have any ideas, please tell me then. Thank you (2 Replies)
I have several bash scripts that write to an SQLite3 database at the same time. At some occasion the database returns: SQL error: database is locked.
How would be the best way, to make a process to wait until the data base is 'free' again. I tried:
sqlite3 test.db ".timeout 1000; update....."... (2 Replies)
Hi Gents,
What is the best way to create a web interface for mysql database? That I can manage my data for root user and for normal user as well according to their privileges? Assuming that I use Linux as a server and Apache as a web server and my database is mysql.
Thanks in advance (5 Replies)
Hello,
Please suggest me the ways how to get graphical interface on AIX server.I need to create oracle database for which I need graphical access.
Best regards,
Vishal (4 Replies)
Discussion started by: Vishal_dba
4 Replies
LEARN ABOUT REDHAT
insert
INSERT(7) SQL Commands INSERT(7)NAME
INSERT - create new rows in a table
SYNOPSIS
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }
INPUTS
table The name (optionally schema-qualified) of an existing table.
column The name of a column in table.
DEFAULT VALUES
All columns will be filled by null values or by values specified when the table was created using DEFAULT clauses.
expression
A valid expression or value to assign to column.
DEFAULT
This column will be filled in by the column DEFAULT clause, or NULL if a default is not available.
query A valid query. Refer to the SELECT statement for a further description of valid arguments.
OUTPUTS
INSERT oid 1
Message returned if only one row was inserted. oid is the numeric OID of the inserted row.
INSERT 0 #
Message returned if more than one rows were inserted. # is the number of rows inserted.
DESCRIPTION
INSERT allows one to insert new rows into a table. One can insert a single row at a time or several rows as a result of a query. The col-
umns in the target list may be listed in any order.
Each column not present in the target list will be inserted using a default value, either a declared DEFAULT value or NULL. PostgreSQL will
reject the new column if a NULL is inserted into a column declared NOT NULL.
If the expression for each column is not of the correct data type, automatic type coercion will be attempted.
You must have insert privilege to a table in order to append to it, as well as select privilege on any table specified in a WHERE clause.
USAGE
Insert a single row into table films:
INSERT INTO films VALUES
('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
In this second example the last column len is omitted and therefore it will have the default value of NULL:
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
In the third example, we use the DEFAULT values for the date columns rather than specifying an entry.
INSERT INTO films VALUES
('UA502','Bananas',105,DEFAULT,'Comedy',INTERVAL '82 minute');
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
Insert a single row into table distributors; note that only column name is specified, so the omitted column did will be assigned its
default value:
INSERT INTO distributors (name) VALUES ('British Lion');
Insert several rows into table films from table tmp:
INSERT INTO films SELECT * FROM tmp;
Insert into arrays (refer to the PostgreSQL User's Guide for further information about arrays):
-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these queries create the same board attribute)
INSERT INTO tictactoe (game, board[1:3][1:3])
VALUES (1,'{{"","",""},{},{"",""}}');
INSERT INTO tictactoe (game, board[3][3])
VALUES (2,'{}');
INSERT INTO tictactoe (game, board)
VALUES (3,'{{,,},{,,},{,,}}');
COMPATIBILITY
SQL92
INSERT is fully compatible with SQL92. Possible limitations in features of the query clause are documented for SELECT [select(7)].
SQL - Language Statements 2002-11-22 INSERT(7)