#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Posts
    3
    Rep Power
    0

    PostgreSQL SQL App suggestion


    I am trying to build a small PostgreSQL app that will read some array values from a C api and transmit them to a network database. The issues is that the application development environment for the app has to support the ability to insert PostgreSQL arrays. The data coming from the api is very fast so I can't do very much local processing. All the app has to do is table inserts and a few selects to get table references. All of the other processes are done on the other end. his app is simply a data collector.

    The trouble is I am not familiar enough with gui app builders in windows to know which ones support array inserts in PostgreSQL. I would use Java but it is too slow. I tried Qt, but it's sql doesn't seem to support array insert. Any help would be appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    Originally Posted by sting98005
    I would use Java but it is too slow.
    Why do you think that? Did you run benchmarks?
    I'm pretty sure your insert speed will not be limited by your programming language.

    Also: you can use string literals in any programming language:
    Code:
    insert into some_table (id, some_array) 
    values 
    (1, '{1,2,3}'::int[]);
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Posts
    3
    Rep Power
    0
    In java I can hardly keep up with the data source. Just reading the data causes the data source to backup and eventually stop.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    Originally Posted by sting98005
    In java I can hardly keep up with the data source. Just reading the data causes the data source to backup and eventually stop.
    This is hard to believe given the fact that there are web servers implemented in Java that can process thousands of concurrent requests per second without problems.
    I can do about 10000 inserts per second through a Java program, a lot more when using JDBC batched statements. But this is getting off-topic now

    But as I showed you: your programming language doesn't need to support arrays, you can use plain String literals to insert arrays.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2015
    Posts
    3
    Rep Power
    0
    I really do appreciate your response. The data is delivered at 50,000 samples per second. It is a real time data acquisition device.

    I did follow your suggestion and can generate the sql to insert the array data. The code looks like this, I trust that is what you meant:

    Code:
        QString str = "INSERT INTO CcmBlock(ccmFrameId, block)"
                             " Values((SELECT ccmFrameId FROM CcmFrame WHERE ccmSampleId = ? AND frameIndex = ?),"
                                            "'{";
    
        // Add the array elements
        for (int i = 0; i < size; i++){
            str += QString::number(data[i]);
            if (i < 4) str += ", ";
        }
         str += "}'::int[])";
    
        theQuery.prepare(str);
        theQuery.bindValue(0, sampleId);
        theQuery.bindValue(1, frameIndex);
        theQuery.exec();

IMN logo majestic logo threadwatch logo seochat tools logo