B4J Question java.sql.SQLException: Invalid object name 'sqlite_master'.

[RobertNM]

I am trying to connect to a SQL database named TaskMgr

I added the mtlmauth.dll to the Java jdk1.8.0_4 - this fixed one error

I have added the jar
#AdditionalJar: jtds-1.3.1.jar

I am using the statement:
SQL1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://localhost")

I am getting the error:
java.sql.SQLException: Invalid object name 'sqlite_master'.

The database is in SQL Server not sqlite.

I have tried different combinations of Initialize and Initialize2 with no success with and without the database name and username and password. Some say too many parameters and don't work at all.

Any help would be appreciated

Thank you

Robert

 

[Erel]

I added the mtlmauth.dll to the Java jdk1.8.0_4 - this fixed one error

Never heard of this.

Can you post the full error message from the logs?

 

[keirS]

Are you trying to connect using windows authentication?

 

[RobertNM]

Sorry, that was a typo. The dll for the SSO is ntlmauth.dll, the documentation says that this required for Windows Authentication.
This dll was part of the jtds-1.3.1-dist when I unzipped the package.
I have added it to the C:\Program Files\java\bin\jdk1.8.0-40 and that fixed one problem.

Here is the log:

B4JHowTo WebApp Example - Task Manager v20140516
2017-02-16 08:17:50:AppStart :: Start
2017-02-16 08:17:51:CreateDBTables :: Start
main._createdbtables (java line: 94)
java.sql.SQLException: Invalid object name 'sqlite_master'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:290)
at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult2(SQL.java:371)
at anywheresoftware.b4j.objects.SQL.ExecQuerySingleResult(SQL.java:360)
at b4j.example.main._createdbtables(main.java:94)
at b4j.example.main._appstart(main.java:68)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:29)

It appears that the program is trying to connect to SQLite_Master and I am trying to connect to MS SQL Server.

Public SQL1 AS SQL

I am using the statement:
SQL1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://localhost")

I also tried this with port address
SQL1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://localhost:1433")

I tried setting the default database for the user to Taskmgr (the database that I am using) and still no luck.

I can connect to the database using MS SQL Management Studio and query the database and everything works fine.
I just can not figure out how to make the connection to SQL and not SQLite.

Thank you for your assistance.

Robert

 

[keirS]

Can you post the code for the createdbtables sub?

 

[keirS]

I think I have worked it out. You are using code from this tutorial?

rivate Sub CreateDBTables
    AppLog("CreateDBTables :: Start")
    If SQLite.ExecQuerySingleResult( _
        "SELECT count(name) FROM sqlite_master WHERE type='table' AND name='Tasks'") = 0 Then
            SQLite.ExecNonQuery("PRAGMA journal_mode = wal") 'best mode for multithreaded apps.
            ' Create the table Tasks if it doesn't already exist
            Dim columns As Map
            columns.Initialize
            columns.Put("ID", DBUtils.DB_INTEGER)
            columns.Put("Task", DBUtils.DB_TEXT)
            columns.Put("Description", DBUtils.DB_TEXT)
            columns.Put("Date", DBUtils.DB_TEXT)
            AppLog("Table Tasks created.")
            DBUtils.CreateTable(SQLite, "Tasks", columns, "ID")
            ' fill the table with dummy data
            Dim ListOfMaps As List
            ListOfMaps.Initialize
            Dim id As Int
            For i = 1 To 5
                Dim m As Map
                m.Initialize
                id = Rnd(id + 1, id + 10000)
                m.Put("Task", "Task " & id)
                m.Put("Description", "Description" & i)
                m.Put("Date", DateTime.Date(DateTime.Now))
                ListOfMaps.Add(m)
            Next
            DBUtils.InsertMaps(SQLite, "Tasks", ListOfMaps)
            AppLog("Table Tasks test data inserted.")
    End If
    AppLog("CreateDBTables :: End")
End Sub

I think your connection works fine. The code checking if the table exists is SQLite specific.

For SQL server the SELECT would be

SELECT COUNT(TABLE_NAME) FROM  TaskMgr.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE' AND TABLE_NAME = 'Tasks'

 

[RobertNM]

keirS,

Thank you for you quick response to my problem.

I am converting a sample program that was posted by another user. He is using SQLite for his example and I wanted to
convert the program to run on a SQL Server. I went through the program and changed everything (I thought). I must have
gone through this code 20 times and I missed this one. Duh!

"SELECT count(name) FROM sqlite_master WHERE type='table' AND name='Tasks'")

As soon as you posted your reply I looked in this section of code and there it was big as life. Success!

Thank you very much for your help.

Robert