adsdb INSERT INTO

Question

I have an ADT that I can modify using Advantage Data Architect.

However I wish to be able to modify the table using adsdb. I've created the table using;

cnxn = adsdb.connect(DataSource='c:/Python27/', ServerType='1')
cursor = cnxn.cursor()
cursor.execute('CREATE TABLE Persons (PersonID INTEGER, LastName CHAR(100), FirstName CHAR(100))'

I am able to Insert data into the PersonsID field using;

cursor.execute('INSERT INTO Persons (PersonID) VALUES (01)')

However trying to insert data into the the columns of char type using;

cursor.execute('INSERT INTO Persons (LastName) VALUES ("Smith")')

I get the error;

adsdb.OperationalError: Error 7200:  AQE Error:  State = S0000;   NativeError = 2121;  [iAnywhere Solutions][Advantage SQL Engine]Column not found: Smith -- Location of error in the SQL statement is: 40

I have tried using single quotes and no quotes in the VALUE field, but I'm still presented with the error. I have Google'd the error codes provided but I have found little in way of a solution.

Answer 1

In ADS SQL (and indeed in ANSI-SQL) strings (CHAR typed) values have to be enclosed in single quotes:

INSERT INTO Persons (LastName) VALUES ('Smith')

In Python a string literal can either be written in single or double quotes:

print("Hello")
print('Hello')

Since the correct SQL statement does not contain double quotes it would be easier to use a double quoted string literal:

cursor.execute("INSERT INTO Persons (LastName) VALUES ('Smith')")

If you want to use a single quoted string literal, you have to escape the single quotes inside the literal:

cursor.execute('INSERT INTO Persons (LastName) VALUES (\'Smith\')')

But I wouldn't do it like that, since using string interpolation or string concatenation to get values into an SQL statement is very dangerous and can lead to SQL injection.

The correct way would be using a parameter:

cursor.execute('INSERT INTO Persons (LastName) VALUES (?)', 'Smith')

BTW: "Persons" is a terrible table name (The plural of person is people, you should either use "person" oder "people" as the table name).