Solved

Issue with creating Tables in Database

  • 21 April 2021
  • 2 replies
  • 24 views

Badge

Hello,

I am trying to create Tables in a database using DirectSQL() function. There are 3 scenarions:

 

  1. DirectSQL("DRIVER=SQLite3 ODBC Driver; Database=Output.db","CREATE TABLE April2121 (Field1 INTEGER)"); Here the procedure is succesfully executed
  2. DirectSQL("DRIVER=SQLite3 ODBC Driver; Database=Output.db","CREATE TABLE 20210421 (Field1 INTEGER)"); Here it generates an error
  3. DirectSQL("DRIVER=SQLite3 ODBC Driver; Database=Output.db",FormatString("CREATE TABLE %s (%s INTEGER)",CurrentDate, Field1)); Here it generates an error

I need to create tables with names taken from string parameter “CurrentDate” and Column Name taken from string parameter “Field1” 

Current Date string parameter is defined as CurrentToString("%c%y-%m-%d").

Could you please tell me where I am going wrong with this? I have attached screenshots for your reference.

Thanks.

icon

Best answer by mohansx 21 April 2021, 22:45

@vishweshpatil 

 

This is a SQLite issue. Table names must be strings. 

 

CREATE TABLE April2121 (Field1 INTEGER)

 

succeeds because you specify the table name as alphanumeric and it is automatically encoded as astring. 

 

CREATE TABLE 20210421 (Field1 INTEGER)

 

will fail, even if you execute on SQLite directly because 20210421 is not accepted as a valid table name. Modify your statement as, 

 

CREATE TABLE “20210421” (Field1 INTEGER)

 

You can do this using formatString by escaping the quotations. 

 

DirectSQL("DRIVER=SQLite3 ODBC Driver; Database=Output.db",

FormatString("CREATE TABLE \”%s\” (%s INTEGER)",CurrentDate, Field1))

View original

2 replies

Userlevel 5
Badge +2

@vishweshpatil 

 

This is a SQLite issue. Table names must be strings. 

 

CREATE TABLE April2121 (Field1 INTEGER)

 

succeeds because you specify the table name as alphanumeric and it is automatically encoded as astring. 

 

CREATE TABLE 20210421 (Field1 INTEGER)

 

will fail, even if you execute on SQLite directly because 20210421 is not accepted as a valid table name. Modify your statement as, 

 

CREATE TABLE “20210421” (Field1 INTEGER)

 

You can do this using formatString by escaping the quotations. 

 

DirectSQL("DRIVER=SQLite3 ODBC Driver; Database=Output.db",

FormatString("CREATE TABLE \”%s\” (%s INTEGER)",CurrentDate, Field1))

Hi @vishweshpatil 

Were you able to solve your SQL issue with Mohan’s suggestions?

 

 

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator