[Solved] Statement does not generate a result set

[JayArr]

Hello Everyone

I've got two identical tables of names and addresses called OldCombined and NewMASTER.

Both of these tables were dBase flat files that I dragged into a new HSQL format using OO3.3

While converting them I allowed OO to add a column called ID to the beginning of each table and make it the Primary key. It was then filled automatically with incrementing numbers.

Now I want to bring a select set of records from the old table to the new table, it should be simple right?

Here is what I've tried.

Code: Select all

INSERT INTO "NewMASTER" ("BUS_NAME", "ADDRESS", "SUITE", "CITY", "PROVINCE", "POSTAL_COD", "PRI_SIC", "PSIC_DESC", "INFOUSAID")

SELECT "BUS_NAME", "ADDRESS", "SUITE", "CITY", "PROVINCE", "POSTAL_COD", "PRI_SIC", "PSIC_DESC", "INFOUSAID"
  
FROM "OldCombined"

WHERE "OldCombined"."PRI_SIC" = 762923

It returns the error

"The data content could not be loaded" "Statement does not generate a result set"

I can run the last half and it will return the records I want

Code: Select all

SELECT "BUS_NAME", "ADDRESS", "SUITE", "CITY", "PROVINCE", "POSTAL_COD", "PRI_SIC", "PSIC_DESC", "INFOUSAID"
  
FROM "OldCombined"

WHERE "OldCombined"."PRI_SIC" = 762923

so I know the problem is in the INSERT INTO statement.

I've checked the table NewMASTER and the ID field is integer. The table shows an index called SYS_IDX_46 on the ID field and is selected as 'unique"

I'm pretty sure it's a primary key problem. I obviously can't copy the whole record because the ID values are duplicates and will cause a conflict, I went to the table definition and set the ID field to autovalue hoping that new numbers would be written into the ID field based on the last number used when the table was created.

I thought that if I inserted every field except that ID field that it would work but no luck!

Any ideas out there?

[keme]

Check the name of the table you want to INSERT INTO against the name you use in your query.
"NewCombined" or "NewMASTER"?

[JayArr]

Thanks Keme but that's just a typo from me trying new things. Even when the table names match it doesn't work.

Original post corrected.

JayArr

[Sliderule]

Your INSERT statement . . . is NOT a Query.

Only Queries ( Starting with a SELECT statement -- and -- returning a Result Set ) are run from the Query section of OpenOffice Query.

Now, I hear you saying / asking . . . OK, so, how do I execute my INSERT statement, or, any other MODIFICATION / CHANGE to the database content / definition ( for example, SQL statements that start with: CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ) . . . the answer is, From the Menu:

Tools -> SQL...

In the Command to Execute box, you can enter your INSERT statement, and, press the Execute button . . . see the message below . . . Command successfully executed . . . and press the Close button.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[JayArr]

Thanks a bunch Sliderule - that solved it.

I've just migrated from StarOffice 6.0 to OO 3.3 so there is a ton of differences in how you get things done that I will have to learn, I really appreciate you taking the time to answer what must look like a newbie question to you.

JayArr

[fsoto]

So, if I have or need to run a stored procedure in SQLServer that returns a resulset, what do I have to do???

let's see.... this is the SQL command tu run this stored in SQL Management Studio, or MS Excel

exec pr_d_rpt_recibo_normal_tipo_d '0000', '9999'

in both cases it returns a resulset, but in OO it doesn´t return anything.

I've tried in Tools->SQL, then run sql command, then Base returns the message Command executed succesfully, but no resultset is available.
on the other hand, when i try to run this command as a query it returns the message Statement does not generate a result set.

I'm really new on this and hope somebody can help me.
Thanks in advance.

[Sliderule]

fsoto:

Since, according to you, it is supposed to return a result set, try the following, I canNOT guarantee it will work, since, I am NOT operating under your environment:

1. Open your OpenOffice Base file ( *.odb )
2. Click on Queries icon on the left
3. Under Tasks, click on: Create Query in SQL View...
4. Enter your command
5. Instruct Base NOT to parse the statement, but, just send it as is to your database engine. Either:
   1. On the Toolbar, press the Run SQL command directly icon ( green check mark with letters SQL )
   2. From the Menu: Edit -> Run SQL command directly
6. Run the Query . . . Either:
   1. F5 key
   2. On Toolbar, press, Run Query icon
   3. From the Menu: Edit -> Run Query

Explanation: Since you want to send a command directly to your database engine withOUT Base first checking it for errors, OR, presenting a Parameter Query ( to allow entry of a variable(s), prompting the user for values, for example a date range to 'filter' the Query ), the above will just take the written command and pass it to the database engine.

I hope this helps, please be sure to let me / us know.

Sliderule

[fsoto]

Sliderule

I did everything in the order you tell me and the result is always the same 'La Ejecución de la Consulta no Regresa un Resultado Válido',
or something like 'The query doesn't returns a valid result'.

But let me tell you something, because Base queries requires the first word in the query string have to be the 'Select' statement i do the following:

1.- In SQL Server create a User Defined Function that returns a table:

create function nomrecibos(@c_codigolug_ini char(4), @c_codigolug_fin char(4))
returns table
as
Return
(
here some sql stuff......
)

2.- In OO Base, Create a Query in SQL View like this one:

Select * from nomrecibos('0000', '9999')

So this query returns a valid result set exactly as it's defined in the SQL User Defined Function. This method resolves partially our need to run SQL Server storeds procedures in OO Base.

We are still looking for a better aproach....

Fer....

[Sliderule]

I can only tell you that I have NEVER tried using an EXEC statement as a Query.

However, I have using a CALL in the Query Window.

Let me just mention, perhaps another 'trick' that you might consider. Create a VIEW on your SQL Server. Since Base will recognize both Tables and Views, perhaps a VIEW might be helpful.

Just a thought.

Sliderule

[fsoto]

In fact, we've been thinking about using tables or views on the server, but that would mean to have an object in the database for each query. In addition, if the end user requires changes in their files, then we should change the design or definition of the views every time this happens.

In addition to this, MS Excel user to update data in your files just press the F5 key, or modify the query in MS Query if the stored procedure takes parameters.

in OO do so with tables or views, the user would first run the command in SQL server and then update the query in Calc.

Either way, we have all the options considered. We might be using the option that most closely matches the requirements of each user.

Building on the Call statement, you could expand a little in their use, such as syntax, or tell me where I can find more information about this.

BTW
I'm usin OO 3.3 on Windows 7. My Signature is OO 3.1 on Windows Vista because this forum makes me select it that way.

Thanks
Fer....