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

R

RobertNM

Member
Licensed User
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
 
Sort by date Sort by votes
R

RobertNM

Member
Licensed User
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
 
Upvote 0
K

keirS

Well-Known Member
Licensed User
Longtime User
I think I have worked it out. You are using code from this tutorial?

B4X: 
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

B4X: 
SELECT COUNT(TABLE_NAME) FROM  TaskMgr.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE' AND TABLE_NAME = 'Tasks'
 
Upvote 0
R

RobertNM

Member
Licensed User
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

J
  • Question
B4J Question (SQLException) java.sql.SQLException: invalid database address in packet, not in debug
Replies
3
Views
894
jopevazquez
J
  • Question
B4J Question B4J - ERRORE ) io non lo vedo)
Replies
6
Views
778
Erel
L
  • Question
B4J Question sqlite missuse - library used incorrectly - out of memory
Replies
3
Views
2K
Harris
  • Question
B4J Question [B4X Table] java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")"
Replies
6
Views
2K
Waldemar Lima
  • Question
B4J Question jRDC2
Replies
3
Views
841
David Hawkins
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…