D3 SQL: create table log (id timestamp primary key default NOW() )?

[Nikolai Lukin]

Hi all!

I have a third party package, which generates SQL "insert" queries to any
available ODBC data source. I need to setup D3 file as an SQL table with a
capability to auto generate a unique item-id (primary key), being preferably
a timestamp. That's due to that insert statement doesn't provide a unique
data to be used as an id.

I discovered that neither "create table log (id timestamp primary key
dafault NOW() )" nor "alter table log alter id default NOW()" constructs,
pretty typical for any RDBMS, work. D3SQL/ODBC manual couldn't shed any
light.

I attempted to use D3 callx trigger to generate new item-id and then delete
an item inserted with null item-id. But I couldn't do that because of
impossibility to unlock an item being inserted by SQL query via ODBC from my
subroutine envoked via callx.

Is it possible to work around that?

TIA
Nick

[Mark Brown]

Nick, tovarisch, always good to hear from you.

Do you have any control over the insert query at all? I've used things like

INSERT INTO tablename (ITEMID, FLD1, FLD2, FLDN) VALUES(NOW(),
"F1","F2","F3")

If you have NO control over the query, do you have any control over the
TABLE you are inserting into. The other trick I used connecting D3 to UPS
software, was have it insert into a "temp file" with a file trigger. That
trigger can write to any file on your system with any item ID you wish.

sub temp.updt(item)
open 'path.to.real.file' then write access(3) on date():"*":time()
END

Notice the END at the end instead of return. If you return, the update
process (which isn't complete) will continue. But if you END, it will
terminate the update process and it won't write anything to the temp file.
Put in the RETURN if you want a transaction log/trace.

Mark
--
-------------------------------
Mark Brown
President/CEO
Computer Mgmt & Resource Consultants
714/537-4437
484/716-6154 (Cell)
413/487-7925 (Fax)

Illegitimis non carborundum.

"Nikolai Lukin" <nvl...@rol.ru> wrote in message
news:b7m4t8$k5s$1...@n6.co.ru...

[Nikolai Lukin]

Mark,

Great to hear from you too!

> Do you have any control over the insert query at all? I've used things
like
>
> INSERT INTO tablename (ITEMID, FLD1, FLD2, FLDN) VALUES(NOW(),
> "F1","F2","F3")

No, I haven't.

> If you have NO control over the query, do you have any control over the
> TABLE you are inserting into. The other trick I used connecting D3 to UPS
> software, was have it insert into a "temp file" with a file trigger. That
> trigger can write to any file on your system with any item ID you wish.
>
> sub temp.updt(item)
> open 'path.to.real.file' then write access(3) on date():"*":time()
> END

Yes I have complete control over the server side things. I saw your comments
about UPS stuff and tryed to do a trick with temporary and real log file
with two triggers:

For temp file:

subroutine(item)
if access(12) = 1 then return
open "log" to filevar else return
itemidnew = system(19)
write item on filevar,itemidnew
return

For real log file:

subroutine(item)
if access(12) = 1 then return
open "temp" to filevar else return
delete filevar,""
return

In the result I had both items in both files locked together with
corresponding groups. I can't undersytand why. My perception was, that item
in a temp file should be released as soon as new item is written in a log
file. Only then item deletion in a temp file should have started.

> Notice the END at the end instead of return. If you return, the update
> process (which isn't complete) will continue. But if you END, it will
> terminate the update process and it won't write anything to the temp file.
> Put in the RETURN if you want a transaction log/trace.

The idea to end the subroutine with an END statement rather than return
seems to be interesting. I need to take a little time tomorrow morning to
try this trick before I come back.

Cheers,
Nick

[Nikolai Lukin]

Mark,

>
> sub temp.updt(item)
> open 'path.to.real.file' then write access(3) on date():"*":time()
> END
>
> Notice the END at the end instead of return. If you return, the update
> process (which isn't complete) will continue. But if you END, it will
> terminate the update process and it won't write anything to the temp file.
> Put in the RETURN if you want a transaction log/trace.

END didn't work for me. I got "[SQL070] Unable to commit modifications due
to constraint failure" message on server and "Constraint failure" type
message on client.

I have D3 Linux 7.2.1 with all recent patches installed. For testing
purposesin addition to the package mentioned I use MSAccess and MSQuery.

Any more ideas?
Nick

[Mark Brown]

Can I see a screen dump of the D3ODBCSRV output? You may have to
stop/restart the server in a foreground window.

You can email me directly if you want. drw...@flash.net

Mark
======================
"Keep an open mind long enough and your brains will fall out."

"Nikolai Lukin" <nvl...@rol.ru> wrote in message

news:b7ob3r$sip$1...@news.rol.ru...

[Angelo Collazo]

Nick,
Where did you get the third party package, which generates SQL

"insert" queries to any available ODBC data source.

Angelo,

"Nikolai Lukin" <nvl...@rol.ru> wrote in message news:<b7m4t8$k5s$1...@n6.co.ru>...

[Nikolai Lukin]

Angelo,

A package mentioned is TMeter, a software for IP traffic accounting. It's
available at
http://www.tmeter.ru/tmeter/tmeter-345.exe

It's free for those who reads Russian. Its English version is shareware and
could be found at the mentioned site.

Good luck,
Nick

"Angelo Collazo" <acol...@tsbrass.com> ???????/???????? ? ????????
?????????: news:cfb4de4e.03042...@posting.google.com...