[Solved] Cannot get COUNT from query

[jgsylvester]

I have a Basic macro that connects to a OpenOffice.org Calc spreadsheet and imports the data using the Uno service Drive Manaer. I successully connect to the Calc spreadsheet and successfully execute the query "SELECT * FROM TABLENAME", and then cycle through all records importing the data to my database. However the following query does not produce the count of records from the spreadsheet "SELECT COUNT(*) AS ICOUNT FROM TABLENAME". The variable ICOUNT is empty. Am I doing something wrong or is COUNT not valid in this situation? I can cycle through the records and get a count of the number of records if necessary.

[eremmel]

When I use a Base document and have as database a Calc sheet, I can use a 'count-*' query. I do not know whether you can expect that a BASIC variable 'ICOUNT' is filled by running the query. I think that you need to bind your BASIC variable to the output column. In some languages with 'embedded' SQL you normally specify '... AS :ICOUNT FROM ... (note the colon) to tell that there is a relation between language variable and query result. Not sure how that works in Base with BASIC, but have a look to variable binding.

[jgsylvester]

Thanks for the suggestion. I get an BASIC runtime error: An exception occurred. Type: com.sun.star.sdbc.SQLException. Syntax error. Unexpected symbol ':', expecting NAME or CHAR_LENGTH or SQL_TOKEN_POSITION or SQL_TOKEN_EXTRACT. I also tried to COUNT (HEADERNAME) but that failed, saying only COUNT (*) was valid. I have never seen CHAR_LENGTH. I will look into that - maybe there is something there.

[rudolfo]

Why don't you try without the column alias in the query statement with "count(*)"? Simply cycle through your one row result set:

Code: Select all

  oStmt = oCon.prepareStatement("SELECT COUNT(*) FROM TABLENAME")
  oResult = oStmt.executeQuery()
  oResult.next()    ' usually you would have a while ...  here, but as it is always exactly one row
  theCount = oResult.getString(1)

[jgsylvester]

It worked!! Thankyou very much for solving this problem. I really appreciate it.