INSERT errors out

[oneeyeman1]

Hi, ALL,

[code]

queries.push_back( L"IF NOT EXISTS(SELECT 1 FROM sysobjects WHERE name = 'abcatedt' AND type = 'U') EXECUTE(\"CREATE TABLE abcatedt(abe_name char(30) NOT NULL, abe_edit char(254), abe_type smallint, abe_cntr integer, abe_seqn smallint NOT NULL, abe_flag integer, abe_work char(32), PRIMARY KEY( abe_name, abe_seqn ))\")" );

queries.push_back( L"IF NOT EXISTS(SELECT o.name, i.name FROM sysobjects o, sysindexes i WHERE o.id = i.id AND o.name = 'abcatedt' AND i.name = 'abcate_x') EXECUTE(\"CREATE UNIQUE INDEX abcate_x ON abcatedt(abe_name ASC, abe_seqn ASC) WITH IGNORE_DUP_KEY\")");

[[/code]

After running those I tried to do this:

[code]

2084 for( std::vector<std::wstring>::iterator it = queries.begin(); it < queries.end(); ++it )
(gdb)
2086 qry.reset( new SQLWCHAR[(*it).length() + 2] );
(gdb)
2087 memset( qry.get(), '\0', (*it).length() + 2 );
(gdb)
2088 uc_to_str_cpy( qry.get(), (*it) );
(gdb)
2089 ret = SQLExecDirect( m_hstmt, qry.get(), SQL_NTS );
(gdb)
2090 if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
(gdb)
2092 GetErrorMessage( errorMsg, STMT_ERROR );
(gdb)
2093 ret = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK );
(gdb) p (*it)
$1 = (std::__cxx11::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > &) @0x6210005e12a0: {
static npos = 18446744073709551615,
_M_dataplus = {<std::allocator<wchar_t>> = {<__gnu_cxx::new_allocator<wchar_t>> = {<No data fields>}, <No data fields>},
_M_p = 0x6130000dc840 L"INSERT INTO abcatedt VALUES( 'BenefitsCheckBox', NULL, 85, 4, 1, 536870916, NULL );"}, _M_string_length = 83, {
_M_local_buf = L"S\000\xbebebebe\xbebebebe", _M_allocated_capacity = 83}}
(gdb) p errorMsg.at(0)
$2 = (__gnu_cxx::__alloc_traits<std::allocator<std::__cxx11::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > >, std::__cxx11::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > >::value_type &) @0x606000233840: {
static npos = 18446744073709551615,
_M_dataplus = {<std::allocator<wchar_t>> = {<__gnu_cxx::new_allocator<wchar_t>> = {<No data fields>}, <No data fields>},
_M_p = 0x615000194b80 L"[SAP][ASE ODBC Driver][Adaptive Server Enterprise]The column abe_edit in table abcatedt does not allow null values.\n"}, _M_string_length = 116, {_M_local_buf = L"t\000\xbebebebe\xbebebebe", _M_allocated_capacity = 116}}
(gdb)

[/code]

I don't understand the error.

The field abe_edit should allow NULLs - it does not have "NOT NULL" clause.

Could someone please sched some lights?

I have SAP ASE 16..1 on Linux.

The connection is done thru the ODBC driver with unixODBC Driver Manager.

Thank you for any pointers...

P.S.: For some reason SAP ASE tag is not available to me. If someone can fix it and place the proper tag here it would be great.

P.P.S.: I tried to add:

[code]

if( !result )
{
memset( qry.get(), '\0', 200 );
uc_to_str_cpy( qry.get(), L"sp_dboption tempdb, 'allow nulls by default', 'true'" );
ret = SQLExecDirect( m_hstmt, qry.get(), SQL_NTS );
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, STMT_ERROR );
result = 1;
}
}

[/code]

doesn't help.

[Mark_A_Parsons · Accepted solution]

What was tempdb's setting for 'allow nulls by default' at the time the table was created?  Had you set 'allow nulls by default' to 'false' at some time in the past?

Running sp_dboption tempdb, 'allow nulls by default', 'true'  will affect future column creations; it will not modify columns that have already been created. 

What does sp_help abcatedt show as the NULLability for column abe_edit?

After running sp_dboption tempdb, 'allow nulls by default', 'true' , if you drop and recreate the table, does your code work (ie, the column will now accept NULLs)?

[oneeyeman1 · Accepted solution]

@MarkAParsons, so every time I need to run sp_F option it has to be run against my db and tempdb?

[Mark_A_Parsons · Accepted solution]

I have no idea what sp_F is nor what it has to do with the original question of a failed insert (due to a column not allowing NULLs).

-------------------

Have you solved the original issue (failing insert due to column not allowing NULLs)?

If the answer is 'yes' and you're now running into a new issue then I'd suggest asking a new question.

If the answer is 'no' then could you please provide answers to my previous questions.

[oneeyeman1 · Accepted solution]

@MarkAParsons,

In your first reply you wrote::

> What was tempdb's setting for 'allow nulls by default' at the time the table was created?  Had you set 'allow nulls by default' to 'false' at some time in the past?

I didn't execute such a command. I only executed it for my DB. That's why in my reply I asked if I have to run it for the "tempdb" as well and also run every single option change in both.

Sorry for the confusion.

Let me try to run it for the "tempdb" as well and see what will happen.

Thank you.

[Mark_A_Parsons · Accepted solution]

sp_dboption is used to change the settings for a single database.

If you need multiple databases to have their settings changed then you need to run sp_dboption once for each database in question.

NOTE: If you're using multiple tempdb's, and they are being assigned to users in a round-robin(ish) approach, then you should insure all tempdbs have been configured the same.

[oneeyeman1 · Accepted solution]

@MarkAParsons, it looks like I have to run "sp_dboption tempdb, 'allow nulls by default', 'true'" AND "sp_dboption <myDB>, 'allow nulls by default', 'true'" in order to allow NULLS to be inserted. Thx. Now I presume that whenever I need to run "sp_dboption" I will have to run it for both my own db and the "tempdb".