B4J Question It is not possible to get data and the number of rows in the Database Table

D

Darsiar

Member
It is not possible to get data and the number of rows in the Database Table
None of the listed methods works; the table is created and there is a record there:
If rs.NextRow Then <-- always gives the true, although there is nothing there !!!

Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")
' Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() FROM Client") ' same mistake!!!

Dim id As Int= 1

Try
If rs.NextRow Then
id=rs.GetInt("ClientId")
id = id + 1
End If
Catch
Log(LastException)
End Try
rs.Close

ERROR:
java.sql.SQLException: no such column: 'ClientId'

thanks for the advice
 
Sort by date Sort by votes

OliverA

Expert
Licensed User
Longtime User
Ok. You need to help us make sense of you question. What is causing the issue? You actually have two queries here and we don't really know which one is causing issues and where the issue is exactly occurring. So query #1
B4X: 
   Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")
   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("ClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close
When you run this, where is the error occurring? It makes a big difference. Is it the line
B4X: 
Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")
than that would indicate that ClientId column does not exist

or is it
B4X: 
id=rs.GetInt("ClientId")
then it could be that the SQL Database you are using is creating a different column name for your MAX(ClientId). So instead of letting the DB decide for you do a
B4X: 
   Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) As MaxClientId FROM Client")
   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("MaxClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

Query #2
B4X: 
   Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() FROM Client")  ' same mistake!!!  

   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("ClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close
If that is how you wrote that one, then there is no ClientId returned! Probably should have been:
B4X: 
   Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() AS MaxClientId FROM Client")  ' same mistake!!!  

   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("MaxClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

Darsiar said:
If rs.NextRow Then <-- always gives the true, although there is nothing there !!!
Click to expand...
Actually, there is, otherwise that would not execute. You're just not getting back what you expect.

BTW: Ninja'd by @Peter Lewis, who has another way to get the answer you are looking for. I'm just explaining why you may have the issue you are experiencing.
 
Upvote 0
D

Darsiar

Member
I try your code and it works!
Thank you so much!!!
But I do not understand what this code means, because it is not in the textbook?
AS MaxClientId

---------------------------
Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() AS MaxClientId FROM Client") ' same mistake!!!

Dim id As Int= 1

Try
If rs.NextRow Then
id=rs.GetInt("MaxClientId")
id = id + 1
End If
Catch
Log(LastException)
End Try
rs.Close
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
Darsiar said:
It is not possible to get data and the number of rows in the Database Table
Click to expand...
Not with what you posted... (use code tags as well).

Not to criticize, but you must study and be knowledgeable of SQL for the answer to your question which is quite elementary...
A little more time on your part should have uncovered this problem - and educated you at the same time...
@OliverA took the time to explain this quite well.
 
Upvote 0
D

Darsiar

Member
I am very grateful!
This is really impressive in this forum - a good desire to help and teach each of us.
We are really still new to Basic, because before we had experience only with languages such as C #, F # in net.core 2 and net.core 3
Net.core is a little different there.
More flexible, wider, more powerful, more professional.
But here, too, impresses with its simplicity, which captivates the brilliance of ideas of enthusiasts!
It is surprising that such a simple language does so many useful things on people's enthusiasm!
And the enthusiasm and attitude towards people is the most important thing in any project.
Regards and admiration!
Thank.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Darsiar said:
But I do not understand what this code means, because it is not in the textbook?
AS MaxClientId
Click to expand...
AS creates an alias for the before standing column, table, function, etc. It's helpful in our case sense (as I mentioned) databases sometimes create their own column names. So for example, if I use Access to run the following query
B4X: 
SELECT Count(*) FROM dbo_Test
the column name that Access creates to access the result is Expr1000. If I use SQLite, that column name becomes Count(*). So instead having to guess what each DB Engine does, we can create an alias
B4X: 
SELECT Count(*) AS MaxClientId FROM dbo_Test
Now, the column name returned will always be MaxClientId, no matter the DB Engine used. I do have to agree with @Harris on this, that you'll need to learn a little about SQL to use SQL.[/code]
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…