how insert array field through jdbc ?

[ggcking]

I want to batch insert a array field into a table by java code; how to do this throgh jdbc ?

[titawork]

Hi, you can seach how to do this with oracle jdbc on the internet. omnisci jdbc is just used with the same method. You can learn how to use omnisci jdbc at https://docs.omnisci.com/latest/6_jdbc.html.

[cdessanti]

the method setArray on preparedStatement class isn't implemented yet, but as a workaround, I think you can convert your array into a string with Array.toString() and then use the setString method of preparedStatement (you could have some trouble using arrays of strings)

[ggcking]

@cdessanti my code as :

private static final String INSERT_LOG_SQL = "INSERT INTO SyncLog_Array VALUES (?, ?, ?, ?, ?)";

public void save(Flux targetLogData) { final Properties props = this.config.getWriterProperties(); final int batchSize = Integer.parseInt(props.getProperty(SplitterConfig.WRITER_BATCH_SIZE)); final long batchTime = Long.parseLong(props.getProperty(SplitterConfig.WRITER_BATCH_TIME));

    targetLogData.bufferTimeout(batchSize, Duration.ofMillis(batchTime)).subscribeOn(Schedulers.single())
            .subscribe(logDataBatch -> {
                logger.info("Save {} record(s) to target database.", logDataBatch.size());
                try {
                    final Connection conn = this.connManager.getTargetConnection();
                    final PreparedStatement statement = conn.prepareStatement(INSERT_LOG_SQL);
                    for (final TargetLogData logData : logDataBatch) {
                        int index = 1; // 便于调整参数顺序。
                        statement.setLong(index++, logData.getMessageId());
                        statement.setString(index++, logData.getOperation());
                        statement.setString(index++, logData.getTableName());
                        statement.setString(index++, Arrays.toString(logData.getFieldNames()));
                        statement.setString(index++, Arrays.toString(logData.getFieldValues()));
                        // statement.setString(index++, String.join(",", logData.getFieldNames()));
                        // statement.setString(index++, String.join(",", logData.getFieldValues()));
                        statement.addBatch();
                    }

                    statement.executeBatch();
                    logger.info("{} record(s) saved.", logDataBatch.size());
                } catch (final SQLException ex) {
                    logger.error(ex.toString());
                }
            });
}

---

the Function Save do not work, and no exception throw out.. what's the problem ?

[cdessanti]

I have no idea but I guess the problem is the the setString method that's escaping the single quotes and the square brackets that enclose the string representation of the array

I can't try right now, but I will try something in the afternoon. With the debugger, you can try what happens when you add the array to the batch. I think the string representing the array is something like this. "[''Field1'',''field2'']" but the syntax expected is "{'field1','field2}"

Could you try with a replace the square brackets with curly cracked and unescape the single quotes?

I know this looks as a bad hack, but it's just a workaround

Ottieni Outlook per Androidhttps://aka.ms/ghei36

---

From: ggcking notifications@github.com Sent: Thursday, August 15, 2019 10:55:25 AM To: omnisci/omniscidb omniscidb@noreply.github.com Cc: Candido Dessanti candido.dessanti@hotmail.it; Mention mention@noreply.github.com Subject: Re: [omnisci/omniscidb] how insert array field through jdbc ? (#376)

@cdessantihttps://github.com/cdessanti my code as :

private static final String INSERT_LOG_SQL = "INSERT INTO SyncLog_Array VALUES (?, ?, ?, ?, ?)";

public void save(Flux targetLogData) { final Properties props = this.config.getWriterProperties(); final int batchSize = Integer.parseInt(props.getProperty(SplitterConfig.WRITER_BATCH_SIZE)); final long batchTime = Long.parseLong(props.getProperty(SplitterConfig.WRITER_BATCH_TIME));

    targetLogData.bufferTimeout(batchSize, Duration.ofMillis(batchTime)).subscribeOn(Schedulers.single())

                    .subscribe(logDataBatch -> {

                            logger.info("Save {} record(s) to target database.", logDataBatch.size());

                            try {

                                    final Connection conn = this.connManager.getTargetConnection();

                                    final PreparedStatement statement = conn.prepareStatement(INSERT_LOG_SQL);

                                    for (final TargetLogData logData : logDataBatch) {

                                            int index = 1; // 便于调整参数顺序。

                                            statement.setLong(index++, logData.getMessageId());

                                            statement.setString(index++, logData.getOperation());

                                            statement.setString(index++, logData.getTableName());

                                            statement.setString(index++, Arrays.toString(logData.getFieldNames()));

                                            statement.setString(index++, Arrays.toString(logData.getFieldValues()));

                                            // statement.setString(index++, String.join(",", logData.getFieldNames()));

                                            // statement.setString(index++, String.join(",", logData.getFieldValues()));

                                            statement.addBatch();

                                    }

                                    statement.executeBatch();

                                    logger.info("{} record(s) saved.", logDataBatch.size());

                            } catch (final SQLException ex) {

                                    logger.error(ex.toString());

                            }

                    });

}

---

the Function Save do not work, and no exception throw out.. what's the problem ?

― You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/omnisci/omniscidb/issues/376?email_source=notifications&email_token=AHLFBFZQSUI2CDTISHYL3RLQEUKX3A5CNFSM4IL2XEF2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4LH4FA#issuecomment-521567764, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AHLFBFYWCN6PYF4VPI4YRJLQEUKX3ANCNFSM4IL2XEFQ.

[randyzwitch]

Thanks for the discussion everyone. There is a JDBC repository now, where discussion like this is more appropriate. I'll move the issue over there.

[cdessanti]

Hi @ggcking ,

I did implement the setArray method on jdbc driver.

I will post a link to a preview of the fix

[ggcking]

@cdessanti hi，i try the new jdbc driver, it works .thk.

[cdessanti]

Could you reopen the issue? I need it open to do a pr. Thanks in advance

Ottieni Outlook per Androidhttps://aka.ms/ghei36

---

From: ggcking notifications@github.com Sent: Friday, August 16, 2019 8:29:33 AM To: omnisci/omnisci-jdbc omnisci-jdbc@noreply.github.com Cc: Candido Dessanti candido.dessanti@hotmail.it; Mention mention@noreply.github.com Subject: Re: [omnisci/omnisci-jdbc] how insert array field through jdbc ? (#20)

Closed #20https://github.com/omnisci/omnisci-jdbc/issues/20.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/omnisci/omnisci-jdbc/issues/20?email_source=notifications&email_token=AHLFBF2OFYB64HTA52FEDOTQEZCM3A5CNFSM4IL6CG42YY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOTCWFF2Q#event-2561430250, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AHLFBF3A33CYBOFQE6T57VLQEZCM3ANCNFSM4IL6CG4Q.

[ggcking]

@cdessanti If it have a new update, please let me know, thanks.

[cdessanti]

It's taking a little longer than expected because I am implementing getArray method. If you have issues with the setArray and bulk inserts let me know (it works for monodimensional arrays only)

Ottieni Outlook per Androidhttps://aka.ms/ghei36

---

From: ggcking notifications@github.com Sent: Monday, August 19, 2019 3:45:54 AM To: omnisci/omnisci-jdbc omnisci-jdbc@noreply.github.com Cc: Candido Dessanti candido.dessanti@hotmail.it; Mention mention@noreply.github.com Subject: Re: [omnisci/omnisci-jdbc] how insert array field through jdbc ? (#20)

@cdessantihttps://github.com/cdessanti If it have a new update, please let me know, thanks.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/omnisci/omnisci-jdbc/issues/20?email_source=notifications&email_token=AHLFBF5ZFHAWCHVNWWNFLPLQFH3NFA5CNFSM4IL6CG42YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD4ROYEA#issuecomment-522382352, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AHLFBF6NEHHHWBLROWZPSSTQFH3NFANCNFSM4IL6CG4Q.

[Smyatkin-Maxim]

It has been fixed with 2cff14500ac3e1c2d15dda57780630b0fe0a1b8c , which adds arrays support into the driver