1

I am unable to run tow SQL queries in a single transaction. Here is my query:

DROP TABLE DevicePing IF EXISTS;

CREATE TABLE DevicePing (DeviceId INTEGER ,RideId BIGINT ,Latitude FLOAT ,Longitude FLOAT ,Speed FLOAT ,Bearing FLOAT ,Angle FLOAT ,HorizontalError FLOAT ,VerticalError FLOAT ,Temprature FLOAT ,Ignition TINYINT ,Motion TINYINT ,RelayState TINYINT ,TIMESTAMP BIGINT);

I have found that only first query executes, while the other doesn't. Whereas documentation about @AdHoc explains it can execute multiple queries. What is going wrong?

Suresh Prajapati
  • 3,991
  • 5
  • 26
  • 38

1 Answers1

1

VoltDB doesn't allow an object to be dropped and created within the same transaction or batch (if using batched DDL statements). If you're concerned about possible interference of this change with other transactions, you should pause the database prior to making this change, then resume the database.

If you have any stored procedures that use this table, they would need to be dropped first, before you can drop the table. Then they can be re-created after you have created the table again. If multiple procedures or views depend on this table and you are trying to minimize the number of batches, you may find you can use one batch to drop everything (starting with procedures, views, then tables) and another batch to create everything again (starting with tables, indexes, views, then procedures). This is the pattern you could follow if you want an idempotent DDL script, which can be a time saver during development if you are making frequent schema changes and you don't need to retain any data.

Another alternative, depending on what type of change you are making, you might be able to use ALTER TABLE.

BenjaminBallard
  • 1,482
  • 12
  • 11