destructive "SELECT" query for SQL?

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

destructive "SELECT" query for SQL?

Dr. Hawkins
I have finally figured out how to make my read/write to the remote server
UNION compatible for my various tables.  And it was so obvious that I feel
silly.

Anyway, after handing over a block of UPDATE followed by a SELECT with
UNION, I'll still have a bit of cruft in a particular column.  Ideally, I'd
like to attach something to my SELECT that sets that column to NULL as a
side effect.  It would be easy enough to do as an UPDATE at the end of the
block, but then I lose the results of my SELECTs.  It could also wait for
the next transaction without great harm.

But is there a way to do a SELECT sot that it wipes a field?
INSERT/RETURNING doesn't seem to be compatible with having a subsequent
SELECT (if I could, I could return the original argument as a string while
setting the field to NULL).

In any event, this sure beats what I had before  . . .

--
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: destructive "SELECT" query for SQL?

Peter Haworth
On Sun, Oct 6, 2013 at 8:38 AM, Dr. Hawkins <[hidden email]> wrote:

> But is there a way to do a SELECT sot that it wipes a field?
> INSERT/RETURNING doesn't seem to be compatible with having a subsequent
> SELECT (if I could, I could return the original argument as a string while
> setting the field to NULL).
>

Not sure I fully understand but you can use the literal NULL in a SELECT
statement, eg SELECT col1,col2,NULL FROM.....

Pete
lcSQL Software <http://www.lcsql.com>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: destructive "SELECT" query for SQL?

Dr. Hawkins
On Sun, Oct 6, 2013 at 10:39 AM, Peter Haworth <[hidden email]> wrote:

> Not sure I fully understand but you can use the literal NULL in a SELECT
> statement, eg SELECT col1,col2,NULL FROM.....
>

I create a record in a sqlite :memory:, and give it a tentative id (a
negative number).  It is conceivable that a couple of these accumulate
before synchronizing with the remote/master db.

I insert this information into the master, which causes a serial number to
be assigned on the primary index.

As part of the same transaction, I have a select which pulls this id back.
I use a field not used for this type of data to store a unique identifier
for the running program and the temporary id, allowing me to map the
universal uniq id to the in-memory database.

After the SELECT that brings this back, I would like that field to be
emptied in the postgres database.

At the moment, on the readback, I just leave an in-memory marker so that it
will wipe the field next time there is a a contact with the remote db, but
it would be nice to get it from the same transaction.


The funky INSERT I was thinking about would happen as something like

   INSERT INTO masterDb (myData,)  VALUES ("abc123") RETURNING
(serialNumber, -7)

where -7 was my temporary tag.

The problem is that if I follow this with a SELECT, even if UNION
compatible, I don't seem to get the return value.

(again, for those joining late, the lag of the remote transaction is
presumed to be the "expensive" part of the transaction)
--
Dr. Richard E. Hawkins, Esq.
(702) 508-8462
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: destructive "SELECT" query for SQL?

Ken Ray
Can't you just do an UPDATE query right after your SELECT and clear the column?

Ken Ray
Sons of Thunder Software

> On Oct 6, 2013, at 5:05 PM, "Dr. Hawkins" <[hidden email]> wrote:
>
>> On Sun, Oct 6, 2013 at 10:39 AM, Peter Haworth <[hidden email]> wrote:
>>
>> Not sure I fully understand but you can use the literal NULL in a SELECT
>> statement, eg SELECT col1,col2,NULL FROM.....
>
> I create a record in a sqlite :memory:, and give it a tentative id (a
> negative number).  It is conceivable that a couple of these accumulate
> before synchronizing with the remote/master db.
>
> I insert this information into the master, which causes a serial number to
> be assigned on the primary index.
>
> As part of the same transaction, I have a select which pulls this id back.
> I use a field not used for this type of data to store a unique identifier
> for the running program and the temporary id, allowing me to map the
> universal uniq id to the in-memory database.
>
> After the SELECT that brings this back, I would like that field to be
> emptied in the postgres database.
>
> At the moment, on the readback, I just leave an in-memory marker so that it
> will wipe the field next time there is a a contact with the remote db, but
> it would be nice to get it from the same transaction.
>
>
> The funky INSERT I was thinking about would happen as something like
>
>   INSERT INTO masterDb (myData,)  VALUES ("abc123") RETURNING
> (serialNumber, -7)
>
> where -7 was my temporary tag.
>
> The problem is that if I follow this with a SELECT, even if UNION
> compatible, I don't seem to get the return value.
>
> (again, for those joining late, the lag of the remote transaction is
> presumed to be the "expensive" part of the transaction)
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode