Using the Database Console, you can link a drawing through an SQL clause:

SELECT * FROM "public"."Locations" WHERE "City" = 'El Paso'

then, link the resulting table to a drawing.

in PostGIS, this is lightning fast. However, I will want the City to change frequently, and would like to pass the SQL query as part of the DataSource.ConnectionString. It doesn't appear to me that Manifold will allow this.

UI scripting will not work because I would eventually like to have this run as an IMS or ActiveX application. Does anyone know of a way to include the SQL string into a connection to a DataSource?

Hello.

I havent tryed this DataSource.ConnectionString or even now if it works but why dont you try to link to the whole table and doa script to link to it, maybe its much easier.

Regards

the dataset is really large, and PostGIS does a great job of doing selections. So, I want to keep it in PostGIS.

So the question is:

How do you push the value of parameter for a server sided parameter view from Manifold to execute in PostGIS? (SQL PREPARE ...)

OR:

How do you export a complete query text including the evaluated parameter to a temporary PostGIS view

not by programming pg client library functions (there are examples for this in C# and PHP ... in www) but through interfaces provided by Manifold currently or in futur.

actually, I would like to be able to insert code into the Database console like can can through the GUI (see attached). Perhaps extend the object model to include something like:

DataSource.LinkDrawing(String drawing, DataSourceColumnSet columns, SQLStatement)

or, as part of the methods of a linked drawing have:

Drawing.RefreshWithSQL(SQLStatement)

or, even make the Properties LinkSource editable, and allow there to be an SQL statement that can be included. So, a LinkSource might return:

host='localhost' user='postgres' password=*** dbname='postgis' definition='SELECT * FROM parcels WHERE value > 500000'

I'm hoping Adam might have a thought as to this request.

Attachments:
dc.jpg

As Manifold is not doing any checks on the SQL command that we enter in the command control of the database console I would think that it should be really low hanging fruit to add an entry to the function behind the corrosponding Run button in the object model. And more: It's completely independent of the DBMS on the other end of the connection. We simply would use the current predefined connection manually set in database console. So I would like to have 1st a programatical way to use the default connection of the database console and 2nd have a method to send any string to the remote DBMS though this connection or any other datasource object (and perhaps have errors reported). Following your examples it would read like

error_string = datasource.SendSQL(SQLStatement)

Appart from this feature requests you always can create a CROSS JOIN view in PostGIS and update a one-record-table part of this view with the criteria of the WHERE clause before you use a linked drawing - the traditional DB approach.

any hints on how to connect to PostGRESQL from VBScript or any other programming language? That is, something like:

PostGRESQLConnection = CreateObject("Postgres.Application")

I can do this with Oracle, but don't know how to do it with PostGRES.

t_crow gave an exaple in this thread.

thanks. That I can do. What I want to do is connect directly to PostGRES and issue the SQL View command that you mentioned. So, I want to use VBScript to connect directly into PostGRES (no Manifold), and update tables.

Art

I don't know if you are able to use .net, but I can imagine that this would be a whole lot easier than using vbscript.

Here is some info in connecting and issueing commands to postgres from a .NET app. Code in both c# and vb. You could do the same within a .net script in manifold (you would have to remove some of the visual studio code), making sure that you import all of the assembly's needed.

http://www.builderau.com.au/program/dotnet/soa/Integrate-PostgreSQL-with-NET/0,339028399,339267580,00.htm

Cheers

James K.

James,

thanks for the info. Yes, I do some .NET programming, but as an academic, alot of what I do is really quick illustrative things. That is, to quickly bang out VBScript to illustrate some kind of spatial task. Its easier for the students to understand (and even the CEO's that I deal with). Then, a programming team can do a real implementation.

But, the information you sent me looks helpful.

for call postgresql using the psycopg python interface

import psycopg

c.execute('SELECT  column1 from tablename  where column2=0  ORDER BY column1 ASC;')

nbr=c.rowcount

o=psycopg.connect('dbname=name user=username')

c=o.cursor()

for i in xrange(nbr):

    rows=c.fetchone()#b   

    column1=str(rows[0])