[ic] sql insert/update with interchange?

[Mike Heins]

Quoting T. Stoffels (tstoffels at gmail.com):
> Hello again.
> 
> I noticed it's possible to write to my database from within an
> interchange page (to save values passed by a form for example) by
> 'abusing' the query tag:
> --
> [query sql=|insert into mytable (myvalue) values(13) | ]  [/query]
> --
> 
> It works, no doubt about that, but I can't imagine it was intended to
> be that way.
> The query.../query tag seems to return '1' btw, (which appears as
> output on my page)
> 
> So, is there some special interchange tag for writing to a database?
> Or maybe interchange provides a ready-to-use DBI-handle which can be
> accessed from within a [perl]...[/perl ] statement?

You can write from query just fine if you allow it. It is operating
as intended.

The question is how would the query get into your sql="" parameter?

If you want to define the table as read-only, you can do in the database
configuration file:

        Database mytable WRITE_CONTROL 1
        Database mytable READ_ONLY     1

If you want to gate the writes with the [flag type=write table=mytable],
which will allow the Admin UI to work with the table:

        Database mytable WRITE_CONTROL 1
        Database mytable WRITE_TAGGED  1

WRT writing the database from perl, there are many ways. A number
of examples can be found in the admin UI pages. You might look
at pages/member/process_return.html for an embedded perl example.
They show something like:

	[perl tables=mytable]

	    my $db = $Db{mytable};  # get the object from pre-built
				    # done with tables=mytable

	    $db->set_slice('a_code',
				{
					column1 => $value1,
					column2 => $value2,
				},
			   );
	[/perl]

Note that you have to flag it for write before opening the table if
you want to use WRITE_CONTROL.

The little-known [record ...] tag can do it too:

    [record
	table=products
	key=foo
	column.price=10
	column.prod_group="Misc."
	column.category="Junk"
	column.description="What have you"
	]


-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.765.647.1295  tollfree 800-949-1889 <mike at perusion.com>

There's nothing sweeter than life nor more precious than time.
-- Barney