Export to DB PostgreSQL

Forum Home Forums Development and Integration Export to DB PostgreSQL

Tagged: 

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #8833
    OtavioF
    Participant

    Hi!

    I’m exporting the data acquired from a GPS to a PostgreSQL database. On the SQL command window to export the current data, I’m using the following code:

    INSERT INTO geodata_temp (datetime, lat, lon, batt, temperature, dist,vel,sat,prec)
    VALUES(
    	@datetime,
    	case when @cnlNum = 40001 then @val/1000 end,
    	case when @cnlNum = 40002 then @val/1000 end,
    	case when @cnlNum = 40003 then @val end,
    	case when @cnlNum = 40004 then @val end,
    	case when @cnlNum = 40005 then @val end,
    	case when @cnlNum = 40006 then @val end,
    	case when @cnlNum = 40007 then @val end,
    	case when @cnlNum = 40008 then @val end
    	);
    
    INSERT INTO geodata (datetime, lat, lon, batt, temperature, dist,vel,sat,prec)
    SELECT datetime as "time",
    	max(lat) as "lat",
    	max(lon) as "lon",
    	max(batt) as "batt",
    	max(temperature) as "temperature",
    	max(dist) as "dist",
    	max(vel) as "vel",
    	max(sat) as "sat",
    	max(prec) as "prec"
    FROM geodata_temp
    group by datetime
    order by datetime desc;
    
    truncate table geodata_temp;

    The first insert results on a diagonal database, like:

    time null null null null null null
    null lat null null null null null
    null null lon null null null null
    ...
    null null null null null null prec

    The second insert moves the data from the temporary database (geodata_temp), grouping and sorting the data like:
    time lat lon batt ... prec

    In the end, It truncates the temporary table.

    However, the final result is the same as the first insert result.
    I tested with the query tool and the result should be right, but when I run inside RapidScada, it doesn’t work properly.

    There is a limitation with the SQL command inside RapidScada and should I make this sorting on database side?

    Thank you!

    #8836
    Mikhail
    Moderator

    Hi,

    DB export module executes only 1 request per operation. If you need to execute many requests, wrap them by stored procedure on the DB side.

Viewing 2 posts - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.