Re: [sqlite] best language match for SQLite?

[Javier Julio]

Um... excuse me! You can all move to the side and make room for Flex  
and AIR!

Ciao!
Javi

On Sep 16, 2008, at 5:29 PM, A. H. Ongun wrote:

> Best language to use is the one that is suited for the job.
>
> Examples:
> I use Perl as a gluing language to connect various subsystems  
> together, and as a swiss army knife.
> C, C++ for embedded development on Linux.
> C++ for Windows development.
> Forth for embedded development on some specialized hardware.
> SQL for Oracle, and MS SQL work.
>
> and Vim to rule them all.  :)
>
> andy
>
>
> "Message: 1
> Date: Tue, 16 Sep 2008 11:50:40 -0400
> From: Patrick <[EMAIL PROTECTED]>
> Subject: [sqlite] best language match for SQLite?
> To: General Discussion of SQLite Database 
> Message-ID: <[EMAIL PROTECTED]>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> I am a beginner to intermediate Python Programmer. I can use SQLite  
> with
> it just fine but it is my understanding that relational database and
> object oriented programming our not the perfect marriage.
>
> I was just wondering if anyone had an opinion on the most ideal  
> language
> to use with SQLite?
>
> I love Python but I LOVE SQLite, I would learn another language just  
> to
> use it better-Patrick  "
>
>
>
>
> ___
> 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] statement is not executing

[Javier Julio]

Greg,

I'm sorry to hear about the troubles you are running into. I've been using
SQLite since I've been doing AIR development although my real bread and
butter is CF development. I've never seen anyone connect CF8 and SQLite and
am wondering why you have chosen that? The reason I ask is CF8 comes with
Derby embedded and I would think that would be the best answer for you.
Sorry I can't help any further.

Ciao!
Javi

On Mon, Sep 1, 2008 at 11:46 AM, Greg Morphis <[EMAIL PROTECTED]> wrote:

> It's just a simple insert into table (columns) values (values)
>
>  returntype="boolean">
>
>   
>
>   
>
> 
>INSERT INTO
>   BILLS
>   (
>  PAYEEID,
>  PAYEE,
>  MINIMUMDUE,
>  DUEDAY,
>  ISCC,
>  ISACTIVE,
>  AMOUNTOWED,
>  APR
>   )
>VALUES
>   (
>  
> value="#arguments.bill.getPayeeID()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getPayee()#"
> cfsqltype="cf_sql_longvarchar" />,
>  
> value="#arguments.bill.getMinimumDue()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getDueDay()#"
> cfsqltype="cf_sql_longvarchar" />,
>   value="#arguments.bill.getIsCC()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getIsActive()#"
> cfsqltype="cf_sql_numeric" />,
>  
> value="#arguments.bill.getAmountOwed()#"
> cfsqltype="cf_sql_numeric" />,
>   value="#arguments.bill.getAPR()#"
> cfsqltype="cf_sql_numeric" />
>   )
> 
>
>   
>
> 
>
> and the DB looks like..
>
> CREATE TABLE bills
> (
>  payeeid integer primary key,
>  payee TEXT,
>  minimumdue numeric,
>  apr numeric,
>  amountowed real,
>  iscc numeric DEFAULT 1,
>  dueday TEXT,
>  isactive numeric DEFAULT 1
> )
>
>
>
>
>
>
>
> On Mon, Sep 1, 2008 at 10:24 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> >> I understand this is a sqlite list, I had hoped that I wasnt the only
> >>  one using it or trying to use it with ColdFusion..
> >>  I've also asked this on a CF list, just hoping somewhere out there is
> >>  the answer..
> >
> > No, no... don't get me wrong. There is no problem with describing the
> > tools you are using (CF and JDBC driver, in this case). However, you
> > have given no other context... no db schema, no example code, the
> > query you are executing, etc. There is little here for anyone to try
> > and help, other than if there happens to be another CF/SQLite/JDBC
> > user.
> >
> > Just offer more details, and you have a slightly better chance of
> > getting a meaningful reply.
> >
> >>
> >>  Anyways, as I just posted the problem seems to be around the
> >>   >>
> >>  I suppose since this is a local app, I don't need the  >>  tags, just looking for answers...
> >>
> >>  Thanks
> >>
> >>
> >>
> >>  On Mon, Sep 1, 2008 at 10:03 AM, P Kishor <[EMAIL PROTECTED]> wrote:
> >>  > On 9/1/08, Greg Morphis <[EMAIL PROTECTED]> wrote:
> >>  >> To further complicate things..  I removed all of the s
> >>  >>  and the code works.. I readd them and I get the same
> >>  >>
> >>  >>
> >>  >>  Error Executing Database Query.
> >>  >>  statement is not executing
> >>  >>
> >>  >>
> >>  >> The error occurred in
> >>  >>
>  C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\testCF\cfc\myApp\appDAO.cfc:
> >>  >>  line 92
> >>  >>
> >>  >>  The Stack Trace looks like :
> >>  >>
> >>  >>  java.sql.SQLException: statement is not executing
> >>  >> at org.sqlite.Stmt.checkOpen(Stmt.java:41)
> >>  >> at org.sqlite.PrepStmt.getUpdateCount(PrepStmt.java:86)
> >>  >> at
> coldfusion.server.j2ee.sql.JRunStatement.getUpdateCount(JRunStatement.java:277)
> >>  >> at coldfusion.sql.Executive.getRowSet(Executive.java:513)
> >>  >> at
> coldfusion.sql.Executive.executeQuery(Executive.java:1205)
> >>  >> at
> coldfusion.sql.Executive.executeQuery(Executive.java:1008)
> >>  >> at 

Re: [sqlite] If row exists use UPDATE else INSERT

[Javier Julio]

>
> Javier Julio <[EMAIL PROTECTED]> wrote:
>> Is it possible in SQLite to have a single statement that basically
>> says if this row exists run an UPDATE statement, if not run an  
>> INSERT?
>
> In some situations, depending on your definition of "exists", INSERT  
> OR
> REPLACE statement may be suitable:
>
> http://sqlite.org/lang_insert.html
> http://sqlite.org/lang_conflict.html
>
> Otherwise you will have to emulate it in your program code. Perform an
> UPDATE, use sqlite3_changes to see if any rows were actually  
> modified as
> a result, if not run INSERT.
>
>> I believe these are done in procedures which I know is not supported
>> in SQLite.
>
> But I'm pretty sure the language you write your host application in
> supports some concept of a subroutine.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Igor,

I have seen that you help out others often and am grateful you find  
the time.

I've looked into the INSERT OR REPLACE docs. It should have made sense  
to me before but was probably overwhelmed with it at first. I've dug  
through it again and am wondering then maybe if this will do it. I  
will have an id field that will always be unique. It won't be an auto  
increment field since I'll be using the id from the resultset coming  
from the server so I will manually insert that id. It's an auto  
incremented on the server's database so its always unique. So from the  
description:

"When a UNIQUE constraint violation occurs, the pre-existing rows that  
are causing the constraint violation are removed prior to inserting or  
updating the current row. Thus the insert or update always occurs."

Reading that carefully I figure that means if I have a record in the  
ideas table with an ideaId of 5 and I perform an INSERT with that same  
ideaId of 5 (remember I always provide the id as I want to use the id  
from the server) then it removes that row, and replaces it with the  
newly provided one? It seems I need to make sure I set a UNIQUE  
constraint on that ideaId field in my ideas table and not just set it  
as a PRIMARY KEY.

Does the INSERT OR REPLACE Internally figure out whether to do an  
INSERT or UPDATE? Maybe it does a delete and then an insert every time?

If that is correct then yes that is all I need. My only concern is  
maybe I don't want to do this for each row of data if its not  
necessary. For example, maybe that record has had no change based on a  
date field. Do you know if that INSERT OR REPLACE is an expensive  
operation? I probably should be cautious and not do that unless  
necessary. Since I'll be syncing the data one way, I probably should  
run a simple SELECT statement first to check the last modified date.  
Thanks Igor.

Ciao!
Javi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] If row exists use UPDATE else INSERT

[Javier Julio]

Hey everyone. Just signed up last night and had skimmed through  
several of the archives. Excited to partake in future discussions. I'm  
just taking the plunge into SQLite since I'm working with Adobe AIR  
and Flex. I'm building a desktop app that will sync data to a SQLite  
database. I have some experience with SQL Server and have been doing  
research on SQLite to find out what it supports and what it doesn't.

Is it possible in SQLite to have a single statement that basically  
says if this row exists run an UPDATE statement, if not run an INSERT?  
I believe these are done in procedures which I know is not supported  
in SQLite. I've seen a lot of discussions on triggers though and  
wonder if this is maybe something I should look into for what I want  
to achieve? Probably won't do the trick as it seems it runs after a  
data change/operation is made.

I'm just learning and trying to figure out the best way to use SQLite  
through AIR to sync data from the server to the desktop as its only  
one way sync. Trying to make sure I get the best performance. If I can  
cut down on the statements I need to use figure it would help. But for  
now I have a feeling I need to do a SELECT statement to see if that  
row exists and based on that result perform an INSERT or UPDATE. I  
would repeat that for every result from the server.

Ciao!
Javi
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users