#1  
Old 18th April 2004, 03:42 PM
Paul Fraser
 
Posts: n/a
Default Insert Record without Dataset Overhead

I have searched google groups, this news group etc. and found many
discussions about using TnxQuery (TQuery) for inserting a single record into
a table without loading the full table with 'SELECT * from TABLENAME' with
the consequent overhead.

Suggestions have been made to use 'SELECT * from TABLENAME WHERE ID = -1'
as the sql statement, which is not available at present in NexusDB.

Blob literals are not (in my opinion) the answer to using sql for blob
handling. We need a clean method of inserting records with blob fields using
the navigation methods of Tnx Query (edit, insert, update, post) , or
perhaps using a lower level approach.

Can anyone offer insight into a solution to this problem?

Perhaps a guide to a low level approach bypassing the Dataset altogether may
be possible with Nexus.

I am talking about a remote client with the need, of course, to reduce
network traffic.

Thx
Paul Fraser






  #2  
Old 18th April 2004, 04:33 PM
Lauchlan M
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

> Suggestions have been made to use 'SELECT * from TABLENAME WHERE ID = -1'
> as the sql statement, which is not available at present in NexusDB.


I don't understand the problem, but why can't you do

'SELECT * from TABLENAME WHERE ID = -1'

in NexusDB, or better yet

'SELECT * from TABLENAME WHERE 1=0'

?

Lauchlan M


  #3  
Old 18th April 2004, 05:06 PM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

> I have searched google groups, this news group etc. and found many
> discussions about using TnxQuery (TQuery) for inserting a single
> record into a table without loading the full table with 'SELECT *
> from TABLENAME' with the consequent overhead.


It appears that we are talking about Live (editable) resultsets here. There
is no overhead here, a live resultset just opens a table cursor on the
server side. There is no copying of records or such done when opening a live
resultset.

> Blob literals are not (in my opinion) the answer to using sql for blob
> handling. We need a clean method of inserting records with blob
> fields using the navigation methods of Tnx Query (edit, insert,
> update, post) , or perhaps using a lower level approach.


Navigational methods are Next, Prev etc.

If you really want to use Edit, Post etc, why dont you just use TnxTable
instead of TnxQuery?

> Perhaps a guide to a low level approach bypassing the Dataset
> altogether may be possible with Nexus.


Before we spend a few months adding that, could you expand the description
on the problems with the way it currently works?


--

Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


  #4  
Old 18th April 2004, 05:25 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

> Can anyone offer insight into a solution to this problem?
TnxTable.

> Perhaps a guide to a low level approach bypassing the Dataset
> altogether may be possible with Nexus.

If you feel adventureous you can directly use the TnxAbstractCursor class
(nxsdServerEngine.pas). TnxDataset is a wrapper around that. Doing so is not
and will not be supported directly by us.

> I am talking about a remote client with the need, of course, to reduce
> network traffic.

To reduce network traffic when inserting from a remote client you should
take a look at TnxTable / BatchAppend.

--
Thorsten Engler [NexusDB Architect]


  #5  
Old 18th April 2004, 10:51 PM
Paul Fraser
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

Hi Lauchlan,

"Lauchlan M" <LMackinnon@Hotmail.com> wrote in message
news:4082076c@wic040d....
> > Suggestions have been made to use 'SELECT * from TABLENAME WHERE ID

= -1'
> > as the sql statement, which is not available at present in NexusDB.

>
> I don't understand the problem, but why can't you do


The problem is that to insert a record in a table using a TnxQuery
(qry.insert) requires RequestLive := true,
otherwise the dataset is read only.

>
> 'SELECT * from TABLENAME WHERE ID = -1'


This does not work, only 'SELECT * from TABLENAME ' works with live datasets
at present in NexusDB.

Paul Fraser


  #6  
Old 18th April 2004, 11:22 PM
Paul Fraser
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

Thanks for your reply, Eivind.

> > I have searched google groups, this news group etc. and found many
> > discussions about using TnxQuery (TQuery) for inserting a single
> > record into a table without loading the full table with 'SELECT *
> > from TABLENAME' with the consequent overhead.

>
> It appears that we are talking about Live (editable) resultsets here.

There
> is no overhead here, a live resultset just opens a table cursor on the
> server side. There is no copying of records or such done when opening a

live
> resultset.
>


What is the effect of the sql property 'SELECT * from Tablename' in this
case?

Are no records copied into a result set on the server?

If a result set is created, could this be a problem with a very large table?

> Navigational methods are Next, Prev etc.
>

I plead brain fatigue here, of course, you are correct!

> If you really want to use Edit, Post etc, why dont you just use TnxTable
> instead of TnxQuery?
>

I am not fully aware of the situation with Nexus, but if TnxTable loads all
records into a result set of a large table on the server, just to add a
single record, it seems an unnecessary load on the server.

> > Perhaps a guide to a low level approach bypassing the Dataset
> > altogether may be possible with Nexus.

>
> Before we spend a few months adding that, could you expand the description
> on the problems with the way it currently works?
>

As discussed above, it does not seem to me that a single record can be added
to a large table without a large result set being created on the server.
This may not be the case with Nexus but it is the case with TTable, and
perhaps TQuery.

When processing blobs, I think that modifying records with insert, update
etc. is better than using sql with blob literals.

Paul Fraser


  #7  
Old 19th April 2004, 12:44 AM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

> What is the effect of the sql property 'SELECT * from Tablename' in
> this case?

It returns a direct cursor into the table. No records are copied anywhere.

> Are no records copied into a result set on the server?

Correct. Non. Nil. Keine.

> If a result set is created, could this be a problem with a very large
> table?

Read my lips N o t h i n g i s c o p i e d.

>> If you really want to use Edit, Post etc, why dont you just use
>> TnxTable instead of TnxQuery?


> I am not fully aware of the situation with Nexus, but if TnxTable
> loads all records into a result set of a large table on the server,
> just to add a single record, it seems an unnecessary load on the
> server.


TnxTable (and TnxQuery, as both are derived from TnxDataSet) are wrappers
around the low-level cursor class TnxAbstractCursor (actually it will be a
TnxRemoteCursor or TnxServerCursor depending if you use a
TnxRemoteServerEngine or TnxServerEngine). In case of a TnxTable or a
TnxQuery with a live result this cursor directly represents the table on
disk, no records are copied into an in-memory resultset. Records are read
from disk (or out of the block cache if already present there) as required
while you navigate the dataset. In case of a non-live select query this
cursor belongs to a temporary in-memory table that was created by the SQL
engine to contain the result set.

> As discussed above, it does not seem to me that a single record can
> be added to a large table without a large result set being created on
> the server. This may not be the case with Nexus but it is the case
> with TTable, and perhaps TQuery.

And once more no "result set" of any form, type, flavour, color or
fragrance is created if you use a TnxTable.

> When processing blobs, I think that modifying records with insert,
> update etc. is better than using sql with blob literals.

TnxTable is your friend.

--
Thorsten Engler [NexusDB Architect]


  #8  
Old 19th April 2004, 01:07 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

> When processing blobs, I think that modifying records with insert,
> update etc. is better than using sql with blob literals.


You might also use parameters in SQL with ftBlob as parameter type, which
passes the blob data as a bytearray (i.e. no need to expand the size by text
encoding).

--

Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com


  #9  
Old 19th April 2004, 02:31 AM
David Marcus [NDX]
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

Thorsten Engler [NDA] wrote:
> TnxTable is your friend.


LOL!

--
David Marcus [NexusDB Expert]
  #10  
Old 19th April 2004, 09:10 AM
Paul Fraser
 
Posts: n/a
Default Re: Insert Record without Dataset Overhead

Thanks Thorsten,

This is all very good news and it clears up a lot of misconceptions.

Nexus is indeed a great product..

When the time is available, I think it would be a great idea to include this
information in the docs..

Paul Fraser

"Thorsten Engler [NDA]" <thorsten.englerNO@SPAMnexusdb.com> wrote in message
news:4082772a$1@wic040d....
> > What is the effect of the sql property 'SELECT * from Tablename' in
> > this case?

> It returns a direct cursor into the table. No records are copied anywhere.
>
> > Are no records copied into a result set on the server?

> Correct. Non. Nil. Keine.
>
> > If a result set is created, could this be a problem with a very large
> > table?

> Read my lips N o t h i n g i s c o p i e d.
>
> >> If you really want to use Edit, Post etc, why dont you just use
> >> TnxTable instead of TnxQuery?

>
> > I am not fully aware of the situation with Nexus, but if TnxTable
> > loads all records into a result set of a large table on the server,
> > just to add a single record, it seems an unnecessary load on the
> > server.

>
> TnxTable (and TnxQuery, as both are derived from TnxDataSet) are wrappers
> around the low-level cursor class TnxAbstractCursor (actually it will be a
> TnxRemoteCursor or TnxServerCursor depending if you use a
> TnxRemoteServerEngine or TnxServerEngine). In case of a TnxTable or a
> TnxQuery with a live result this cursor directly represents the table on
> disk, no records are copied into an in-memory resultset. Records are read
> from disk (or out of the block cache if already present there) as required
> while you navigate the dataset. In case of a non-live select query this
> cursor belongs to a temporary in-memory table that was created by the SQL
> engine to contain the result set.
>
> > As discussed above, it does not seem to me that a single record can
> > be added to a large table without a large result set being created on
> > the server. This may not be the case with Nexus but it is the case
> > with TTable, and perhaps TQuery.

> And once more no "result set" of any form, type, flavour, color or
> fragrance is created if you use a TnxTable.
>
> > When processing blobs, I think that modifying records with insert,
> > update etc. is better than using sql with blob literals.

> TnxTable is your friend.
>
> --
> Thorsten Engler [NexusDB Architect]
>
>





Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Triggers overhead Paul Toms nexusdb.public.support.sql 3 16th June 2010 05:39 AM
NX1 :SQL for get a record using the position in the returned dataset Roberto Nicchi nexusdb.public.support.sql 13 8th April 2008 09:51 PM
Again: Insert, Add Record Fails via VB6 Gary Naughton nexusdb.public.support.odbc 3 2nd October 2007 10:18 PM
Re:Again: Insert, Add Record Fails via VB6 Gary Naughton nexusdb.public.support.odbc 0 26th September 2007 07:11 PM
V1.07 Insert & Add Record Failure Gary Naughton nexusdb.public.support.odbc 0 15th September 2007 09:32 PM


All times are GMT +11. The time now is 09:56 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.