How to have oracle database accessible remotely

Question

So I have an app that I input data in to an sqlite database, it gets that data from a text file dragged in to the documents directory. The contents look like:

file1.pdf, A1, A3, folderName1, 
file2.pdf, A4, A75, folderName2, 

I have tested it with 10 strings of text. It worked and inserted fine so I decided to create a test that would be a bit more. (357) This would not be the maximum it would be used with but a big enough sample for now.

I ran this and at around 240 ish the insert query changes to Error preparing. And then the following ones start with Error: cannot open database. - the error message I created for sqlite3_open != SQLITE_OK.

2016-07-12 09:49:10.289 AppName[897:491157] /       /
2016-07-12 09:49:10.290 AppName[897:491157] INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file1.pdf', 'Statement', 'S460', 'S460' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file1.pdf')

2016-07-12 09:49:10.317 AppName[897:491157] /       /
2016-07-12 09:49:10.317 AppName[897:491157] INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file2.pdf', 'Important', 'S461', 'S461' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file2.pdf')

2016-07-12 09:49:10.341 AppName[897:491157] /       /
2016-07-12 09:49:10.342 AppName[897:491157] INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file3.pdf', 'Important', 'S462', 'S463' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file3.pdf')
2016-07-12 09:49:10.343 AppName[897:491157] Error running insert: INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file3.pdf', 'Important', 'S462', 'S463' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file3.pdf')

2016-07-12 09:49:10.343 AppName[897:491157] /       /
2016-07-12 09:49:10.344 AppName[897:491157] Error: cannot open database
2016-07-12 09:49:10.344 AppName[897:491157] INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file4.pdf', 'Important', 'S464', 'S464' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file4.pdf')
2016-07-12 09:49:10.344 AppName[897:491157] Error preparing INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT 'file4.pdf', 'Important', 'S464', 'S464' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = 'file4.pdf')

Above is a snippet of my output showing my INSERT statement and the error messages that appear. As you can see the first two went in as normal. Then the 3rd file had an issue with running the INSERT statement. Then from then on the rest of the files cant open the database which I assume is why it can't prepare the statement.

What I have tried

I assumed that the reason it was doing it was because a certain line was throwing it off. - I cut the lower half of the text file and pasted it to the top. The first lot went in fine so this was not the reason.

I have noticed that there is a command called transaction on sqlite for android that is used for multiple insert queries. Do I need to do something similar to that for iOS?

-(void) addFileName:(NSString *)fileName
                        fileType:(NSString *)fileType
                        refNumber:(NSString *)bates
                           fileLocation:(NSString *)location
                 endrefNumber:(NSString *)endBates
{
    long long fileID;
    NSLog(@"/       /");
    [self openDB];

    // Names Table
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO Files (fileName, folder, refNum, endrefNum) SELECT '%@', '%@', '%@', '%@' WHERE NOT EXISTS(SELECT fileName FROM Files WHERE fileName = '%@')", fileName, fileType, bates, endBates, fileName];
    NSLog(@"%@",insertSQL);
    const char* query1 = [insertSQL UTF8String];
    sqlite3_stmt * query1_stmt = NULL;
    if (sqlite3_prepare_v2(database, query1, -1, &query1_stmt, NULL) != SQLITE_OK){
        NSLog(@"Error preparing %@", insertSQL);
        [self closeDB];
        return;
    }

    if (sqlite3_step(query1_stmt) != SQLITE_DONE) {
        NSLog(@"Error running insert: %s", query1);
        [self closeDB];
        return;
    }
    // row id
    fileID = sqlite3_last_insert_rowid(database);

    // phones table
    insertSQL = [NSString stringWithFormat:@"INSERT INTO Location (FileID, Location) VALUES (%lld, '%@')", fileID, location];
    const char* query2 = [insertSQL UTF8String];
    sqlite3_stmt * query2_stmt = NULL;
    if (sqlite3_prepare_v2(database, query2, -1, &query2_stmt, NULL) != SQLITE_OK){
        NSLog(@"Error preparing %@", insertSQL);
        [self closeDB];
        return;
    }

    if (sqlite3_step(query2_stmt) != SQLITE_DONE) {
        NSLog(@"Error running insert: %s", query2);
        [self closeDB];
        return;
    }

    [self closeDB];
}

Open Database

-(void) openDB
{
    if ( (sqlite3_open([[self dbDocPath] UTF8String], &database)) != SQLITE_OK )
        NSLog(@"Error: cannot open database");
}

Close Database

-(void) closeDB
{
    sqlite3_close(database);
}

UPDATE

So I have made it so that the database opens once, inserts all of the data, then closes once it has done. This seems that the opening and closing of the database is what caused the issues. Still seems a bit strange to me, if anyone could explain why that would be helpful.

Solution 1

You need to enable the instance listener to be listening on an IP address, not just localhost. Go to the Net Services Administration page on the Oracle Enterprise Manager web interface. Edit the listener (LISTENER_localhost) and add a listener and select to use the IP address of the instance. Keep port 1521 as per default. You will be asked if you want to restart the service. Please do so. Then you should be able to connect from your host (or any other machine for that matter).

Solution 2

You have to make sure that the listener is using 0.0.0.0:1521. You can check this on Windows with: netstat -an -p tcp |find ":1521"

Comments

[Wesley]

See if there is anything listening on the standard Oracle Database port: sudo netstat --tcp --udp --listening --program | grep 1521

[CL.]

A single transaction for all inserts would be needed for peformance. But your problem is that it does not work at all, because of a bug in your code (which you have not shown).

[Rowan Knight]

Added code, what I am trying to do now is open the connection, read and insert and then close the connection rather than opening and closing for each insert statement.

[Droppy]

Why do you open/close the database for every query? Open it and keep it open and only close it when the app goes into the background. Only open it if it's closed and only close it if it's open (this requires guard code). Also opening the database can fail, so you need a return value and you need to check it.

[CL.]

You forgot sqlite3_finalize().

[Droppy]

@kni9ht Yeah drop your call to [self closeDB] and put those into the app delegate methods that relate to the app going into the background etc. Just always put a check for [self openDB] at the start of all database-related methods.

[digz6666]

Its listening on port 1521, 0.0.0.0:1521 but when I connect it says: An error occurred while establishing the connection: Long Message: The Network Adapter could not establish the connection Details: Type: java.sql.SQLException Error Code: 20 SQL State: 61000

[Thomas Uhrig]

Thanks! I tried to edit the listener.ora by hand and it was terrible. But the Net Manager was quite easy. However, I used the Windows program, not the web application since I didn't know the password for it (as I'm writing this, it sounds like a joke, but I'm serious...).

[Rowan Knight]

@Droppy Yeah, that was it. Just updated the post, so I assume opening and closing were corrupting the file? This has obviously made the process faster as well.